Applies to MS Excel 2007+

Find Cells that Contain Conditional Formatting

If you have applied Conditional Formatting in your workbooks, you may later want to know how to find cells that contain Conditional Formatting as it isn’t always obvious how the feature has been used or what the formatting represents. This is especially true if someone else created the file and now you need to work on it.

Fortunately, it is relatively easy to find which cells have conditional formatting applied to them using the Go To feature of Excel.

Steps to find all conditional formatting:

1. Press F5 function key. Excel displays the Go To dialog box.

2. Click Special… button to display the Go To Special dialog box.

3. Click on Conditional Formats 

4. Click OK

Excel now reveals all the cells in your worksheet that contain conditional formatting.

If that’s all you needed, you are free to go to lunch! If you want to see, edit or delete those formats, then read on…

How to See Existing Conditional Formatting Rules

Now that you know which cells contain conditional formatting, you might want to look at the definition of the different rules applied.

On the Home tab, Styles group, Conditional Formatting, Manage Rules:

Select This Worksheet from drop down list to display all Conditional Formatting Rules for the worksheet:

Click on Edit Rule… button to see details and what rule was applied or Delete Rule to remove it.

The Fast Way to Delete Existing Conditional Formatting Rules

You don’t have to be in the Rules Manager to delete any or all the conditional formatting applied, for instance…

If you want specific cells/ ranges cleared, select them first, then:

On the Home tab, Styles group, click Conditional Formatting, Clear
Rules > Clear Rules from Selected Cells

To delete all the conditional formatting on the worksheet, you don’t need to have any particular cell selected, just choose > Clear Rules from Entire Sheet

Thanks for reading! Hope this has been valuable to you. For more helpful tips on Conditional Formatting and other neat Excel features, give a click and go to this blog: https://gaylelarson.com/conditional-formatting-clarity-visual-impact-excel/ 

Conditional formatting has six rules that can be applied to your data. For more information, visit this Microsoft site for additional options with this powerful tool:

Use Conditional Formatting to Highlight Text

Please leave a comment below and let me know how Conditional Formatting works for you in Excel…Happy Computing!