Data validation can be used in Google Sheets to control what a user can enter into a particular cell.
The following example shows how to use data validation in practice.
Example 1: Data Validation in Google Sheets
Suppose we are sending out a survey to users in Google Sheets in which we want them to type in cell B1 the number of days per week they exercise.
To ensure that users can only enter numbers between 0 and 7, we can click cell B1, then click the Data tab, then click Data validation:
In the new window that appears, we’ll fill in the following information:
- Cell range: data_validation!B1
- Criteria: number (between 0 and 7)
- On invalid data: Show warning
- Appearance: Show validation help text (with custom text)
Once we click Save, the data validation rule will automatically be applied to cell B2.
Now, if we type a number in cell B2 that is outside of the range 0-7 we’ll receive a warning message:
This warning message lets the user know that the input must be a number between 0 and 7.
If we instead enter a number between 0 and 7 then we won’t receive any warning message:
Notice that we don’t receive a warning message because we typed in a valid number.
Note: In this example we chose to use a number range as the criteria for the data validation but we could also specify to use a date, text, or list of items as the data validation instead.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Replace #N/A Values in Google Sheets
How to Ignore #N/A Values with Formulas in Google Sheets
How to Use ISERROR in Google Sheets