To dedupe with a formula, mark each row as the first occurrence or a repeat. A running COUNTIF labels the first as unique and the rest as duplicates — then filter to keep only the firsts.
The example
First sees "First"; repeats see "Duplicate".
| A | B | |
|---|---|---|
| 1 | Value | Flag |
| 2 | apple | First |
| 3 | apple | Duplicate |
The formula
The formula:
How it works
How it works:
COUNTIF($A$2:A2, A2)counts how many times the value has appeared up to this row — the absolute start with a relative end expands as you fill down.- It equals 1 on the first occurrence, and more on repeats.
- Flag the firsts, then filter to "First" (or delete "Duplicate" rows) to dedupe.
- Combine columns for a multi-field duplicate check.
Lock the start, not the end. The magic is $A$2:A2 — an absolute top and a relative bottom. Filled down, the range grows row by row, so the count is “how many so far,” making the first hit unique. Use the whole-column COUNTIF($A:$A, A2) > 1 instead to flag every member of a duplicate set, firsts included.
Try it: interactive demo
Values, one per line.
Variations
Any duplicate (all)
Mark every repeat:
Multi-column key
Two fields:
Count occurrences
How many total:
Pitfalls & errors
$A$2:A2 is the trick. Absolute start, relative end — the count expands as you fill down.
First vs all. Use a whole-column COUNTIF to flag every duplicate, not just repeats.
Case-insensitive. COUNTIF ignores case; build a key first if case matters.
Practice workbook
Frequently asked questions
How do I flag the first occurrence of a duplicate in Excel?
How do I mark every row in a duplicate set?
How do I check duplicates across two columns?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class