How to Use Data Validation in Google Sheets

In an increasingly data-driven world, maintaining accurate and consistent data is crucial for effective decision-making and operational workflows. Whether you’re managing project tasks or tracking product inventory, even minor errors or inconsistencies can lead to significant issues. By validating data in Google Sheets, an online spreadsheet tool for analyzing and managing data, you can ensure the accuracy and integrity of your data by restricting the data that you can enter into your worksheet based on predefined rules.

According to a report by Precisely and Drexel University's LeBow College of Business, 64 percent of organizations cite data quality as a common challenge for maintaining data integrity [1]. This underscores the importance of having high-quality, accurate, and reliable data for data analysis tasks. Google Sheets’ data validation can help you accomplish this. 

Explore how to use data validation in Google Sheets, as well as advanced data validation techniques, and how to solve common data validation issues.

What is data validation in Google Sheets?

Data validation allows you to ensure the data in your spreadsheet is error-free through the creation of predefined rules that restrict the values you can enter into cells. By setting specific criteria for what type of value you allow in each cell, you can ensure that your worksheets contain only authorized and appropriate data. The parameters you can set include numbers, dates, text, and custom formulas. For example, you could set a numerical value limit for a cell to allow the entry of only numbers within a specified range. 

You can also program Google Sheets to display an error message if a user enters data that doesn’t meet your preset criteria, preserving data quality. Additionally, you can utilize a dropdown list to prevent users from typing misspellings or typos by allowing them to only select from your provided options. This ensures that your spreadsheet data is accurate and consistent and can provide reliable insights upon data analysis. In this way, individuals or businesses who need to handle vast amounts of data can save the time and effort that is spent performing data checks manually. 

When should you use data validation?

The data validation function can help you make accurate analyses and models by ensuring that you don’t incorporate incorrect input into your data. Data validation is also beneficial when you need to share your worksheet or workbook with multiple people and ensure that the data they enter is consistent.

You can use data validation when you want to:

• Keep data input in a consistent, structured list throughout your worksheet 

• Limit the input of numbers outside a specific range

• Restrict the entry of dates or times outside a specific time frame

• Create a dropdown list of predefined choices, for example, department selections

• Restrict the number of text characters in a cell

Set up data validation in Google Sheets

To set up data validation in Google Sheets, you must first define a rule that determines what values you allow in your spreadsheet. Next, you need to create a dropdown list of these allowed choices, either from a range or by entering predefined values. Explore these steps in detail below.

Add a rule in data validation

Google Sheets lets you set a rule for data validation by either typing the allowed values in a validation list or by creating a new worksheet that contains a list of the allowed values. If you have a large number of allowed values, you should consider creating a new worksheet for these, as manual entry of these values is limited to 256 characters for the entire list.

If you want to add a separate worksheet with the allowed values, follow the steps below to define a data validation rule:

1. In your Google workbook where you have your application data, add a new worksheet for the allowed values. For example, if your workbook involves project management tasks, you can name your original datasheet as ProjectData and your sheet containing the allowed values as DataValues.

2. In this new sheet, allocate a column for your first set of allowed values, assuming you have more than one set, and add a column header. For example, in the DataValues sheet, you might choose column A and type in Project Status as the header in cell A1.

3. Under this column, enter all the allowed values. For the above example, this may be “Completed,” “In progress,” “Blocked,” or “Under review” in cells A2 to A5.

4. In your original datasheet, which is ProjectData here, go to the column that will contain the allowed values in the DataValues sheets, and select all the cells where you want to apply your rule, excluding the column header. For example, this may be column C, and you select cells C2 to C15 by excluding the header cell C1.

5. Go to the Data menu and click on Data Validation. This will open the Data validation rules pane.

6. Click on + Add rule.

Create a dropdown list from a range

7. Check the Apply to range field to ensure that you have selected the right range of cells for inserting the dropdown menus. For the above example, this field should display ProjectData!C2:C15.

8. In the Criteria menu, select Dropdown (from a range). A text field will appear underneath.

9. In this text field, click on the box icon on the right called Select data range. A Select a data range dialog box will open.

10. While this dialog box is open, navigate to the worksheet with the list of allowed values, which is DataValues in the example, and open it.

11. Select the appropriate range of cells in the column containing the allowed values. You will know you have selected a range as the text field in the Select a data range dialog box will display something. In the above example, this should include “Completed,” “In progress,” “Blocked,” or “Under review” in cells A2 to A5 as the range, which will appear as DataValues!A2:A5.

