Data Validation Basics

Data > Data Validation

Create a drop-down list (most common use)

You can limit the choice of values from a previously defined list.

To create this, click on Data > Data Validation > Allow > List and define the values in Source.

There are two ways to enter values.

• Define these explicitly separated by a comma.
• Select a range of cells with the desired values.

An error message is shown when a value not belonging to the list is entered.

Allow other kind of data (not from a list)

The drop-down menu includes 95 % of the uses of data validation, but the functionality is called data validation, not drop-down list. There are several other kind of data validations to define which are not a list and do not show a drop-down.

Allow whole numbers

You can e.g. only allow whole numbers between 0 and 20. The error message will be shown when entering a negative number (like -1) or decimal number (like 3.7).

Allow value between 0 % and 100 %

A percentage is interpreted by Excel as any other number, e.g. 21 % = 0.21. You can therefore define a percentage input field indirectly with a data validation between 0 % = 0, 100 % = 1, and cell format as percentage.

Input message (useful even without data validation)

When allowing a values which do not belong to a list, there is in general no indication to the user of which ones can be entered. You can provide such an indication through the Input Message tab. It will be only shown when the cell is selected.

It is possible to define an input message even if Any value is allowed. This is useful to enter remarks about the content of the cells.

Error Alert

When entering a non-allowed value, a standard error alert is shown.

This can be personalized on the Error Alert tab to show a more meaningful message.

You can use other kind of error alerts. These can be picked under Style.

• Stop
• Warning
• Information

Stop

Stop is the default error alert.

• Retry selects the cell again to edit the entered value.
• Cancel deletes the entered value.
• Help opens the data validation documentation.

Warning

With Warning a value can be entered anyway if confirmed. E.g. you might have a situation where in most cases a past date should be entered, while it should still be possible to enter future dates sometimes.

• Yes leaves the entered value, even if it doesn’t meet the allowance criterium.
• No selects the cell again to edit the value.
• Cancel deletes the entered value.
• Help opens the data validation documentation.

Information

With Information the value is entered without explicit confirmation.

• OK leaves the entered value, even if it doesn’t meet the allowance criterium.
• Cancel deletes the entered value.
• Help opens the data validation documentation.

Deactivate error alert

You can choose to deactivate the error alert, in most cases it will be as if there is no data validation in place.

This is useful to show standard options from a drop-down list while still allowing to entered custom values.

Download

• Example
• Exercise
• Solution