Find and Highlight Duplicates in Excel

The quickest way to highlight duplicates in Excel is the built-in Duplicate Values rule under Conditional Formatting: select the cells, pick the rule, click OK. When you need more control, such as flagging only the second copy or finding whole rows that repeat, a COUNTIF formula does the job. This guide shows both, with examples you can type in.

How to highlight duplicates in Excel in four clicks#

The Duplicate Values rule colors every value that appears more than once in the cells you select.

Here's a list of invoice numbers where two were entered twice:

1. Select A2:A9. Leave out the header.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values…
3. Keep Duplicate in the first box and choose a format, such as Light Red Fill with Dark Red Text.
4. Click OK.

A2, A4, A5 and A8 turn red: both copies of INV-2041 and both copies of INV-2043. Switch the first box to Unique to color the values that appear only once instead.

The rule is live. Type a new invoice that matches an existing one and both cells turn red at once. Conditional formatting in general is covered in our guide to highlighting what matters with conditional formatting.

Find duplicates with COUNTIF#

COUNTIF tells you how many times each value appears, which is more useful than a color when you need to filter or count the repeats.

=COUNTIF($A$2:$A$9,A2)

• $A$2:$A$9 is the whole list. The dollar signs lock it so it doesn't move as you fill down.
• A2 is the value to count. It has no dollar signs, so it becomes A3, A4 and so on.

Type it in B2 and fill down to B9. INV-2041 and INV-2043 show 2; the others show 1. To get a clear label instead of a number, wrap it in IF:

=IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate","Unique")

Now turn on filters and show only Duplicate rows to review them. To count how many cells are part of a duplicate, use =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)>1)), which gives 4 here. For more COUNTIF patterns, see how to use COUNTIF with criteria and the COUNTIF function guide.

Highlight only the second and later copies#

The Duplicate Values rule colors every copy, including the first. Often you want to keep the first and flag only the repeats. A growing range does that.

1. Select A2:A9.
2. Go to Home > Conditional Formatting > New Rule…
3. Choose Use a formula to determine which cells to format.
4. Type this formula and pick a fill color:

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

5. Click OK.

$A$2:A2 starts at A2 and ends at the current row. In row 5 it becomes $A$2:A5, which contains INV-2041 twice, so row 5 is colored. In row 2 the range holds only one INV-2041, so the first copy stays plain. Only A5 and A8 light up.

Write the formula for the first selected cell (A2). Excel adjusts it for every other cell in the range.

Highlight duplicate rows across several columns#

Sometimes a value can repeat, but a whole row shouldn't. For example, a name can appear many times in a timesheet, but the same name on the same date is a double entry. Use COUNTIFS with one pair per column.

Select A2:C5, create a formula rule, and use:

=COUNTIFS($A$2:$A$5,$A2,$B$2:$B$5,$B2)>1

The $ before the column letters ($A2, $B2) keeps every cell in the row checking columns A and B, so the whole row is colored. Rows 2 and 5 light up; row 4 doesn't, because Priya's date is different.

Compare two lists for values in both#

A close cousin of duplicates is "which values appear in both lists?" For example, which customers on this month's list also bought last month. COUNTIF answers that too: count each value in the other list.

With this month's customers in A2:A20 and last month's in D2:D30, select A2:A20, create a formula rule and use:

=COUNTIF($D$2:$D$30,A2)>0

Every customer who also appears in column D is colored. Change >0 to =0 to color the new customers instead: the ones that don't appear in last month's list at all. The same formula works in a helper column if you'd rather filter than color.

Why duplicates aren't highlighted#

If two values look the same but aren't flagged, the cells are probably different underneath:

• Extra spaces. "Acme" and "Acme " are different values. Clean them with =TRIM(A2).
• Numbers stored as text. The text "1001" and the number 1001 can be treated differently. Make the column one type.
• The range is wrong. Check Home > Conditional Formatting > Manage Rules to see which cells the rule applies to.

Two other things to know. Both the rule and COUNTIF ignore case, so "ACME" matches "acme". And COUNTIF treats * and ? in a value as wildcards, so values that contain those characters can be counted as matches when they aren't.

Remove duplicates or count unique values#

Highlighting is safe because it changes nothing. When you're sure you want the repeats gone:

• Data > Remove Duplicates deletes repeated rows and keeps the first. Ctrl+Z (⌘Z on a Mac) works straight after, but it's safer to copy the data first.
• UNIQUE (Excel 2021 and Microsoft 365) gives a duplicate-free copy with a formula: =UNIQUE(A2:A9).

Our comparison of UNIQUE vs Remove Duplicates helps you choose. If you need a count per value rather than a cleaned list, a pivot table with the column in Rows and Values shows each value with its count; see the pivot table beginner's guide. Keeping the list in an Excel table means new rows are checked by your rules too.

Practise it#

Use the exercise on this page to label duplicate invoices with IF and COUNTIF. Then practise conditional formatting rules in a live sheet, including the Duplicate Values rule, and practise COUNTIF to get fast at counting with criteria.