Google Sheets Course: Data Validation

As its name suggests, data validation allows to ensure the validity of entered data and to display an error message or reject the entry if the data is not valid.

This feature allows to validate data by limiting the possible input choices or by testing the entered value against a defined criteria.

The example file: lesson-10d.xlsx

Validation based on a range

Select the range that should contain valid data and click on "Data Validation" in the Data menu:

Add a rule and select "Dropdown (from a range)":

Now enter the range of cells that contains the list of valid data:

If you wish, you can assign colors to the different choices:

In the advanced options, you can (if necessary) define a custom validation message, choose whether to display a warning or whether to refuse input in case of invalid value (in this case, a value that is not part of the proposed choices) and modify the display style of the dropdown list:

Validation based on a list of choices

To add a drop-down menu whose choices are static, you can select "Dropdown":

This criteria then allows to directly list the possible choices:

Validation based on a number

To apply a criteria related to a numerical value, select one of the following criteria:

In this case, only data between 0 and 1000 will be accepted:

Validation based on a date

When the criteria is related to a date, select one of the following criteria:

Validation based on a text

When the criteria is related to a text, select one of the following criteria:

Validation based on a formula

For more personalized data validations, choose formula validation.

In this example, the formula will check if the entered value is unique and will display a warning in case of duplicates:

=COUNTIF($B$2:$B$16,B2)=1

The "C" is entered twice, the warning is then displayed: