How to Use Data Validation in Google Sheets

Published on March 2, 2023

In this article, we will explore how to use the data validation feature in Google Sheets. Data validation allows you to control the type of data that users can enter into a cell or range of cells in a spreadsheet. When a user tries to enter data that does not meet the validation criteria, Google Sheets displays an error message and prevents the user from entering incorrect data.

What is Data Validation in Google Sheets?

Data validation in Google Sheets is a feature that allows you to control the type of data that users can enter into a cell or range of cells in a spreadsheet. It helps prevent errors and ensures the accuracy of data and formulas in your spreadsheet.

For example, with data validation, you can set rules to restrict the type of data that can be entered in a cell, such as:

• A specific range of numbers, such as allowing only numbers between 1 and 100.
• A predefined list of values or text strings, such as allowing only “Yes” or “No” values.
• A specific range of dates, such as allowing only dates within a given period.

When to Use Data Validation in Google Sheets?

By using the data validation feature, you can make your analysis, KPI dashboard, or financial model more efficient and valid without overlooking irregular inputs or revising your model or formulas to accommodate new types of input.

Data validation is particularly beneficial in the following cases:

• You want to maintain consistent data input in a list from start to finish.
• You want to restrict data input (e.g., a specific range of numbers: 1-10) or data style.
• You need to create a drop-down list with specific choices.

Updates to the Data Validation Feature in Google Sheets

In December 2022, two major updates to the data validation feature in Google Sheets were released.

• Dropdown List Tokens: Dropdown list tokens are a feature that allows you to display and quickly view the states of a project. Google Sheets already had a similar feature for creating a dropdown list, but this feature has been enhanced to be consistent with what was available in Google Docs.

• Better Visibility of All Data Validation Rules: Before the update, it was not necessarily easy to see all data validation rules at a glance. However, with the update, you can see all data validation rules in a list, similar to other features like conditional formatting, protected range, and named ranges. This improvement helps you manage data validation rules more efficiently and accurately.

How to Use Data Validation in Google Sheets?

Here are the steps to use data validation in Google Sheets:

1. Go to the “Data” tab, click on “Data Validation,” and a dialog box will appear on the right side of the spreadsheet.

2. Click on “+ Add rule.”

3. Select the range to which you want to apply data validation.

4. Choose one of the criteria and enter the values based on the criteria.

5. Check the advanced option “Show help text for selected cell” if you want to provide a hint to a user entering invalid data to make it valid.

6. Choose one of the two options for invalid data.

7. Click “Done” to apply the settings.

• “Show warning”: This option allows users to enter invalid data into a cell in the selected range but displays a warning message indicating that their inputs do not comply with the defined input rule.

• “Reject input”: This option rejects any invalid input.

Next, we will explain the types of data validation available in Google Sheets.

• Dropdown List: This option allows you to create a dropdown menu in the spreadsheet by manually entering the choices. You can assign a color to each choice you generate.

• Dropdown List (from range): The only difference between a dropdown list and a dropdown list (from range) is that the choices in the latter are generated by referencing cells instead of manual input in the former.

• Text-related Data Validation: By selecting this option, you can limit data input to text with conditions. There are five prefix conditions: “text contains,” “text does not contain,” “text is exactly,” “text is a valid email address,” and “text is a valid URL.” Note that depending on the choice, you need to enter a specific text string in the text box.

• Date-related Data Validation: If this option is enabled for a cell, the data input for the cell must be a date. There are eight prefix conditions related to date. Similarly to text, depending on your choice, you need to enter a specific date in the text box.

• Number-related Data Validation: If you choose this option, the input must be a specific numeric value or formula. You can set a condition such as “between X and Y” and “greater than Z” from the available eight choices.

• Custom Formula is: This option allows you to insert a formula to extract data from another location.

• Checkbox: You can check or uncheck a box in a cell by choosing this option. You can enter a cell value corresponding to a checked box and another for an unchecked box (e.g., TRUE for a checked box and FALSE for an unchecked box).

How to Quickly Insert a Dropdown List and Checkbox in Google Sheets?

In addition to the method we presented above, there is a faster way to insert a dropdown list or checkbox in Google Sheets.

1. Go to the menu bar.

2. Click on “Insert.”

3. Select “Checkbox” or “Dropdown.”

4. The data validation rule appears on the right side for editing the details.

How to Create a Dependent Dropdown List in Google Sheets?

To learn how to create a dropdown list in detail, check out this article: Dropdown List in Google Sheets: Explained.

Furthermore, if you are interested in creating a dependent dropdown list, where the choices change based on a selection made in another dropdown list, you can learn more here: Dependent Dropdown List in Google Sheets: Explained.

Finally, here is an example of a dropdown list. It is the consolidated income statement template of LiveFlow for Excel and Google Sheets. You can see a dropdown list, with which you can switch monthly financial data, in the middle of the image (the cell next to the text “Choose a month”).

To learn more about advanced features of Google Sheets and discover how to optimize your work, visit Crawlan.com.