Apps Install Account Training Admin
Apply data validation to cells Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel Starter 2010
You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Check out this video for a quick data validation overview.
Discover more Excel training at LinkedIn Learning
Download our examples You can download an example workbook with all of the data validation examples you'll find in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples
Add data validation to a cell or a range Note: The first three steps in this section are for adding any type of data validation. Steps 4-8 are specifically for creating a drop-down List.
1. Select one or more cells to validate. 2. On the Data tab, in the Data Tools group, click Data Validation.
3. On the Settings tab, in the Allow box, select List.
4. In the Source box, type your list values, separated by commas. For example: a. To limit an answer to two choices ("Do you have children?" for example), type Yes,No. b. To limit a vendor's quality reputation to three ratings, type Low,Average,High. Note: These steps are generally only recommended for list items that aren’t likely to ever change. If you have a list that could change, or if you need to add or remove items over time, then you’re better off following the Best Practice step below.
Best Practice: You can also create list entries by referring to a range of cells elsewhere in the workbook. The most efficient way is to create your list, then format it as an Excel Table (from the Home tab select Styles > Format as Table > choose the Table Style that works best for you). Next, select the table’s Data Body Range, which is the portion of the table that has just your list, not the table Header (Department in this case), and give it a meaningful name in the Name Box above column A.
Now, instead of typing your list values in the data validation Source box, you add the name that you just defined, preceded by an Equal (=) sign.
The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically. Note: It’s best to put your lists on a separate worksheet (hidden if necessary) so that no one can edit them.
5. Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the dropdown arrow next to the cell.
6. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. Note: If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.
7. Test the data validation to make sure that it is working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
Notes: After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if the cell is wide enough to show all your entries. If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them. Remove data validation - Select the cell or cells that contain the validation you want to delete, then goto Data > Data Validation and in the data validation dialog press the ClearAll button, then OK.
Adding other types of data validation Examples of formulas in data validation Frequently Asked Questions
Do you have a specific function question? Post a question in the Excel community forum
Help us improve Excel Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.
See also More on data validation Video: Create and manage drop-down lists Add or remove items from a drop-down list Remove a drop-down list Discover more Excel training at LinkedIn Learning
Expand your skills E X P LORE TRA I NI NG
Get new features first J OI N OFFI CE I NS I DE RS
Was this information helpful?