Important limitation

It is important to understand that data validation can be easily defeated.

This Input Message is completely optional.

The Error Alert Tab controls how validation is enforced.

Example data validation message displayed when cell selected

The table below summarizes behavior for each error alert option.

Whole Number- only whole numbers are allowed.

Once the whole number option is selected, other options become available to further limit input.

Example data validation message displayed when cell selected

For example, you’ve got the option to require a whole number between 1 and 10.

Decimal- works like the whole number option, but allows decimal values.

List- only values from a predefined list are allowed.

Example data validation message displayed when cell selected

The values are presented to the user as a dropdown menu control.

Allowed values can be hardcoded directly into the prefs tab, or specified as a range on the worksheet.

Date- only dates are allowed.

Example data validation message displayed when cell selected

Time- only times are allowed.

Text length- validates input based on number of characters or digits.

For example, you could require code that contains 5 digits.

Example data validation message displayed when cell selected

Custom- validates user input using a custom formula.

In other words, you’re able to write your own formula to validate input.

Custom formulas greatly extend the options for data validation.

data validation error alert invalid product code example

In practice, this setting seems to affect only the command “circle invalid data”.

When enabled, blank cells are not circled even if they fail validation.

For example, with sizes (i.e.

Example data validation dropdown menu

small, medium, etc.)

it’s possible for you to also usenamed rangesto specify values.

If they enter a value like “apple” in A1, ISNUMBER returns FALSE and data validation fails.

Data validation controls on the data tab of the ribbon

Troubleshooting formulas

Excel ignores data validation formulas that return errors.

The concept is exactly the same.

Data validation formula examples

The possibilities for data validation custom formulas are virtually unlimited.

Data validation window has three main tabs

The two circled values return FALSE with this formula.

pop in the keyboard shortcut Control + G, then hit the Special button.

Data validation settings tab example

Data validation settings tab

Data validation error alert tab

Example data validation error alert message

Data validation dropdown menu with hardcoded values

Data validation dropdown menu hardcoded values in use

Data validation dropdown menu values with worksheet reference

Data validation dropdown menu values with named range

Testing data validation with dummy formulas

Circle invalid values with data validation - menu

Data validation invalid values circled on worksheet

Go to special button

Select data validation with go to special dialog

Using paste special to copy data validation

Use the Clear All button to remove data validationhttps://exceljet.net/sites/default/files/images/articles/inline/Clear%20all%20to%20remove%20data%20validation%20completely.png

Data validation settings tab

Data validation error alert tab

Data validation dropdown menu hardcoded values in use

Testing data validation with dummy formulas