Data Validation in Excel

What is data validation?

Data validation in Excel ensures that the data entered into a cell is correct, by restricting the sort of data that can be entered (for example, a date or a number within a range) or by making the user choose their entry from a drop-down list (for example, January, February, March etc.).

Why use it?

Data validation is used to ensure consistent data entry. There will be fewer mistakes entering data, if users are restricted with the sort of data they can actually enter.

Note – If the workbook is shared you must first remove its shared status before you can set data validation.

Add Validation

Select the cell(s) that you want to add validation to.
Click on the Data tab.
Click on Data Validation in the Data Tools Group.
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert.
In the Settings tab, choose the type of data that you want to user to input (see table below).
Use the Data drop-down to specify the conditions for that type of data. So, for a whole number you may want it to be between 1 and 100, or, for a date you may want it to be before (less than) January 2020, or, you may want to specify the exact length of the text needed.
Ensure the Ignore blank tick box has a tick in it, if you want to ignore blank spaces.
Click OK (Unless you want to add input instructions and/or error messages – see instructions below).

Add Input Message (Optional)

To add a title and a message to your rule, click on the Input Message tab, and then type a title and input message. (If no input message is set, no message will appear when a user selects the validated cell. The input message has no effect on what the user can actually enter, it simply displays a message to let the user know what is allowed or expected.
If you added an input message, make sure the Show input message when cell is selected tick box has a tick in it, to display your message when the user selects or hovers over the selected cell(s).
Click OK (unless you want to add an error alert – see instructions below).

Add Error Alert (Optional)

Click on the Error Alert Tab. This controls how validation is enforced. When it is set to Stop, any invalid data will trigger a warning message and the input is not allowed. However, when it is set to Information or Warning, a warning message is displayed, but the user can ignore the message and enter invalid data.
Click OK.

Note – If a user copies data from a cell without validation to a cell with data validation, any validation rules are ignored. Data validation is a good way to let users know what is allowed or expected, but it cannot guarantee input.

Types of Data

Any Value	No validation is specified. Use this setting if you want to display an input message without checking for valid entries.
Whole Number	Entries must be integers (whole numbers). Then, select a comparison operator (for example, less than, more than, between, is equal to etc.) from the Data drop-down list.
Decimal	Works in the same way as the whole number option above, but allows decimals. For example, with the Decimal option configured to allow values greater than 2, values like 6.5, 2.5, and 3.1 would all be allowed.
List	Only allows values from a predefined list, which are used as drop-down options. Values can be either typed directly into the Settings tab and separated by a comma, or a specified range on a worksheet. Make sure In-cell drop-down is ticked, to display a drop-down arrow that provides the user with a list to select from.
Date	Only dates are allowed. For example, you can require a date between January 1, 2020 and December 31, 2021 or a date after April 25, 2020.
Time	Only times are allowed. For example, you can require a time between 8:00 am and 5:30 pm or only allow times before 11:00 pm.
Text Length	Specifies the number of characters for entries. For example, you could require a product code that contains 4 digits.
Custom	Allows the user to enter a formula, use an expression, or refer to a calculation in another cell to determine valid entries. Using custom formulas extends the options for data validation. For example, you could use a formula to ensure an entry is uppercase, or that a date is a weekday in the next 90 days. Simply, enter a formula in the Formula Text box, starting with an equal sign = The formula must evaluate either True or False.

Example 1 – Data entered must be whole numbers between 1 and 100

To set validation so that a user can only enter whole numbers –

Select the cell or cells that you want to add validation to
Click on the Data tab
Click on Data Validation in the Data Tool Group
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert
In the Settings tab, choose Whole Number
Use the Data drop-down and select Between
In the Minimum box type 1 and in the Maximum box type 100
Click on the Input Message tab, and type in a title and message. This will display as the user clicks into the cell or hovers over it
Click on the Error Alert tab
Choose the Stop style (as you don’t want the user to enter incorrect data), then type an error title and message
Finally click on OK

Example 2 – Data entered must be within certain dates

Select the cell or cells that you want to add validation to
Click on the Data tab
Click on Data Validation in the Data Tool Group
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert
In the Settings tab, choose Date from the Allow drop-down box
Under the Data heading, use the drop-down to select Between
In the Start Date type a date and in the End Date type your end date
Click on the Input Message tab, and type in a title and message. This will display as the user clicks into the cell or hovers over it
Click on the Error Alert tab. Choose the Stop style (as you don’t want the user to enter incorrect data), then type an error title and message
Finally click on OK.

Example 3 – Data must be selected from a drop-down list (Typing it in)

Select the cell or cells that you want to add validation to
Click on the Data tab
Click on Data Validation in the Data Tool Group
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert
Choose List from the Allow drop-down box
In the Source box type in the options you want in your drop-down, making sure they are separated by a comma
Make sure the In-cell drop-down is ticked
Click on the Input Message tab, and type in a title and message
This will display as the user clicks into the cell or hovers over it
Click on the Error Alert tab
Choose the Stop style (you don’t want the user to enter incorrect data), then type an error title and message
Finally click on OK

Example 4 – Data must be selected from a drop-down list (List in worksheet)

Before you add validation, you much have the list (that you want as your drop-down options) in a worksheet
Select the cell or cells that you want to add validation to
Click on the Data tab
Click on Data Validation in the Data Tool Group
A dialogue box will appear, with three tabs – Settings, Input Message and Error Alert
Choose List from the Allow drop-down box
In the Source box click on the up arrow
Select your data source – the cell references will be shown in the data source box
Click on the icon on the right-hand side of the source box to maximise it again
Make sure the In-cell drop-down is ticked
Click on the Input Message tab, and type in a title and message
This will display as the user clicks into the cell or hovers over it
Click on the Error Alert tab
Choose the Stop style (as you don’t want the user to enter incorrect data), then type an error title and message
Finally click on OK

Deleting Validation

To get rid of any validation –

Select the cell or cells where you want to get rid of validation
Click on the Data tab
Select Data Validation in the Data Tool Group
Click on Clear All (the data validation will show Any Value in the Allow drop-down box)
Click on OK

Editing Validation

Select the cell or cells where you want to get rid of validation
Click on the Data tab
Select Data Validation in the Data Tool Group
Make any changes
Make sure the box labelled Apply these changes to other cells with the same settings is ticked if necessary (this will update any validation that has been applied to other cells when it matches the (original) validation of the cell(s) being edited)
Click OK

Highlighting Invalid Data

Often you will apply data validation after some or even all of your data has been entered. The warnings and restrictions of data validation only apply while entering data, so you may need a way of highlighting any invalid data that has already been entered.

Select the cell(s) that you want to check, or click into the column
Click on the Data tab
In the Data Tool Group, click on the drop-down next to Data Validation
Choose Circle Invalid Data

Removing the Highlighting

Click on the Data tab
In the Data Tool Group, click on the drop-down next to Data Validation
Choose Clear Validation Circles

Just click here to return to the resources page.