How to find cells with conditional formatting

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.

How to find cells with conditional formatting

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.

How to find cells with conditional formatting

When we click OK, you will see that all cells that have conditional formatting will be highlighted.

How to find cells with conditional formatting

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.

How to find cells with conditional formatting

You will see the following

How to find cells with conditional formatting

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!