Conditional formatting is a great way to get Excel to auto format cells depending on some criteria you set up (e.g. turn red if the number is less than 10). However, as conditional formatting is on a cell and not in a cell, it is very difficult to see which cells in Excel have conditional formatting on them unless they are activated. Below some ways of how to find cells with conditional formatting.
Find Conditional Formatted Cells with GOTO SPECIAL
There is an underutilised tool that has always existed in Excel called GOTO SPECIAL.
It allows you to find all cells that meet certain criteria e.g. there are comments in them, they are formula’s etc. One of the options is Conditional Formatting. You find the tool under the HOME tab as shown below.
When you click on this, the GOTO Special box appears. As below we have clicked on Conditional Format, and note that we have left the second option on ALL. This means that we want Excel to identify ALL the cells within this sheet that have conditional formatting of any kind.
When we click OK, you will see that all cells that have conditional formatting will be highlighted.
Just some important notes:
- If you want Excel to look at the whole sheet, make sure you are only clicked in one cell.
- If you want Excel to only look in a certain area, then highlight the applicable cells.
- If you want to find cells with a particular type of conditional formatting, click on one of the cells with that type of formatting and then choose SAME instead of ALL in the GOTO Special dialogue box.
Find Conditional Formatted cells with the Manage Rules
Another option is to use the MANAGE RULES button in Conditional Formatting. So on the sheet with the cells you are looking for, click on the HOME tab, then CONDITIONAL FORMATTING and MANAGE RULES as shown below.
You will see the following
Firstly you must decide where you want to look. We have chosen ‘This Worksheet’ but there are many other options.
Then in the section below you will see all the rules, but more importantly you will see where each rule is applied to. You can also change these here if you want to!