Change data validation settings

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 Top of Page

Change the data validation settings

  1. Click a cell that has data validation settings that you want to change.
  2. On the Data tab, in the Data Tools group, click Data Validation.

Excel Ribbon Image

ShowIssue: 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.

  1. 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 Top of Page

 
 
Applies to:
Excel 2007