How to find duplicates in Excel
COUNTIF counts how many times a value appears in a list. More than once means a duplicate.
Flagging duplicates with a formula lets you check them first. When you’re sure, Data > Remove Duplicates deletes the extra rows.
The formula
| A | |
|---|---|
| 1 | Invoice |
| 2 | INV-101 |
| 3 | INV-102 |
| 4 | INV-101 |
| 5 | INV-103 |
=IF(COUNTIF($A$2:$A$5,A2)>1,"Duplicate","OK")Result: Duplicate
INV-101 appears twice, so its rows are flagged.
Step by step
- Next to the first row, type the formula, locking the whole list with $.
- Press Enter and fill down.
- Filter the column to see only the duplicates.
Other ways to write it
=IF(COUNTIF(A$2:A$5,A2)>1,"Duplicate","OK")Locking just the rows works too, since the column doesn’t change.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- An unlocked list
- Without $, the list shrinks as you fill down and misses duplicates higher up.
- Hidden spaces
- "INV-101 " with a trailing space isn’t a match for "INV-101". Clean the column with TRIM first.
Reading isn’t the same as doing
Finding duplicates gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.