Remove data validation

You can remove validation on a cell in Excel 2007 so that users are no longer required to enter information or information formatted in specific way.

  1. Select the cells where you no longer want to validate data.

To remove data validation from all similar cells or from all cells that have validation on a worksheet, find the cells.

ShowHow to find cells with data validation

Do one of the following:

Find all cells with data validation    

  1. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

Excel Ribbon Image

  1. Click Data Validation, and then click All.

Find cells that match certain data validation settings    

  1. Click a cell that has the data validation settings for which you want to find matches.
  2. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

Excel Ribbon Image

  1. Click Data Validation, and then click Same.
  1. On the Data tab, in the Data Tools group, click Data Validation.

Excel Ribbon Image

ShowIssue: The Validation command is unavailable.

An Excel table may be linked to a SharePoint site     You cannot remove data validation from an Excel table that is linked to a SharePoint site. To remove 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. For more information about how to stop sharing, or to remove protection from a workbook, see Use a shared workbook to collaborate and Change or remove protection of worksheet or workbook elements.

  1. Do one of the following:
  • If you are prompted to erase the current settings and continue, click OK, and then click Cancel.
  • If the Data Validation dialog box appears, click the Settings tab, and then click Clear All.
 
 
Applies to:
Excel 2010, Excel 2007