12. Click on OK in the dialog box. The Select a data range dialog box will close, and the Data validation rules pane will display the range you selected above the names of items that were in this range. This will form the allowed values for validation.

13. Click on Done at the bottom of the Data validation rules pane. 

Add a dropdown list for predefined values

If you don’t have too many allowed values and feel comfortable with manually typing in your allowed values, then you can follow the steps below to define a rule and add a dropdown list for your data. 

Consider the above example, where you have a data sheet called ProjectData with all your original data, and follow the steps below to set up a dropdown list for predefined values:

1. Select a cell or cells where you want to create a dropdown menu. For instance, your data sheet has column A as a Tasks list, where A1 is the header with names of tasks from A2 to A6. In column B, you want to add a dropdown based on the priority of each task, so you add the header Priority in B1. Then, select B2 to B6 as the range of cells that will have dropdown lists.

2. Click on Data, then Data validation, then + Add rule. The Apply to range field in the Data validation rules pane will display ProjectData!B2:B6 for the above example.

3. Under Criteria, select Dropdown.

4. You will see editable fields “Option 1”, “Option 2,” etc. Change these names to the names you want your dropdown list to display. You can click on Add another item to add more editable fields as per your needs. For example, in the above example, you could enter “Critical,” “High,” “Normal,” and “Low” for the task priority options.

5. Click on Done after you’ve entered the values.

Advanced data validation techniques

Advanced data validation techniques allow you to enhance data validation by incorporating dependent dropdowns and error messages for invalid input.

Using dynamic data validation

Dynamic data validation involves using dependent dropdowns where choices in a dropdown list automatically change based on selections in another dropdown list, allowing you to create more flexible and efficient spreadsheets. You can achieve this using Named Ranges and the INDIRECT formula in Google Sheets. 

To better understand how to use dynamic data validation, consider the example of a product inventory that has two categories: Clothes and Accessories. Each category has three subcategories: Dresses, Tops, and Jeans for Clothes and Shoes, Bags, and Jewelry for Accessories. You will need to create two dropdown lists, one for the category and another for the subcategory, where the dropdown values will change based on your choice in the first dropdown. You can find a summarized version of the steps to set up dynamic data validation below:

1. Prepare a list of your categories and subcategories for the two dropdown menus. 

2. Create your first dropdown list in a separate cell using the category names as the range.

3. Create Named Ranges for each category. Ensure to add the same name for the range as your category. 

4. In another cell, use the INDIRECT function to refer to the cell that contains the first dropdown list.

5. Create your second dropdown list using the range in the INDIRECT formula, which will create a dynamic dropdown, where the options in the second dropdown will change based on your choices in the first dropdown.

Set up error messages for invalid input

You can program Google Sheets to display an error message if your input is invalid.

1. After you’ve entered your data range and dropdown criteria in the Data validation rules pane, click on Advanced options.

2. Here, you will have the option to Show a warning, which will allow the user to enter invalid data but give them a warning message that the input doesn’t align with the rules, or Reject the input, which will not accept any invalid data.

3. You can also tick Show help text for a selected cell if you want to let the user know why their input is invalid.

Troubleshooting common data validation issues

While validating data in Google Sheets, you might encounter certain issues or error messages. Some common issues include:

• Dropdown options not showing correctly: Ensure the range you’ve selected for the dropdown includes all relevant items, and you haven’t selected any hidden rows or columns.

• Data validation not working: Ensure you’ve applied data validation to the correct range, you’re not copy-pasting data, and any formulas you’re using are error-free.

• Data input rejected: Ensure you’ve entered accurate validation criteria and removed any typos or extra spaces.

Summary of steps

Data validation in Google Sheets can help ensure that you’re entering accurate and error-free data in your spreadsheets. You can set up data validation by following the steps below:

• Select a range of cells for validation

• Click on Data → Data validation and add a validation rule

• Add predefined values manually or values from another list

• Add your preferences for error handling

• Save and test your dropdown lists

Explore data analysis on Coursera

Data validation is an important aspect of data analysis, which helps ensure the availability of high-quality and accurate data. Spreadsheet software like Google Sheets is popular in data analytics due to its ease of use in organizing numeric data. To learn more about data analysis and spreadsheet software, consider taking an online course on Coursera. With options like the Google Data Analytics Professional Certificate and the IBM Data Analytics with Excel and R Professional Certificate, you can have the opportunity to acquire key analytical skills and advanced knowledge of spreadsheet applications.