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

Change the data validation settings

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

  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

    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.

  3. 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

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language