How to Find Restricted Values in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on March 14, 2022

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

AI Formula Generator

Try for Free

See all How-To Articles