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.

Data validation in Excel

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

Add Validation

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

---

I hope you enjoyed adding data validation to your spreadsheets in Excel. For more resources on Microsoft Excel, Google Sheets and Libre Calc, just click here 🙂