Excel Conditional Formatting for Duplicates: How-To

How-to basics · Updated November 22, 2026

To highlight duplicate values in Excel, select your data range, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and click OK. This built-in rule instantly shades repeated entries with the default Light Red Fill with Dark Red Text, and you can change the format to any fill or font color you prefer.

Why Conditional Formatting for Duplicates Matters

Duplicate detection is one of the most common data-cleaning tasks in Excel. Whether you are merging mailing lists, auditing inventory IDs, or checking survey responses, duplicates can skew totals, cause lookup errors, and waste hours of manual review. Conditional formatting offers a visual, non-destructive way to spot repeats—it does not delete or alter your data, just paints a target on it. Unlike formulas or filters, the highlighting updates automatically as you add, remove, or edit values, making it ideal for live worksheets.

How the Built-In Duplicate Rule Works

The Duplicate Values rule is part of Excel’s Highlight Cells Rules group. When you apply it to a selected range, Excel scans every cell and compares it against all others in that range. A cell is flagged as a duplicate if its value appears at least twice. The rule uses the cell’s displayed value, not the underlying formula, so two cells showing 1/1/2025 (one as a date, one as text) may not match if their underlying values differ. For text, matching is case-insensitive by default: Apple and apple are treated as duplicates. The rule also treats blank cells as duplicates—if your range has two or more empty cells, they will all be highlighted, which often surprises users.

How to Apply the Rule Step by Step

  1. Select the range you want to check, for example A2:A100. Include the header row only if you want it checked too.
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. In the dialog, choose Duplicate from the left dropdown (the right dropdown lets you pick Unique instead).
  4. Pick a format from the right dropdown or choose Custom Format to set your own fill, font, or border.
  5. Click OK. The duplicates are highlighted immediately.

On a Mac, the steps are identical: Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In Excel for the web, the same menu exists, though the dialog is simplified.

Using Formulas for More Control

The built-in rule is fast but limited: it highlights all occurrences of a duplicate, including the first one. If you want to keep the first instance unhighlighted, or if you need to compare duplicates across multiple columns, use a formula-based rule. For example, to highlight only the second and later occurrences of a value in A2:A100, select that range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:

=COUNTIF($A$2:$A2,A2)>1

This formula counts how many times the value in A2 appears from the top of the range down to the current row. If that count is greater than 1, the cell is a repeat. The mixed reference $A$2:$A2 locks the start row but lets the end row move with each cell. With this rule, the first occurrence stays clean and only duplicates are shaded.

To highlight duplicates across two columns (e.g., find rows where both column A and column B repeat together), use a formula like =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. This checks for rows with the same pair of values in A and B.

Removing or Editing the Rule

To clear the highlighting, select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To modify the rule later, go to Conditional Formatting > Manage Rules, select the rule, and click Edit Rule. There you can change the range, the format, or the formula. If you have multiple rules on the same range, the order matters—Excel applies rules in the order listed in the Manage Rules dialog, and you can use the arrow buttons to reorder them.

Common Problems and Misconceptions

  • Blanks are highlighted: The built-in rule treats blank cells as duplicates. To exclude blanks, use a formula rule like =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1).
  • First occurrence is highlighted: As mentioned, the built-in rule colors every instance. Use the COUNTIF formula above to leave the first one alone.
  • Case sensitivity: The built-in rule is case-insensitive. If you need case-sensitive duplicates, use a formula with EXACT, e.g., =SUMPRODUCT(--EXACT($A$2:$A$100,A2))>1.
  • Duplicates across different columns: The built-in rule only works within the selected range. To compare two columns, use a formula rule like =COUNTIF($B$2:$B$100,A2)>0 to highlight values in A that appear in B.
  • Rule not updating: Conditional formatting recalculates automatically when you change data, but if you insert or delete rows, the range may not expand. Use an Excel Table (Ctrl+T) so the rule automatically extends to new rows.

Alternatives to Conditional Formatting

If you need to actually remove duplicates, use Data > Remove Duplicates, which permanently deletes repeated rows. For a quick count, use =COUNTIF(range,value) to see how many times a specific value appears. The UNIQUE function (available in Excel 365 and 2021) can extract a list of distinct values: =UNIQUE(A2:A100). For a permanent visual marker that survives sorting, you could add a helper column with a formula like =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate",""), but conditional formatting is usually simpler and does not clutter your data.

Frequently Asked Questions

How do I highlight duplicates in two columns?
Select both columns (e.g., A2:B100), then apply the built-in Duplicate Values rule. It will highlight any cell that repeats anywhere in that two-column range. To highlight entire rows where the pair repeats, use a formula rule with COUNTIFS as shown earlier.

Why is my conditional formatting not catching duplicates?
Check that the range is correct and that the cells contain consistent data types. Numbers stored as text (e.g., with an apostrophe) will not match real numbers. Also, ensure the rule is not being overridden by another rule higher in the Manage Rules list.

Can I highlight duplicates in a single column but ignore blanks?
Yes. Use a formula rule: select the column, go to New Rule > Use a formula, and enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). Adjust the range to match your data.

How do I highlight the first duplicate only?
If you want to highlight only the first occurrence of each duplicate group, use =COUNTIF($A$2:$A$100,A2)=1 combined with a check that the value appears more than once: =AND(COUNTIF($A$2:$A$100,A2)>1,COUNTIF($A$2:$A2,A2)=1). This highlights the first instance but not the repeats.

Does conditional formatting work on merged cells?
Merged cells can cause issues because the rule applies to the top-left cell only. It is best to unmerge before applying duplicate highlighting.

Can I use conditional formatting to find duplicates across multiple sheets?
Conditional formatting works within a single sheet. To compare across sheets, you would need to combine the data into one sheet (e.g., using Power Query) or use a formula that references another sheet, such as =COUNTIF(Sheet2!$A$2:$A$100,A2)>0.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.