See all How-To Articles

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.

 

restricted values excel

 

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.

 

restricted values go to special

 

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

 

restricted values data validation

 

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

Clear Restricted Values

Now, to clear this restriction:

 

restricted values clear data restriction

 

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

 

restricted values clear restriction

 

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.

 

restricted values clear all

 

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

 

restricted values clear all confirmed

 

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.

 

google sheets main table

 

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

 

google sheets data validation

 

This will bring up a new menu for Data Validation.

 

google sheets data validation criteria

 

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.

 

google sheets data validation remove