How to Use Data Validation in Google Sheets

[This article was updated on March 2nd, 2023]

Data Validation is a powerful feature in Google Sheets that allows you to control the type of data users can enter into cells. By setting validation criteria, you can prevent incorrect data entry and ensure the accuracy of your worksheet data and formulas. In this article, we will explore how to use Data Validation effectively and discuss its various applications.

What is Data Validation in Google Sheets?

Data Validation in Google Sheets enables you to set rules for the type of data that can be entered into specific cells or ranges. When a user attempts to input data that does not meet the validation criteria, Google Sheets displays an error message, preventing the entry of incorrect data. This feature helps minimize errors and maintain data consistency. You can set rules for:

1. Number ranges, such as allowing only numbers between 1 and 100.
2. Predefined values or text strings, like only permitting “Yes” or “No.”
3. Date ranges, limiting entries to a specific period.

Why Should You Use Data Validation in Google Sheets?

Data Validation can significantly enhance the efficiency and validity of your analysis, KPI dashboards, or financial models. Instead of constantly revising your models or formulas to accommodate new input types, Data Validation ensures consistent and accurate data entry. Here are a few scenarios where Data Validation proves beneficial:

1. Maintaining consistent data input throughout a list.
2. Limiting data input to specific ranges or styles.
3. Creating drop-down lists with predefined choices.

Recent Updates to Data Validation in Google Sheets

In December 2022, Google Sheets introduced two significant updates to the Data Validation feature:

1. Dropdown chips: Dropdown chips are pull-down lists that provide a quick overview of project statuses. This update enhanced the existing drop-down list feature, making it consistent with Google Docs’ functionality.

2. Improved visibility of all data validation rules: Previously, it was challenging to see all data validation rules at a glance. With the recent update, you can now access a list of all data validation rules, improving management efficiency and accuracy.

How to Use Data Validation in Google Sheets

To use Data Validation in Google Sheets, follow these steps:

1. Go to the “Data” tab and click “Data Validation” to open the dialog box.
2. Click “+ Add rule”.
3. Select the range where you want to apply Data Validation.
4. Choose a validation criterion and input the required values.
5. Optionally, enable “Show help text for a selected cell” to provide hints for invalid data inputs.
6. Select one of the two options for invalid data: “Show a warning” or “Reject the input”.
7. Click “Done” to apply the settings.

Next, let’s explore the different types of data validation available in Google Sheets.

Types of Data Validation in Google Sheets

1. Drop-down: Create a pull-down menu in your sheet by manually inputting the choices. You can even assign colors to each option.

2. Drop-down (from a range): Similar to the drop-down option, this allows you to generate choices based on cell references instead of manual input.

3. Text-related data validation: Limit data input to specific text conditions. Choose from options such as “Text contains,” “Text does not contain,” “Text is exactly,” “Text is valid email,” and “Text is valid URL.” Input a specific text string based on your choice.

4. Date: Restrict cells to accept only date entries. Choose from various date conditions and provide specific dates accordingly.

5. Number: Validate numeric values or formulas. Set conditions such as “between X and Y” or “greater than Z” among the available options.

6. Custom formula is: Insert a formula to pull data from another source.

7. Tick box: Allow users to check or uncheck a box in a cell. Define corresponding values for checked and unchecked boxes, like TRUE or FALSE.

How to Quickly Insert a Drop-down List or Tick Box in Google Sheets

Apart from the previously mentioned method, you can use a quicker way to insert a drop-down list or a checkbox in Google Sheets. Follow these steps:

1. Navigate to the menu bar.
2. Click “Insert”.
3. Select “Tick box” or “Drop-down”.
4. Edit the details in the data validation rule that appears on the right side.

How to Create a Dependent Drop-down List in Google Sheets

To learn how to create a drop-down list in detail, check out this article: Drop Down List in Google Sheets: Explained.

If you’re interested in creating a dependent drop-down list, where choices change based on another drop-down selection, you can find a detailed guide here: Dependent Drop-down List in Google Sheets: Explained.

Finally, take a look at this example of a drop-down list. This is LiveFlow’s Consolidated P&L Template for Excel & Google Sheets. In the middle of the image, you can see a drop-down list that allows you to switch between monthly financial data.

Remember, mastering Data Validation in Google Sheets can significantly improve your data accuracy and streamline your workflows. Stay organized and save time by implementing Data Validation effectively in your spreadsheets.

For more helpful guides and resources, visit Mr Reviews.