Data Validation in Excel

Data validation is a feature available in Excel that helps you to control the information that is entered in the worksheets. This tutorial describes ways to use the data validation feature and examples to illustrate how to implement it.

Data validation restricts entries to a specific type in a cell. It allows you to do the following:

• Create list of options / items in a cell
• Set range of values that can be entered in a cell
• Create a prompt message / warning explaining the kind of data allowed in a cell

Data validation dialog box can be activated by choosing: Data -> Validation in Excel menu.

Create list of options

You can create a list of the entries you will accept for a cell in the worksheet. Such lists of items can be typed directly into the “Source” box in the data validation dialog box or it can be typed in a row or column on any worksheet in the workbook.

Type a list directly in the dialog box

Follow these steps:

• Select a cell
• Select from menu: Data -> Validation
• On the Settings tab, click List in the Allow drop-down list
• By default, the ‘Ignore blank’ and ‘In-cell dropdown’ check boxes are selected. Do not change them
• In the Source box, type the list of entries that you wish, e.g.: “Yes”,”No”,”Maybe”
• Click OK
• In that cell, click the drop-down list and then click any item it contains.

Refer to the screenshot for example. Note if you try to manually enter anything other than the list of entries that have been created in the Source box, a stop message will appear and your only options are Retry or Cancel. 

Screenshot: Create a list by typing directly

You can also custom format your options such as displaying “1” and “0” as “Yes” and “No” respectively. Refer to screenshot and our tutorial titled Custom Formats in Excel to learn about custom formatting.

Type a list in row / column

If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. For example in column E44:E46, type in the repayment options. The way to enter the reference in the data validation dialog box is as shown below Screenshot: Create a list in worksheet

Note: if the list of options is stored on a different sheet in the same workbook, you need to name the range first before using it as data validation source in another sheet. The screenshot below shows an example of where the list of options is named “Products” (The name box is located to the left of the formula bar). As shown, in the Source box in data validation dialogue box, we type in “= Products” or press F3 and paste in the range name.

Screenshot: Create a list in different worksheet

Set range of values in a cell

You can place limits on the data that can be entered in a cell. You can set or exclude a range of numbers, or set the minimum and maximum for certain cells.

• Select a cell and click: Data -> Validation
• On the Settings tab, Allow drop-down, click either Whole number, Decimal, Date, Time, Text length In Data you could choose criteria such as between, not between, equal to, etc.
• You can then type values into the dialog box, or Refer to cells in the worksheet, or, Use formula to set the values

Refer to the examples in the screenshot for various validation criteria examples, more examples can be found in the accompanied workbook. Input / Error message could be added to these types of restricted cells to alert user on kind of data allowed.

Create input message / warning

Data validation Input Message

An Input Message can be displayed when the cell with data validation is selected:

• Select from menu: Data -> Validation -> Input Message -> Add the check mark in the box
• Type your message heading and message in the Title box and Input message box.

Screenshot: Various validation criteria examples

Error Alert

Click on the Error Alert tab to activate then add a check mark to the box. The alert message can be typed in the designated box. There are three styles of Error Alert from the drop-down list:

• Stop: User cannot enter invalid entry in the cell
• Warning: User could choose to enter invalid entry but the warning box will be displayed. If Yes is clicked, then the invalid entry is accepted.
• Information: This highlights the entry of invalid data; the user could leave the invalid entry in the cell.

Screenshot: Input Message and Error Alert