Excel Training: Data Validation

Data validation allows to ensure the validity of entered data and to display an error message and reject the entry if the data is not valid.

The sample file: lesson-10.xlsx

Validation based on a range

Select the range that should contain valid data and click on "Data Validation" from the "Data" tab:

Then choose "List" and select the range of cells that will make up the list of allowed data:

The advantage with list validation is that the list of allowed choices is then displayed as a drop-down list:

Validation based on a number

To apply data validation based on a numerical value, select the "Whole number" or "Decimal" choice if decimal numbers are allowed:

In this case, only whole numbers from 1 to 100 are allowed. Otherwise, an error message is returned and the entry is rejected:

Date or time validation

It is also possible to validate data based on the date or time. In this case, the entered date must be later than the one indicated:

Validation based on the number of characters

Data validation also allows defining the number of allowed characters. In this case, the number of characters must be between 4 and 12:

To display an informative message during input, define this message from the second tab:

To make the error message more understandable to the user, you can customize it from the "Error Alert" tab:

To display a custom message in case of an error:

Validation based on a formula

When no other mode of validation is suitable, choose formula validation for much more customized data validations.

In this example, the formula =COUNTIF($B$2:$B$13,B2)=1 (which checks if there's only one instance of the entered value in the cell range) will display a warning in case of duplicates:

Here, the "D" is entered twice, so the warning is displayed: