Do you know how to find and select the cells in an excel worksheet that contain data validation? In this blog we would learn how to find and select all the cells that contain some data validation rule in it.
With this technique, you can select all the cells containing data validation in it at one go and can format all those cells (like giving a distinct border or distinct color to those cells so as to distinguish it from other cells).
Let us begin with this technique.
Find Cells Containing Data Validation
Follow the below steps to achieve the same :
Step 1 : Go to the “Home” tab and click on the “Find and Select” button. You can find this option under the ‘Editing’ group.
In the drop-down menu, select the “Go To Special” option.
The “Go to Special” dialog box would open as shown in the screenshot below.
You can also use a keyboard shortcut to open the “Go To Special” Dialog box. Press “F5” key on your keyboard. In the “Go To” dialog box, click on the button – “Special”. As a result, the “Go To Special” dialog box would pop-out.
Step 2 : In the “Go to Special” dialog box, select the radio button “Data Validation”.
Below this radio button, there are two more radio buttons. The first one is “All”. If you select this option, then Excel would search for all the data validation rules in the excel worksheet and select all of them.
Another radio button is “Same”. This option will select only those cells that contain the data validation which is the same as that of the data validation in the active cell.
As we want to find cells containing validation, therefore, we have selected radio button “All” as highlighted in the screenshot below :
Step 3 : Click on the “OK” button.
As a result, you would notice that all the cells containing data validation rule gets selected.
Refer to the screenshot below.
You can now change the format of the selected cells at one go like changing the cell color or font, making borders of the cell, etc.
This brings us to the end of this blog.