The tutorial explains how to do Data Validation in Excel: create a validation rule for numbers, dates or text values, make data validation lists, copy data validation to other cells, find invalid entries, fix and remove data validation.
When setting up a workbook for your users, you may often want to control information input into specific cells to make sure all data entries are accurate and consistent. Among other things, you may want to allow only particular data type such as numbers or dates in a cell, or limit numbers to a certain range and text to a given length. You may even want to provide a predefined list of acceptable entries to eliminate possible mistakes. Excel Data Validation allows you to do all these things in all versions of Microsoft Excel 365, 2021, 2019, 2016, 20013, 2010 and lower.
What is data validation in Excel?
Excel Data Validation is a feature that restricts (validates) user input to a worksheet. Technically, you create a validation rule that controls what kind of data can be entered into a certain cell.
Here are just a few examples of what Excel's data validation can do:
- Allow only numeric or text values in a cell.
- Allow only numbers within a specified range.
- Allow data entries of a specific length.
- Restrict dates and times outside a given range.
- Restrict entries to a selection from a drop-down list.
- Validate an entry based on another cell.
- Show an input message when the user selects a cell.
- Show a warning message when incorrect data has been entered.
- Find incorrect entries in validated cells.
For instance, you can set up a rule that limits data entry to 4-digit numbers between 1000 and 9999. If the user types something different, Excel will show an error alert explaining what they have done wrong:
How to do data validation in Excel
To add data validation in Excel, perform the following steps.
1. Open the Data Validation dialog box
Select one or more cells to validate, go to the Data tab > Data Tools group, and click the Data Validation button.
You can also open the Data Validation dialog box by pressing Alt > D > L, with each key pressed separately.
2. Create an Excel validation rule
On the Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any of the following:
- Values - type numbers in the criteria boxes like shown in the screenshot below.
- Cell references - make a rule based on a value or formula in another cell.
- Formulas - allow to express more complex conditions like in this example.
As an example, let's make a rule that restricts users to entering a whole number between 1000 and 9999:
With the validation rule configured, either click OK to close the Data Validation window or switch to another tab to add an input message or/and error alert.
3. Add an input message (optional)
If you want to display a message that explains to the user what data is allowed in a given cell, open the Input Message tab and do the following:
- Make sure the Show input message when cell is selected box is checked.
- Enter the title and text of your message into the corresponding fields.
- Click OK to close the dialog window.
As soon as the user selects the validated cell, the following message will show up:
4. Display an error alert (optional)
In addition to the input message, you can show one of the following error alerts when invalid data is entered in a cell.
| Alert type | Description |
| Stop (default) |  The strictest alert type that prevents users from entering invalid data. You click Retry to type a different value or Cancel to remove the entry. |
| Warning |  Warns users that the data is invalid, but does not prevent entering it. You click Yes to input the invalid entry, No to edit it, or Cancel to remove the entry. |
| Information |  The most permissive alert type that only informs users about an invalid data entry. You click OK to enter the invalid value or Cancel to remove it from the cell. |
To configure a custom error message, go to the Error Alert tab and define the following parameters:
- Check the Show error alert after invalid data is entered box (usually selected by default).
- In the Style box, select the desired alert type.
- Enter the title and text of the error message into the corresponding boxes.
- Click OK.
And now, if the user enters invalid data, Excel will display a special alert explaining the error (like shown in the beginning of this tutorial).
Note. If you do not type your own message, the default Stop alert with the following text will show up: This value does not match the data validation restrictions defined for this cell.
Excel data validation examples
When adding a data validation rule in Excel, you can choose one of the predefined settings or specify custom criteria based on your own validation formula. Below we will discuss each of the built-in options, and next week we will have a closer look at Excel data validation with custom formulas in a separate tutorial.
As you already know, the validation criteria are defined on the Settings tab of the Data Validation dialog box (Data tab > Data Validation).
Whole numbers and decimals
To restrict data entry to a whole number or decimal, select the corresponding item in the Allow box. And then, choose one of the following criteria in the Data box:
- Equal to or not equal to the specified number
- Greater than or less than the specified number
- Between the two numbers or not between to exclude that range of numbers
For example, this is how you create an Excel validation rule that allows any whole number greater than 0:
Date and time validation in Excel
To validate dates, select Date in the Allow box, and then pick an appropriate criteria in the Data box. There are quite a lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a specific date, and more.
Similarly, to validate times, select Time in the Allow box, and then define the required criteria.
For example, to allow only dates between Start date in B1 and End date in B2, apply this Excel date validation rule:
To validate entries based on today's data and current time, make your own data validation formulas as shown in these examples:
- Validate dates based on today's date
- Validate times based on current time
Text length
To allow data entry of a specific length, select Text length in the Allow box, and choose the validation criteria in accordance with your business logic.
For example, to limit the input to 10 characters, create this rule:
Note. The Text length option limits the number of characters but not the data type, meaning the above rule will allow both text and numbers under 10 characters or 10 digits, respectively.
Excel data validation list (drop-down)
To add a drop-down list of items to a cell or a group of cells, select the target cells and do the following:
- Open the Data Validation dialog box (Data tab > Data Validation).
- On the Settings tab, select List in the Allow box.
- In the Source box, type the items of your Excel validation list, separated by commas. For example, to limit the user input to three choices, type Yes, No, N/A.
- Make sure the In-cell dropdown box is selected in order for the drop-down arrow to appear next to the cell.
- Click OK.
The resulting Excel data validation list will look similar to this:
Note. Please be careful with the Ignore blank option, which is selected by default. If you are creating a drop-down list based on a named range that has at least one blank cell, selecting this check box allows entering any value in the validated cell. In many situations, it is also true for validation formulas: if a cell referenced in the formula is blank, any value will be allowed in the validated cell.
Other ways to create a data validation list in Excel
Supplying comma-separated lists directly in the Source box is the fastest way that works well for small dropdowns that are unlikely to ever change. In other scenarios, you can proceed with one of the following ways:
- Dropdown data validation list from a range of cells
- Dynamic data validation list from a named range
- Dynamic data validation list from Excel table
- Cascading (dependent) drop down list
- Excel drop down list with multiple selections
Custom data validation rules
In addition to built-in Excel data validation rules discussed in this tutorial, you can create custom rules with your own data validation formulas. Here are just a few examples:
- Allow numbers only
- Allow text only
- Allow text beginning with specific characters
- Allow only unique entries and disallow duplicates
For more examples, please see Custom data validation rules and formulas.
How to edit data validation in Excel
To change an Excel validation rule, perform these steps:
- Select any of the validated cells.
- Open the Data Validation dialog box (Data tab > Data Validation).
- Make the required changes.
- Select the Apply these changes to all other cells with the same settings check box to copy the changes you've made to all other cells with the original validation criteria.
- Click OK to save the changes.
For instance, you can edit your Excel data validation list by adding or removing items from the Source box, and have these changes applied to all other cells containing the same drop-down list:
How to copy Excel data validation rule to other cells
If you've configured data validation for one cell and wish to validate other cells with the same criteria, you don't have to re-create the rule from scratch.
To copy the validation rule in Excel, perform these 4 quick steps:
- Select the cell to which the validation rule applies and press Ctrl + C to copy it.
- Select other cells you want to validate. To select non-adjacent cells, press and hold the Ctrl key while selecting the cells.
- Right-click the selection, click Paste Special, and then select the Validation option.
Alternatively, press the Paste Special > Validation shortcut: Ctrl + Alt + V, then N.
- Click OK.
Tip. Instead of copying data validation to other cells, you can convert your dataset to an Excel table. As you add more rows to the table, Excel will apply your validation rule to new rows automatically.
How to find cells with data validation in Excel
To quickly locate all validated cells in the current worksheet, go to the Home tab > Editing group, and click Find & Select > Data Validation:
This will select all cells that have any data validation rules applied to them:
How to remove data validation in Excel
Overall, there are two ways to remove validation in Excel: the standard approach designed by Microsoft and the mouse-free technique devised by Excel geeks who would never take their hands off the keyboard unless absolutely necessary (e.g. to take a cup of coffee :)
Method 1: Regular way to remove data validation
Normally, to remove data validation in Excel worksheets, you proceed with these steps:
- Select the cell(s) with data validation.
- On the Data tab, click the Data Validation button.
- On the Settings tab, click the Clear All button, and then click OK.
Tips:
- To remove data validation from all cells on the current sheet, use the to select all of the validated cells.
- To remove a certain data validation rule, select any cell with that rule, open the Data Validation dialog window, check the Apply these changes to all other cells with the same settings box, and then click the Clear All button.
As you see, the standard method is pretty fast but does require a few mouse clicks, no big deal as far as I'm concerned. But if you prefer working with the keyboard over a mouse, you may find the following approach appealing.
Method 2: Paste Special to delete data validation rules
De jure, Excel Paste Special is designed for pasting specific elements of copied cells. De facto, it can do many more useful things. Among others, it can quickly remove data validation rules in a worksheet. Here's how:
- Select an empty cell without data validation, and press Ctrl + C to copy it.
- Select the cells(s) from which you want to remove data validation.
- Press Ctrl + Alt + V, then N, which is the shortcut for Paste Special > Data Validation.
- Press Enter. Done!
Excel data validation tips
Now that you know the basics of data validation in Excel, let me share a few tips that can make your rules a whole lot more effective.
Excel data validation based on another cell
Instead of typing values directly in the criteria boxes, you can enter them in some cells, and then refer to those cells. If you decide to change the validation conditions later, you will simply type new numbers on the sheet, without having to edit the rule.
To enter a cell reference, either type it in the box preceded by an equal sign, or click the arrow next to the box, and then select the cell using the mouse. You can also click anywhere within the box, and then select the cell on the sheet.
For example, to allow any whole number other than the number in A1, pick the not equal to criteria in the Data box and type =$A$1 in the Value box:
To take a step further, you can enter a formula in the referenced cell, and have Excel validate the input based on that formula.
For example, to restrict users to entering dates after today's date, enter the =TODAY() formula in some cell, say B1, and then set up a Date validation rule based on that cell:
Or, you can enter the =TODAY() formula directly in the Start date box, which will have the same effect.
Formula-based validation rules
In situations when it's not possible to define a desired validation criteria based on a value or cell reference, you can express it using a formula.
For example, to limit the entry to the minimum and maximum values in the existing list of numbers, say A1:A10, use the following formulas:
=MIN($A$1:$A$10)
=MAX($A$1:$A$10)
Please pay attention that we lock the range by using the $ sign (absolute cell references) so that our Excel validation rule works correctly for all selected cells.
How to find invalid data on the sheet
Although Microsoft Excel allows applying data validation to cells that already have data in them, it won't notify you if some of the existing values do not meet the validation criteria.
To find invalid data that had made its way into your worksheets before you added data validation, go to the Data tab, and click Data Validation > Circle Invalid Data.
This will highlight all cells that don't meet the validation criteria:
As soon as you correct an invalid entry, the circle will be gone automatically. To remove all circles, go to the Data tab, and click Data Validation > Clear Validation Circles.
How to protect a worksheet with data validation
In case you'd like to protect worksheet or workbook with password, configure the desired data validation settings first, and then protect the sheet. It is important that you unlock validated cells prior to protecting the worksheet, otherwise your users won't be able to enter any data in those cells. For the detailed guidelines, please see How to unlock certain cells on a protected sheet.
How to share a workbook with data validation
To allow multiple users to collaborate on the workbook, be sure to share the workbook after you have done data validation. After sharing the workbook your data validation rules will keep working, but you won't be able to change them, nor to add new rules.
Excel Data Validation not working
If data validation isn't working properly in your worksheets, it's most likely because of one of the following reasons.
Data validation does not work for copied data
Data validation in Excel is designed to prohibit typing invalid data directly in a cell, but it cannot stop users from copying invalid data. Though there is no way to disable copy/paste shortcuts (other than by using VBA), you can at least prevent copying data by dragging and dropping cells. To do this, go to File > Options > Advanced > Editing options, and clear the Enable fill handle and cell drag-and-drop check box.
Excel data validation is unavailable when in cell edit mode
The Data Validation command is unavailable (greyed out) if you are entering or changing data in a cell. After you've finished editing the cell, press Enter or Esc to quit the edit mode, and then do data validation.
Data validation cannot be applied to a protected or shared workbook
Although the existing validation rules keep working in protected and shared workbooks, it's not possible to change data validation settings or set up new rules. To do this, unshare and/or unprotect your workbook first.
Incorrect data validation formulas
When doing formula-based data validation in Excel, there are three important things to check:
- A validation formula does not return errors.
- A formula does not reference empty cells.
- Appropriate cell references are used.
For more information, please see Custom data validation rule not working.
Manual recalculation is turned on
If the Manual Calculation mode is turned on in your Excel, uncalculated formulas can prevent data from being validated correctly. To change the Excel calculation option back to automatic, go to the Formulas tab > Calculation group, click the Calculation Options button, and then click Automatic.
For more information, please see Automatic calculation vs. Manual calculation.
That's how you add and use data validation in Excel. Another way to correctly enter data in a worksheet is to use Excel Autocorrect. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
- Make a dynamic dependent dropdown in Excel 365 an easy way
- Create dependent dropdown for multiple rows
- Make dropdown list with images in Excel 365
- How to change, copy and remove drop down list in Excel
- How to make and use Excel data entry form
FAQs
Data validation in Excel: how to add, use and remove? ›
Click the control whose data validation you want to remove. On the Format menu, click Data Validation. In the Data Validation dialog box, click the condition that you want to remove, and then click Remove.
How do I add and remove Data Validation in Excel? ›Remove data validation - Select the cell or cells that contain the validation you want to delete, then go to Data > Data Validation and in the data validation dialog press the Clear All button, then click OK .
How do I add or remove items from a drop-down list in Excel? ›- Select the cell(s) containing the drop-down list.
- On the Data tab, in the Data Tools group, click Data Validation. This will open the Data Validation dialog box.
- In the Source box, change, add or remove comma-separated items.
- Click OK to save the changes.
Data validation in Excel is a feature that allows you to control the type of data entered into your worksheet. For example, Excel data validation allows you to limit data entries to a selection from a dropdown list and to restrict certain data entries, such as dates or numbers outside of a predetermined range.
How do I edit existing data validation in Excel? ›Go to Data > Data Validation. On the Settings tab, click in the Source box, and then change your list items as needed.
How do I add multiple items to a drop down list in Excel? ›Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.
How do I edit a drop down list in a spreadsheet? ›- In Google Sheets, open a spreadsheet.
- Select the cell or cells you want to change, then select an option: Click Data. ...
- Edit the dropdown list: To change the options listed, edit the items under "Criteria." ...
- Click Done.
Open the Data Validation dialog box (Data tab > Data Validation). On the Settings tab, select List in the Allow box. In the Source box, type the items of your Excel validation list, separated by commas. For example, to limit the user input to three choices, type Yes, No, N/A.
What are some examples of data validation rules? ›- Data Type. This rule ensures the data being entered has the correct data type as required by the field, for example, text. ...
- Code Check. ...
- Range. ...
- Consistent Expressions. ...
- Format. ...
- Uniqueness. ...
- No Null Values. ...
- Standards for Formatting.
- Stay in the Settings tab;
- Select List in the Allow drop-down list;
- Enter the following formula in the Source box; =IF($E$2=$B$2,$B$3:$B$6,$C$3:$C$6) Copy.
- Click the OK button. See screenshot:
How do I add a formula to a drop-down list? ›
In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria. As soon as you select List, the source field appears. In the source field, enter the following formula: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”)) Make sure that the In-cell drop down option is checked.
Why can't i edit Data Validation in Excel? ›The worksheet might be protected or shared - You cannot change data validation settings if your workbook is shared or protected. You'll need to unshare or unprotect your workbook first.
Can I have multiple Data Validation in Excel? ›You can use data validation in Excel to restrict data entry based on another column. You can create two lots of data validation in one column based on another column by using a custom formula.
How do I remove cells from between data? ›Delete cells after the last cell with data
This will select a range of cells between your data and the last used cell on the sheet. Click the heading of the first blank row below your data and press Ctrl + Shift + End. Click Clear > Clear All on the Home tab or right-click the selection and choose Delete… >
- Click the control whose data validation you want to remove.
- On the Format menu, click Data Validation.
- In the Data Validation dialog box, click the condition that you want to remove, and then click Remove.
More often than not, you'll want to use your dropdown list for an entire row or column of cells. To do this, you can either drag the bottom-right corner of the cell with your dropdown menu to the cells you want to populate with the same list, or copy and paste the contents of the cell into other cells.
Can you have 2 drop-down lists in Excel? ›Excel Drop Down Lists are intuitive to use and extremely useful in when you are creating an Excel Dashboard or a data entry form. You can create multiple drop-down lists in Excel using the same source data.
How many options can you have in a drop down list in Excel? ›There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.
What is the shortcut to edit a drop-down list in Excel? ›Select the cell where you want a drop-down list. Click Data Validation, select List, click in Source, press F3, select the name, click OK, and click OK again.
How do I edit an existing drop-down list in Word? ›Value is saved in the document for programmatic retrieval. If you need to make any changes… simply click on the Display Name and select Modify, likewise if you need to delete the choice, you can select remove, or move the choices up and down on the list.
How do I create a dynamic searchable drop-down list in Excel? ›
- Type the list items in a table or convert an existing range to a table using the Ctrl + T shortcut.
- Select the cell(s) where you wish to insert a dropdown.
- Open the Data Validation dialog window.
- Select List from the Allow drop-down box.
- Detail Plan. It is the most critical step, to create the proper roadmap for it. ...
- Validate the Database. This is responsible for ensuring that all the applicable data is present from source to sink. ...
- Validate Data Formatting. ...
- Sampling.
The following are the common Data Validation Types:
Range Check. Format Check. Consistency Check. Uniqueness Check.
Data Type Validation: This technique checks if the data entered into the system is of the correct data type, such as a string, integer, or date. Range Validation: This technique checks if the data entered into the system falls within a specific range of values, such as a customer's age between 18 and 65 years old.
What are simple validation rules? ›A simple validation rule is based on a PredefinedGreexRule, which is used in conjunction with the value of a required attribute. The value is entered as DataCapture information in Sterling Business Center.
Which is the best approach to validate data? ›The best way to ensure the high data quality of your datasets is to perform up-front data validation. Check the accuracy and completeness of collected data before you add it to your data warehouse. This will increase the time you need to integrate new data sources into your data warehouse.
What is a dynamic drop-down list? ›It's a list that we can select from in order to filter attendance data by department. The source of this dynamic data validation list has been created using the UNIQUE function. UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range. In other words, it removes duplicates from a data set.
How do you use indirect validation in Excel for Data Validation? ›- Click in cell B15.
- Click the Data tab.
- From the Data Tools group, click Data Validation.
- Select List in the Allow drop-down.
- In the Source, type =INDIRECT($B$14)
The following code snippet shows a sample example, how we deselect one of the values from the dropdown by specifying its value: Select select = new Select(driver. findElement(By.id("oldSelectMenu"))); //Deselect option with value "6" select. deselectByValue("6");
How do you make a drop-down list change depending on selection? ›In the same or in another spreadsheet, select a cell or several cells in which you want your primary drop-down list to appear. Go to the Data tab, click Data Validation and set up a drop-down list based on a named range in the usual way by selecting List under Allow and entering the range name in the Source box.
How do I select a specific value from a dropdown? ›
- selectByVisibleText(String args) ...
- selectByIndex(String args)
- This method takes the index of the option to select in the dropdown. ...
- Syntax − Select s = new Select(driver.findElement(By.id("<< id exp>>"))); s.selectByIndex(1);
- selectByValue(String args)
- AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)
- OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)
- NOT – =IF(NOT(Something is True), Value if True, Value if False)
AND() = Returns a TRUE response if all values are true; returns a FALSE response if one or more values are false. Use this function as an alternative to the operator && (AND). OR() = Determines if expressions are true or false. Returns TRUE if any expression is true.
How do I remove Data Validation from sheets? ›- Select the cell or cell range that contains the data validation.
- Click Data on the menu bar.
- Select Data Validation.
- Edit or remove the existing validation.
To remove the circle from a single cell, enter valid data in the cell. On the Data tab, in the Data Tools group, click the arrow next to Data Validation, and then click Clear Validation Circles.
How do I edit a Data Validation list in sheets? ›- In Google Sheets, open a spreadsheet.
- Select the cell or cells you want to change, then select an option: Click Data. ...
- Edit the dropdown list: To change the options listed, edit the items under "Criteria." ...
- Click Done.
- Select the cell that contains a data validation list.
- Choose Data|Validation.
- On the Settings tab, remove the check mark from the Ignore blank box.
- Click OK.
In the Excel DataSheet go to the column that will contain the enum values and select all of the cells in the column. On the Excel Data tab, click Data Validation and select Data Validation from the drop-down. In the Data Validation dialog, select List from the Allow drop-down.
What are the 3 types of Data Validation in Excel? ›Time - The user should enter a time. Text Length - It validates input based on the length of the data. Custom - It validates the user input using a custom formula.
What does clear validation circles do? ›Excel will highlight the cells that don't meet your specified criteria. Click Clear Validation Circles to remove them.