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:

excel data validation png

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

excel data validation range

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

excel data validation dropdown 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:

excel data validation number

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

excel data validation error message

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:

excel data validation date

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:

excel data validation character count

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

excel data validation input message

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

excel data validation error alert

To display a custom message in case of an error:

excel data validation custom error message

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:

excel data validation formula

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

excel data validation duplicates