Data Validations in Google Sheets

Data validation is a feature in Google Sheets that allows you to restrict the type or range of data that can be entered in a cell or a range of cells. Data validation can help you prevent errors, ensure consistency, and improve the quality of your data.

In this article, I will explain what data validation is, why it matters, and how to use it in Google Sheets. I will also show you some examples of data validation with different criteria and options.

What Is Data Validation in Google Sheets?

Data validation is a way of setting rules for the data that can be entered in a cell or a range of cells. You can use data validation to:

Limit the data type to numbers, text, dates, lists, checkboxes, or custom formulas.
Specify the range or criteria for the data, such as greater than, less than, between, equal to, not equal to, contains, does not contain, etc.
Choose how to handle invalid data, such as showing a warning message, rejecting the input, or clearing the cell.
Provide help text or instructions for the user when they select or enter data in the cell.

Data validation can help you ensure that your data is accurate, consistent, and formatted correctly. For example, you can use data validation to:

Prevent users from entering negative numbers, decimals, or text in a column that should only contain positive integers.
Create a drop-down list of predefined options for users to choose from, such as product names, categories, or countries.
Validate dates and times to make sure they are within a certain period or follow a specific format.
Use custom formulas to check for complex conditions or dependencies between cells.

Why Data Validation Matters

Data validation is important for several reasons:

It reduces the chances of errors and inconsistencies in your data, which can affect your calculations, analysis, and reports.
It improves the usability and readability of your spreadsheet by providing clear and relevant information for the users.
It saves time and effort by preventing users from entering invalid or unnecessary data and by providing them with easy and convenient options.

Data validation is especially useful when you share your spreadsheet with other people or use it for collaborative purposes. By using data validation, you can:

Control the input and output of your spreadsheet and ensure that everyone follows the same rules and standards.
Protect your spreadsheet from unwanted changes or modifications by other users.
Guide and assist other users on how to use your spreadsheet correctly and efficiently.

How to Use Data Validation in Google Sheets

To use data validation in Google Sheets, follow these steps:

Select the cell or range of cells that you want to apply data validation to.
Go to the Data menu and click on Data Validation. This will open the Data Validation dialog box on the right side of the screen.
In the Criteria section, choose the type of data that you want to allow in the cell or range. You can choose from Number, Text, Date, List from a range, List of items, Checkbox, or Custom formula is.
Depending on the type of data that you choose, you will see different options for setting the range or criteria for the data. For example, if you choose Number, you can specify if the number should be greater than, less than, between, equal to, not equal to, etc., and enter the values accordingly.
In the On invalid data section, choose how you want Google Sheets to handle invalid data. You can choose from Show warning (which will allow invalid data but show a warning message), Reject input (which will prevent invalid data from being entered), or Remove input (which will clear the cell if invalid data is entered).
In the Appearance section, you can enter a help text that will appear when the user selects or enters data in the cell or range. This can be useful for providing instructions or explanations for the user.
Click Save to apply the data validation rules to the cell or range.

You can also access the Data Validation dialog box by right-clicking on a cell or range and choosing Data Validation from the menu.

Examples of Data Validation in Google Sheets

Here are some examples of how you can use data validation in Google Sheets with different criteria and options.

Example 1: Setting a Number Limit Using Data Validation

In this example, we have a spreadsheet that tracks sales figures for different products. We want to limit the sales amount column to only accept positive integers between 1 and 1000.

To do this:

Select column C (Sales Amount).
Go to Data > Data Validation.
In the Criteria section, choose Number from the drop-down menu.
Choose Between from the second drop-down menu and enter 1 and 1000 in the boxes below.
In the On invalid data section, choose Reject input from the drop-down menu.
In the Appearance section, enter “Please enter a positive integer between 1 and 1000” in the box below.
Click Save.

Now, if we try to enter a negative number, a decimal, or a text in column C, we will see an error message and the input will be rejected.

Example 2: Creating a Drop-Down List Using Data Validation

In this example, we have a spreadsheet that collects feedback from customers. We want to create a drop-down list of ratings for the customers to choose from, such as Excellent, Good, Fair, Poor, or Very Poor.

To do this:

Select column D (Rating).
Go to Data > Data Validation.
In the Criteria section, choose List of items from the drop-down menu.
Enter “Excellent,Good,Fair,Poor,Very Poor” in the box below, separated by commas.
In the On invalid data section, choose Show warning from the drop-down menu.
In the Appearance section, enter “Please choose a rating from the drop-down list” in the box below.
Click Save.

Now, if we click on any cell in column D, we will see a drop-down list of ratings that we can choose from.

Example 3: Using a Custom Formula With Data Validation

In this example, we have a spreadsheet that calculates the total price of items based on the quantity and unit price. We want to use a custom formula to validate that the quantity is not greater than the available stock.

To do this:

Select column B (Quantity).
Go to Data > Data Validation.
In the Criteria section, choose Custom formula is from the drop-down menu.
Enter “=B2<=D2” in the box below. This formula checks if the quantity in B2 is less than or equal to the stock in D2. You can use relative references in your formula and they will adjust automatically for each cell in the range.
In the On invalid data section, choose Show warning from the drop-down menu.
In the Appearance section, enter “Please enter a quantity that is not greater than the available stock” in the box below.
Click Save.

Now, if we try to enter a quantity that is greater than the stock in column B, we will see a warning message and a red triangle in the cell.

Conclusion

Data validation is a powerful feature in Google Sheets that can help you restrict and validate the data that can be entered in your spreadsheet. Data validation can help you prevent errors, ensure consistency, and improve the quality of your data.

You can use data validation to limit the data type to numbers, text, dates, lists, checkboxes, or custom formulas. You can also specify the range or criteria for the data and choose how to handle invalid data. You can also provide help text or instructions for the user when they select or enter data in the cell or range.

To use data validation in Google Sheets, you need to select the cell or range that you want to apply data validation to and go to Data > Data Validation. Then you need to choose the criteria and options for your data validation and click Save.

You can also access data validation by right-clicking on a cell or range and choosing Data Validation from the menu.

I hope this article has helped you understand what data validation is and how to use it in Google Sheets. If you have any questions or feedback, please let me know.

Data Validations in Google Sheets