Excel

Excel conditional formatting rules duplicated? Here's the fix

3 minute read

Open Home → Conditional Formatting → Manage Rules on a worksheet that's been edited for a while, and it's common to find the same rule listed two or three times over — same range, same condition, same formatting — doing nothing except making the sheet slower to recalculate and harder to audit.

How duplicates creep in

A few habits cause this more than anything else:

• Copy-pasting cells that already carry a conditional formatting rule into a range that has an overlapping rule of its own — Excel doesn't merge them, it keeps both.
• Re-applying a rule "just to be safe" after it seemed not to work, when the actual issue was something else.
• Extending a range by selecting cells and reapplying the same rule instead of editing the existing rule's range.

None of these are wrong exactly — they're just how formatting naturally accumulates on a sheet that gets reused and extended over time, and Excel has no automatic cleanup for it.

Why it's worth clearing up

Every conditional formatting rule on a sheet gets evaluated for every relevant cell, every time the sheet recalculates. A handful of duplicate rules on a small sheet is invisible; the same pattern on a sheet with tens of thousands of formatted cells can noticeably slow down scrolling and editing. It also makes the rules genuinely harder to reason about — if you go to edit "the" rule for a range and there turn out to be three, it's easy to edit one and leave the others silently still active.

Clearing duplicates manually

1. Go to Home → Conditional Formatting → Manage Rules.
2. Set the Show formatting rules for dropdown to This Worksheet so you see everything, not just the current selection.
3. Compare each rule's range and condition — Excel doesn't sort or group duplicates for you, so this means reading through the list carefully.
4. Select and delete the redundant ones, leaving one copy per genuinely distinct rule.

On a sheet with more than a handful of rules, this becomes slow and error-prone fast — it's easy to delete the wrong one or miss a duplicate that has a slightly different priority number but is otherwise identical.

The automatic way

Because the underlying rule definitions are just structured data in the file, exact duplicates — rules with identical conditions and formatting on the same range — can be found and removed precisely, including cases where the range is defined as multiple separate but redundant blocks rather than one obviously duplicated rule.