Excel Conditional Formatting Duplicate Rules: How to Fix

Errors & fixes · Updated November 22, 2026

To fix duplicate conditional formatting rules in Excel, select the range, go to Home > Conditional Formatting > Manage Rules, then delete or edit any rule that appears more than once. Duplicate rules often cause confusing or inconsistent highlighting because Excel applies all matching rules in priority order, and the first rule in the list wins when two rules conflict.

What Causes Duplicate Rules in the First Place?

Duplicate rules usually happen when you apply the same formatting to a range more than once, copy cells with formatting into another area, or paste a rule from another sheet. Excel does not automatically merge identical rules — it simply adds a new one. For example, if you highlight duplicates in column A, then later select the same column and apply the same rule again, you get two identical rules. Each rule is evaluated separately, so you might see double highlighting or no visible change if the rules conflict.

How to View and Manage All Rules at Once

The fastest way to see every rule on a sheet is to use the Conditional Formatting Rules Manager. Here’s how:

  1. Select any cell in the range that has the formatting you want to check.
  2. Go to Home > Conditional Formatting > Manage Rules.
  3. In the dialog box, choose This Worksheet from the “Show formatting rules for” dropdown to see all rules on the sheet.
  4. Look for rules that have the same range, rule type, and formatting. Select a duplicate and click Delete Rule.
  5. Click OK to apply changes.

On a Mac, the path is the same: Home tab > Conditional Formatting > Manage Rules. In Excel for the web, the option is under Home > Conditional Formatting > Manage Rules as well, but the dialog is slightly simpler.

How to Choose Which Rule to Keep

When you have two rules that look identical, keep the one that appears first in the list — that rule takes priority. If the rules are not identical (e.g., one uses “duplicate values” and another uses a formula), decide which one you actually want. To change the order, select a rule and use the Up and Down arrows in the Rules Manager. The rule at the top is applied first, and if it returns TRUE, the formatting is applied and lower rules are ignored for that cell.

Common Problems Caused by Duplicate Rules

Duplicate rules can cause several visible issues:

  • Inconsistent highlighting — some cells appear formatted, others don’t, even though they meet the same condition.
  • Performance slowdowns — every rule is recalculated when the sheet changes, so extra rules can make large sheets feel sluggish.
  • Confusing priority conflicts — if one rule formats duplicates red and another formats them yellow, the top rule wins, so you might see red where you expected yellow.
  • Hidden duplicates — if you copy a range with formatting, the new range gets its own copy of the rule, which may not update when you edit the original rule.

How to Prevent Duplicate Rules in the Future

To avoid creating duplicates, follow these habits:

  • Always check the Applies to box in the Rules Manager before adding a new rule — if the range already has a rule, edit it instead of adding another.
  • When copying cells, use Paste Special > Values if you don’t need the formatting, or use Format Painter carefully — it copies all rules from the source range.
  • Use a single formula-based rule instead of multiple simple rules when you need complex conditions. For example, instead of two rules for “duplicate” and “unique”, use one formula like =COUNTIF($A$2:$A$20,A2)>1 for duplicates.
  • Periodically run the Rules Manager and delete any rule that is no longer needed.

Comparing Duplicate Rules with Formula-Based Rules

Excel’s built-in “Duplicate Values” rule (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) is the easiest way to highlight duplicates, but it has limitations: it only checks for exact matches, and it applies to the entire selected range. A formula-based rule gives you more control. For example, to highlight duplicates only in column A, ignoring blanks, use:

Assuming data is in A2:A20, select A2:A20, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:

=AND(A2<>

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.