There is no doubt in the Excel mines that Conditional Formatting is an unparalleled gem used to distinguish cells meeting defined characteristics. Sometimes, however, you may find your mind boggled on what rules belongs where with so much color fill, borders, and various font colors being a general part of worksheets.
It also doesn’t help if you’re not the creator of the file. It is fortunately very easy to identify Conditional Formatting cells so that you can proceed with your objective such as simply finding the relevant cells or editing or deleting rules applied to them.
Today’s tutorial is about finding cells with Conditional Formatting by cell selection or display. Our 3 methods will select all Conditional Formatting cells, cells of a single rule, and will display one or more rules in the Conditional Formatting Rules Manager.
Switch on the searchlights!
Recommended Reading: What is Conditional Formatting In Excel
Method #1 – Using Find & Select Feature
Purpose: Selects all Conditional Formatting cells.
The Find & Select feature will find all the cells on the active worksheet that contain Conditional Formatting. Find & Select is used for searching and/or replacing text or for selecting cells of a certain kind (such as formula cells, Conditional Formatting cells, etc.) on a sheet. This is the first method due to its quickness; completed in just a couple of clicks. Here is what you need to do:
- In the Home tab, click on the Find & Select button from the Editing, Select the Conditional Formatting option in the menu.
All the cells on the sheet that have a Conditional Formatting rule applied to them will be selected:
Notes:
The Conditional Formatting cells will be selected regardless of the cell selection before using Find & Select.
The Conditional Formatting option in Find & Select does not select cells covered by a single Conditional Formatting rule but cells covered by all Conditional Formatting rules on the worksheet. For selecting cells of a particular rule, you will find the next method helpful.
Method #2 – Using Go To Special Feature
Purpose: Selects all Conditional Formatting cells or all cells of one Conditional Formatting rule.
Go To Special in Excel will also select all Conditional Formatting cells but there’s one thing different with this feature. Like Find & Select, you can select all the cells with Conditional Formatting (Find & Select would actually be the quicker method for that) but in Go To Special, you can select the cells covering only one Conditional Formatting rule, based on the selected cell.
The steps below will guide you on how to select all Conditional Formatting cells in a sheet and also what to do if you want only one rule’s cells selected.
- For selecting the Conditional Formatting of one rule, select one cell that is covered by that rule.
- If you want to select all Conditional Formatting cells, the cell selection does not matter, and you can begin with the next step.
- Press the F5 key (or the Ctrl + G keys) to open the Go To dialog box.
- In the dialog box, click on the Special
- You will be redirected to the Go To Special dialog box.
- In the Go To Special dialog box, select the Conditional Formats radio button.
- By default, the All option will be selected in the new options.
- For selecting all the cells with Conditional Formatting, hit the OK
To select the cells of one rule, click on the Same option instead and then hit OK. This will select the cells of the same rule as the selected cell.
With the All option, the cells containing all the Conditional Formatting rules will be selected:
Notes:
Bear in mind that this way, you can limit the selection to cells of the same Conditional Formatting rule but you cannot select cells of more rules; it is either cells of one rule or all rules. E.g. if the sheet contains 3 rules and you select one cell from each rule, only the cells covering the rule of the last selection will be selected.
To select cells of one rule, you need to know which cell to select so that Go To Special can select the rest for you. In case you aren't sure which rule covers what cells, you can take a sneak peek at the Conditional Formatting Rules Manager. Find out how to do that in the next section.
Method #3 – Using Rules Manager
Purpose: Displays all Conditional Formatting rules of the selected cells, entire sheet, or workbook.
The Rules Manager refers to the Conditional Formatting Rules Manager in Excel and it can be used to find cells with Conditional Formatting. The Rules Manager works as a display, map, creator, editor, and cleaner for Conditional Formatting rules. Our interest here is the map aspect of the manager as it shows the range covered by the rules.
The manager can show the rules of the cell selection, the worksheet, or the whole workbook. Therefore, you can select cells of as many or as few rules and the Rules Manager will display them. Use the Rules Manager to view the ranges covered by Conditional Formatting rules or to figure out which cell to select to apply the Go To Special feature. These steps will take you to the Conditional Formatting Rules Manager:
- To view the range covering a single rule in the manager, select one cell containing the Conditional Formatting
- For more rules, select a cell of each rule.
- For displaying all Conditional Formatting rules in the manager, directly access the manager from the Home tab > Styles group > Conditional Formatting button > Manage Rules
- The Rules Manager shows the rules for the cell selection by default.
- Open the drop menu of Show formatting rules for and select This Worksheet to have the manager display all the rules of the current worksheet.
The Conditional Formatting rules on the worksheet will be displayed in Rules Manager. The Applies to column shows the cells that each rule covers:
That was all you needed to know about finding and locating cells with Conditional Formatting in Excel. While you’re at conditional findings, we’ll track some other findings, digging deeper into the Excel mines and chiseling more Excel skills. Heigh-ho!
About Mehwish Javaid