In Excel, you use data validation to control the type of data that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. You can change existing data validation settings in the Data Validation dialog box.
In this article
Find cells that have data validation
If you don't remember exactly which cells have data validation, you can easily find them.
- On the Home tab, in the Editing group, click Find & Select, and then click Data Validaton.
Any cells that have data validation are highlighted on the worksheet.
Top of Page
Change the data validation settings
- Click a cell that has data validation settings that you want to change.
- On the Data tab, in the Data Tools group, click Data Validation.
Issue: The Data Validation command is unavailable.
An Excel table may be linked to a SharePoint site You cannot change data validation to an Excel table that is linked to a SharePoint site. To change data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
- On each tab in the Data Validation dialog box, select and change the options that you want.
If you want the same changes for other cells on the worksheet, select the Apply these changes to all other cells with the same settings check box on the Settings tab.
Top of Page