Everything You Need to Know About Data Validation in Microsoft Excel
Whether you want to restrict data inputs, ensure consistency, or avoid errors, data validation in Microsoft Excel is the tool for you. It can also serve as a vital safety net if you plan to share your workbook with people who are new to Excel or unfamiliar with your spreadsheet.
Data validation can be easily overridden or canceled. It is not a foolproof way to guarantee that the data entered into cells falls within the specified parameters.
To apply data validation rules to a selected cell or range, head to the “Data” tab on the ribbon, and click the top half of the “Data Validation” icon if you’re using a wide screen, or the left half of the icon if you’re using a narrower screen.
Types of Data Validation Rules You Can Set in Excel
Once you’ve opened the Data Validation dialog box, you’ll see three tabs.
Of these tabs, the Settings tab is where you can create the data validation rules. Here are the different options you can select in the Allow field.
Any Value (Default)
By default, cells in Excel can accept any input, whether text, numbers, or a formula. This is why the Allow field in the Settings tab of the Data Validation dialog box defaults to Any Value.
Although this option might seem redundant, in fact, it’s handy if you want to allow any input into a cell but have a message appear when the cell is selected. Scroll to the “Setting a Data Validation Input Message in Excel” section of this article for more information on input messages.
Whole Number
The Whole Number option restricts entries to integers. That is to say, if a decimalized number is input into the cell, it won’t match the data validation rule. After selecting “Whole Number” in the Allow field, you’ll see various other restriction options that you can apply to further specify the parameters the whole numbers must follow.
As you can see in the screenshot above, selecting “Between” in the Data field allows you to input a minimum and maximum value.
As well as specifying the parameters as values, you can also reference cells. Here, the minimum value is typed into cell L2, and the maximum value is typed into cell L3.
Referencing cells rather than typing values in these fields means you can easily change the limitations without having to relaunch the dialog box.
Changes to a data validation rule do not apply to existing data input into a cell. They are only enforced when new data is entered or existing data is edited.
Decimal
Selecting “Decimal” in the Allow list means that the cells to which the rule applies can accept both whole numbers and decimals. As with the Whole Number option, when you select “Decimal,” further options appear that let you add specificity to the rule, and you can input values or cell references into these additional fields.
List
When you select “List” in the Allow field, only values from a predefined list are permitted, and these appear as a drop-down menu when the cell is selected.
As well as clicking the down arrow in a cell containing a drop-down list, you can also select the cell and use the Alt+Down Arrow keyboard shortcut.
There are various ways to define a list of accepted values. First, you can type individual items into the Source field, each separated by a comma.
Second, you can select a range of cells containing items you want to appear in the list. If the cells make up a whole column in a formatted Excel table, the range will expand and shrink automatically when rows are added to or deleted from the table.
Third, after naming a range containing the list of options, in the Source field, type = and input this name into the Source field.
The order of the options in the data validation list is the same as the order in which they are arranged in the source range. So, sort the source range into alphabetical or numeric order to make the drop-down list easier to use.
When deciding which source type you’re going to use, consider whether you are likely to add more options to the list. The most efficient way to create a variable drop-down list is to format the source cells as an Excel table.
|
Source |
How to Add More Options to the List |
|---|---|
|
Typed directly into the Source field |
In the Source field of the Data Validation dialog box, add a comma and type the extra option. |
|
A whole column in an Excel table |
Type the new option directly below the existing options, and the table column (and source range) will expand automatically. Alternatively, insert a new row into the center of the table, and add the extra option there. |
|
Regular range of cells |
Insert a new row in the center of the regular range, and add the extra option there. Alternatively, reopen the “Data Validation” dialog box, and adjust the range. |
|
Named range of cells |
Insert a new row in the center of the named range, and add the extra option there. Alternatively, click Formulas > Name Manager, and redefine the cells included in the named range. |
Avoid selecting a whole column as the data validation source, as this prevents the header row from being included in the drop-down list and ensures your workbook’s performance isn’t adversely affected by the data validation rule.
Date
When you select “Date” in the Allow field, various other options appear where you can define the date parameters in more detail.
When typing a date into one of the text fields, ensure you use a recognized date format according to your system settings. For example, for December 31, 2025, in the U.S., you would need to type 12/31/2025, and in the U.K., 31/12/2025. Excel will let you know if you haven’t entered a recognized format.
Similarly, if you reference cells containing dates, ensure they have an appropriate date number format.
Time
As with the Data option in the Allow field of the Data Validation dialog box, selecting “Time” opens up other options for specifying what constitutes a valid entry.
When typing a time into one of the fields, you can use a 12-hour or 24-hour format. For example, typing 5:00:00 PM results in the same parameter as typing 17:00:00. The same applies to cells containing dates if you insert cell references into these fields.
Text Length
Choose “Text Length” if you want the cell to have a character limitation. In this example, inputs in the selected cells are limited to 20 characters.
Don’t be fooled by the word text in Text Length—the cell input can be textual, numeric, or any other character. If you want to restrict a cell’s input to text only, head straight to the next section.
Custom
Much like custom number formatting, Excel’s custom data validation rules are incredibly powerful and greatly extend the program’s capabilities.
After you select “Custom,” you’ll see a Formula field at the bottom of the dialog box.
This means you can write your own formula to determine particular parameters for a valid input.
For example, to allow text only in cells A1 to A20, select these cells, open the “Data Validation” dialog box, select “Custom,” and type:
=ISTEXT(A1:A20)
To allow numbers only, type:
=ISNUMBER(A1:A20)
To allow entries starting with “HTG” only, use a wildcard:
=COUNTIF(A1:A20,"HTG*")
Finally, to allow unique entries only in cells A1 to A5, type:
=COUNTIF($A$1:$A$5,A1)
Setting a Data Validation Input Message in Excel
The second of the three tabs in Excel’s Data Validation dialog box is Input Message.
This optional tab lets you define a message to display when cells containing a data validation rule are selected. After opening this tab and ensuring the checkbox at the top of the dialog box is checked, type a title and an input message, and click “OK.”
This is how the message appears when an affected cell is selected.
Input messages don’t affect what can be entered into a cell—they merely act as helper notices to remind you or someone else of what should be entered into the cell.
If you change the rule, remember to change the input message so everything aligns!
Setting an Error Alert for Incorrect Data Inputs in Excel
The third and final tab of the Data Validation dialog box is Error Alert, which lets you trigger a message if data input into a cell doesn’t match the data validation rules.
Newly defined alerts only appear when new, invalid data is entered or existing data is invalidly edited.
A stop error alert (see the table below) appears by default when invalid data is entered into a cell. You can deactivate alerts by unchecking the checkbox at the top of the dialog box after opening the Error Alert tab.
You can choose from three alert styles: Stop, Warning, and Information.
Here’s what you can expect for each:
|
Alert Style |
Behavior When an Invalid Entry Is Input |
|---|---|
|
Stop (default) |
This stops an invalid input from being entered into a cell. Clicking “Retry” lets you type a different value, and clicking “Cancel” clears the cell. |
|
Warning |
This warns you that the data entered is invalid. Clicking “Yes” keeps the invalid entry, clicking “No” lets you edit the entry, and clicking “Cancel” clears the cell. |
|
Information |
This informs you that the data entered is invalid. Clicking “OK” keeps the invalid entry, and clicking “Cancel” clears the cell. |
Regardless of which alert style you choose, type a title and error message to decide what appears on screen when an invalid entry is entered. This is what a Stop alert looks like.
Check “Ignore Blanks” in the Settings tab of the Data Validation dialog box to prevent an alert from appearing when a cell is left blank.
Other Excel Data Validation Tips
Here are some additional things you should know when using data validation in Microsoft Excel:
- When you apply data validation rules to a column in an Excel table, they’ll automatically apply to new rows as you add them.
- To highlight all cells containing values that don’t adhere to the data validation rules you have set, click the down arrow next to the Data Validation icon in the Data tab on the ribbon, and select “Circle Invalid Data.” You’ll then see circles around the relevant cells.
- To select all the cells in a spreadsheet to which data validation rules apply, press F5 to launch the Go To dialog box, click “Special,” check “Data Validation,” and click “OK.”
- To copy a data validation rule from one cell to another, first, select a cell or range of cells containing the data validation rule you want to copy. Then, select the cell where you want to duplicate the rule, and press Ctrl+Alt+V to launch the Paste Special dialog box. Finally, check “Validation,” and click “OK.”
- Finally, to clear data validation rules from the selected cells, open the “Data Validation” dialog box, and click “Clear All.”
Data validation isn’t the only tool you should learn if you want to become an Excel power user. For example, Power Query is great for importing and shaping data, PivotTables are essential for quick data summarization and analysis, and versatile functions, like XLOOKUP, make finding and retrieving data super easy.
- OS
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
