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.).

JCH List Validation selecting 3

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
  • 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.JCH Excel Validation (23)
  • 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).JCH Excel Validation (4)
  • 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.JCH Excel Validation (5)
  • 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). JCH Excel Validation (6)
  • 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.JCH Excel Validation (2)
  • 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 ValueNo validation is specified. Use this setting if you want to display an input message without checking for valid entries.
Whole NumberEntries 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.
DecimalWorks 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.
ListOnly 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.
DateOnly 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.
TimeOnly 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 LengthSpecifies the number of characters for entries. For example, you could require a product code that contains 4 digits.
CustomAllows 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 itJCH Excel Validation (6)
  • 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 messageJCH Excel Validation (7)
  • Finally click on OK
whole numbers validation 3

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 dateJCH Excel Validation (10)
  • 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 tickedJCH Excel Validation (11)
  • 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
List validation typing 3

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 worksheetJCH Excel Validation (12)
  • 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 arrowJCH Excel Validation (14)
  • Select your data source – the cell references will be shown in the data source boxJCH Excel Validation (15)
  • 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)JCH Excel Validation (18)
  • 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.

JCH Excel Validation (19)
  • 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 JCH Excel Validation (20)
  • Choose Circle Invalid Data
JCH Excel Validation (21)
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 CirclesJCH Excel Validation (22)

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 🙂

Data Validation in Excel

5 thoughts on “Data Validation in Excel

    • March 30, 2020 at 11:31 am
      Permalink

      Thank you! Hopefully there’ll be lots more to come too.

      Reply
    • May 1, 2020 at 10:38 am
      Permalink

      Thank you so much for taking the time to comment!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.