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.
How to Use Google Sheets Data Validation
Data validation is quite a simple feature to use.
1. Select the individual cell or cell range
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
2. Select Data > Data Validation
From the file menu select Data to produce a drop-down menu, from this menu select Data Validation.
3. Select “Add Rule” from the Data validation rules menu
The Data validation rules menu will now display on the right-hand side, from this window select + Add Rule.
4. Set the Criteria for the Data Validation
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.
5. Click Done to Apply the Data Validation
With the criteria and parameters click Done to apply the criteria, the data validation will be applied to the data range selected earlier.
How to Use Data Validation to Set a Number Value Limit in Google Sheets
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.
1. Select the Cell or Cell Range
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
2. Click Data > Data validation followed by +Add Rule
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.
3. Select “greater than” From the Criteria Drop-Down Menu
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.
4. Choose an Action for Invalid Data and Click Done
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.
How to Make a Drop-Down List Using Data Validation in Google Sheets
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.
1. Select an Empty Cell or Cell Range
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
2. Select Data followed by Data Validation. Then Select + Add Rule
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.
3. Select “Drop-down” from the Criteria and input the Parameters
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.
4. Select “Done” and Test the Results
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.
Use Google Sheets to Set a Text Length Limit Using Data Validation
We can also limit inputted text length in Google Sheets, here is how.
1. Select the cell or cell range
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.
2. Select Data validation from the Data menu and select +Add rule
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.
3. Select “Custom formula is” from the Criteria menu
Expand the drop-down menu underneath the Criteria heading, scroll to the bottom and select the option for Custom formula is.
4. Input the Formula “=LEN(First_Cell)<Text_Length”
In the criteria input box enter the formula in the following format:
=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:
5. Press “Done” to apply the data validation
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.