In this article we will teach you everything you need to know about data validation in Google Sheets along with examples
A demonstration sheet was created for the purpose of these examples. Follow along by clicking the following link:
https://docs.google.com/spreadsheets/d/17qQWyxECJnu9RZ8sjpL5ncmSqRLKDnFUGLsuYSb6yfo/edit?usp=sharing
Data validation allows the control and limitation of the data a user can input. This Google Sheets feature is useful to improve the accuracy and functionality of spreadsheets that are intended for use by other people.
The limitations can be applied to a variety of input types and can be utilised to ensure the data entered matches set criteria.
The below examples are demonstrations of how to use data validation in Google Sheets including the limitation of inputed text and numeric values. We will also show how to make a drop-down list using data validation.
Data validation is quite a simple feature to use.
Click an empty cell to select it, alternatively, you can select a cell range by clicking and dragging over the required cells, highlighting them.
In our example, we will select the cell range C4:C9
From the file menu select Data to produce a drop-down menu, from this menu select Data Validation.
The Data validation rules menu will now display on the right-hand side, from this window select + Add Rule.
A set of options will now appear in the Data validation rules menu
Expand the drop-down menu in the criteria section and choose the criteria you want to apply for the data validation to take place. Depending on the criteria you can input various parameters.
With the criteria and parameters click Done to apply the criteria, the data validation will be applied to the data range selected earlier.
In this example, we will demonstrate how to set a number value limit in Google Sheets using the useful data validation feature. We will now apply data validation to show a warning on any number that is less than (<) 20.
Select the cell or cell range you want to apply the data validation to.
In our example, we will select the cell range G5:G10
From the file menu at the top of the screen click Data followed by Data Validation from the drop-down menu
From the displayed Data validation rules menu, select +Add rule.
Expand the Criteria drop-down menu, and select the greater than option. This is specific to applying a limit under a certain number. If you need to apply data validation to specify a limit over a number or equal to then select the relevant option.
As we are applying data validation to limit numbers under 20 we will choose the greater than option.
From the if the data is invalid section select the option you want to action if the data validation is not in the defined limit:
Show a warning: Allows the input however will show a warning message.
Reject the input: Does not allow the input and clears the text upon invalid limit.
For our example we will check the option to Show a warning.
ALT: Data validation in Google Sheets
After this click Done to see the results.
We can use data validation to create a drop-down list in Google Sheets. This is exceptionally useful to allow very specific input. In our example, we will create a drop-down list for a person's hair color.
Highlight the cell or cell range you want to apply the data validation to.
In our example, we are going to highlight the cell range D23:D28
Click Data followed by data validation from the file menu at the top of the display.
Now click +Add rule from the displayed data Validation options menu on the right-hand side of the screen.
From the Criteria section, ensure the Drop-down option is selected. You will now see a list appear underneath. Complete the list with your chosen parameters, you can also choose colors to apply to the list when the specific option is chosen.
In our example, we will enter five different hair colors and assign a corresponding color to each one.
With the parameters and criteria chosen, click the green Done button. You can now test your drop-down list by clicking the relevant cell to see the list values.
As can be seen in our example when a cell in the previously selected cell range is clicked, the list values now apply, we can simply click a value to input it in the cell.
We can also limit inputted text length in Google Sheets, here is how.
Select the cell or cell range to where you want to apply the data validation.
We will select the cell range D5:D10 in our example.
From the file menu, select Data followed by Data validation from the drop-down menu.
The Data validation rules menu will appear on the right hand side of the display. Click the + Add rule button.
Expand the drop-down menu underneath the Criteria heading, scroll to the bottom and select the option for Custom formula is.
In the criteria input box enter the formula in the following format:
=LEN(First_Cell)<Text_Length
Formula Breakdown:
=LEN: Informs the program to count the length of the input
First_Cell: This is the first cell in your selected range
<: Using the less than operator (<) defines the text needs to be below a defined text range, to set a minimum input text limit simply use the greater than (>) operator.
Text_Length: The defined amount you want to set the entered limit to.
In our example we will use the following formula to ensure that any text entered over 7 characters displays the data validation warning. To achieve this the formula is:
=LEN(D5)<7
With the formula entered, click the done button. The data validation rule will be applied to the previously selected cell range.
As can be seen in the example, any cell containing text longer 7 characters displays an error message.
We hope this article has helped you and given you a better understanding of data validation in Google Sheets. You might also like our articles on how to use data visualization in Google Sheets and how to add a drop-down list in Google Sheets.
To optimize your workflow, we recommend reading our guide on how to send an email from Google Sheets and trying our software for invoice reminders!
