Data Validation in Google Sheets

Data validation is a powerful feature of Google Sheets.

With data validation, you can easily create drop-down lists. checkboxes, and validate multiple types of data. You can also use data validation with a custom formula.

In this tutorial, I am going to show you everything you need to know about data validation in Google Sheets.

What is Data Validation in Google Sheets?

In Google Sheets, data validation is a feature that allows you to set parameters for data to ensure that input data is in the format that you want it in.

You can create rules with numbers, dates, and text to ensure that there are no errors when entering data.

For example, you can set a range of cells to have a data validation of “Date” and when users entering data that is not a date, it will show an error message. This is just one example, but you can use data validation with many different criteria depending on what you are trying to accomplish.

Below we will cover all of the different criteria that you can use with data validation and show you examples of how each criterion can be used.

By the end of this post, you should have a firm understanding of data validation and you should be ready to start implementing it into your own spreadsheet.

Using Data Validation

List from a Range

One very useful way to use data validation is to create a drop-down list using list from a range as your criteria.

You can set up a data validation on an entire column, or a specific range of cells to create a drop-down list from your data.

Here are the steps to do this:

1. First select the cell where you want the drop-down list to appear

2. Then, in the top menu select Data and then select Data validation

3. In the criteria section of the popup box select List from a range 

4. In the text box where it says “Enter range or formula” enter the range that contains your list that you want to turn into a dropdown.

5. Make sure the “Show dropdown list in cell” box is checked and then select Save

6. You should now see a drop-down menu created from the cell range you specified in step 4

 

List of Items

You can also use the list of items criteria to create drop-down lists in Google Sheets.

This is very similar to the list from a range option except with list of items, you have to enter each value that you want in your drop-down manually, you can not select a range that contains your values.

This means that this one is best for smaller drop downs that don’t have a long list of options to choose from. For example, it would be ideal for creating a simple YES or NO dropdown.

Here is how to use it:

1. Select the cell where you want the drop-down menu to appear

2. In the top menu select Data and then select Data validation

3. In the Data validation box that pops up, set your criteria to List of items

4. In the text box you need to enter the values that you want to show up in your drop-down separated by a comma. We have used YES, NO in this example to create a simple yes or no drop down list.

5. Make sure the Show dropdown list in cell box is checked and then select Save

6. You should now see a drop-down list created with the values you chose in step 4.

Number

Using the number criteria in data validation allows you to set rules for numerical values entered into a cell or range of cells.

You can set rules to only accept numerical values that meet certain criteria and if the numerical values do not meet that criteria you can either show a warning message of reject the value from being entered altogether.

With the number criteria you can set data validation with the following rules:

• Between
• Not between
• Less than
• Less than or equal to
• Greater than
• Greater than or equal to
• Equal to
• Not equal to

To show you how to use the number criteria, I am going to use an example where in one column I have a list of employee names and in the second column, I want to grade each employee on a scale of 1 to 10. I am going to set up criteria that all numbers entered into the second column must be between 1 and 10 using data validation.

Here are the steps to do this.

1. First highlight the range of cells that you want to add number data validation for

2. Then in the top menu select Data and then select Data validation

3. In the Data validation box that pops up, you should cell your cell range is set to the range you highlighted in step 1. In the criteria section, select Number

4. After you select Number, you will see options for the rules you want to apply to your data. We selected between in this example, but you can use whichever you want

5. Since we are using between as our number rule, we need to set a range of data. We only want to accept values between 1 and 10, so that is what we set as our numerical values.

6. Next set your rules for what happens if invalid data is entered. You can either choose to show a warning or to reject the input altogether. Select Save when you are done.

7. You have now applied number data validation rules to your selected cell range. You can see that when I enter values into the range that are not between 1 and 10, I get an error message.

Text

The text criteria option in Data validation works very similarly to the number criteria option.

With text criteria, you set certain rules for your text, and if the values entered into your cell or cell range do not meet those rules you can either show a warning or reject that input altogether.

You can set these rules for text criteria:

• Contains
• Does not contain
• Equals
• Is valid email
• Is valid URL

Text data validation can be very useful for creating forms where users enter information such as email addresses, names, websites, etc. This can help prevent users from entering data in the wrong format.

To use the text criteria the steps are the exact same as using the number criteria. You first select your range of cells, select the Text criteria, and then select your text rules.

Date

With the date criteria option in Data validation, you can set rules regarding the dates entered into your cell or range to ensure that all dates entered meet certain criteria.

The rules you can apply for date criteria include:

• Is valid date
• Equal to
• Before
• On or before
• After
• On or after
• Between
• Not between

Setting up date criteria is similar to the process we used entering both numerical and text criteria:

1. Select the range of cells that you want to apply the date data validation to
2. Select the Date criteria in the Data validation pop up box
3. Select the date rule that you want to use
4. Then enter the date or dates that your rule must follow and select Save when finished

Using date data validation can be very useful in your spreadsheets if you are logging dates for certain things. You can make sure the dates in your file are in the appropriate format.

Custom Formula is

Using the custom formula is criteria in Data validation allows you to enter your own custom formula that your specified cell or range must follow. This can be a powerful tool if none of the other criteria options will do what you need.

With custom formula is you can string together different functions or math operators to ensure that data entered follows your chosen format or rules.

To show you an example of one way you can use custom formula is criteria in your spreadsheet, I am going to set up a data validation so that we can only enter values in column B, if column A is set to “Yes”

To do this the first thing I will do is highlight the range of data in column B that I want to set up my data validation for.

Then, select Custom formula is and set up your formula. In my example, I have used “=A2=”Yes”

You can see with the custom formula that I used, the data in column B is not valid unless column A is set to “Yes”

This is just one way to use a custom formula is. An important thing to note with this criteria is that you need to understand relative and absolute cell references so that you can understand what to use in your own formulas.

Checkbox

The last data validation criteria is the checkbox option. This option will create a basic checkbox in your spreadsheet that you can use to track progress in a spreadsheet.

Here is a basic example of how the checkbox criteria can be used:

An important thing to note with the checkbox option is that the values in your checked-off cells by default return “TRUE” if the cell is checked, and “FALSE” if the box is unchecked. You can change this setting by selecting the “Use custom cell values” and entering what you want the checked or unchecked cells to return.

This is useful because then you can set up conditional formatting or other formulas based on whether or not the checked-off boxes return either TRUE or FALSE. This can allow you to do many neat things in your spreadsheet.

Closing Thoughts

We have walked you through the basics of how to use each data validation type and given some examples of ways that they can be used.

The possibilities with data validation are endless, and we have only scratched the surface on what is possible.

Take your time and try to go through each type and really understand how it works and how it can be used.

More Google Sheets Tutorials:
How to Create a Dependent Drop Down List
How to Randomize a List
How to Use INDEX and MATCH Together
How to Format Phone Numbers