How to Audit Conditional Formatting Rules Across Your Workbook

Finding Rules on a Single Sheet

Home → Conditional Formatting → Manage Rules

The dropdown at the top says “Show formatting rules for: Current Selection” — change this to “This Worksheet” to see all rules on the current sheet.

You can see each rule's condition, format, and applies-to range. But this only works for one sheet at a time.

The Multi-Sheet Problem

To audit the entire workbook, you must:

• Go to each sheet
• Open Manage Rules
• Switch the dropdown to “This Worksheet”
• Review the rules
• Repeat 40+ times

There's no option for “This Workbook” in the dropdown.

Common Conditional Formatting Problems

Rule conflicts: Multiple rules apply to the same cells. The first rule in the list wins (unless “Stop If True” is set). Conflicting rules cause unpredictable highlighting.

Performance drag: Each rule recalculates when the sheet changes. Workbooks with hundreds of rules become slow.

Broken references: Rules referencing deleted columns or moved data. They don't error — they just silently stop working.

Overlapping ranges: Rules that apply to $A:$A (entire column) when they should apply to $A$1:$A$100. This wastes processing on empty cells.

Cleaning Up Rules

Step 1: In Manage Rules, look for rules with tiny “Applies to” ranges that might be leftover from deleted data.

Step 2: Check for duplicate rules (same condition, same format, overlapping ranges).

Step 3: Consolidate rules where possible — one rule for $A:$Z is better than 26 rules for each column.

Official Resources

• Conditional formatting overview — Microsoft's formatting guide
• Rule precedence — how rules interact