How to Find Restricted Values in Excel & Google Sheets

This tutorial will demonstrate how to identify cells containing restricted values and clear those restrictions in Excel and Google Sheets.

 

 

Find Restricted Values

In Excel to find cells that have restricted values (also known as Data Validation) follow these steps:

In the Home Ribbon, go to Find & Select > Go To > Special > Data Validation.

 

 

Select the radio button next to Data Validation and then click OK.

 

 

Excel will highlight any cells that have restricted values (in this example, B4:C15).

Clear Restricted Values

Now, to clear this restriction:

 

 

Go to the Data Ribbon Menu and select Data > Data Validation.

 

 

This will bring up the Data Validation Settings Menu. Click Clear All to remove the data restrictions in this range of cells. Then click OK to apply the new settings.

 

 

If you repeat the first steps to find cells with restricted values (data validation rules), you’ll see that no cells are found.

 

 

Modify Restricted Values

To modify restricted values, follow the previous steps, but in the Data Validation Menu select “Allow” to toggle the criteria.

Find Restricted Values in Google Sheets

Unfortunately, Google Sheets does not have such a nice way to find cells that are restricted. However, if you believe that a cell range may be restricted you can verify that by performing the following steps.

Select the range of cells that you suspect are restricted.

 

 

From the Command Ribbon, go to Data > Data Validation.

 

 

This will bring up a new menu for Data Validation.

 

 

A quick review of the criteria displayed indicates that this data range does have a restriction entered already.

To remove that restriction simply click on the Remove validation button in the menu displayed.