IDs, invoice numbers, and SKUs are supposed to be unique. A COUNTIF over the key column flags any value that appears more than once — duplicates that would break lookups and double-count totals.
The example
Invoice INV-100 appears twice.
| A | B | |
|---|---|---|
| 1 | Key | Check |
| 2 | INV-100 | Duplicate |
| 3 | INV-101 | Unique |
The formula
The formula:
How it works
How it works:
COUNTIF(key_column, A2)counts every occurrence of the key in the whole column.- Greater than 1 means the key is duplicated — both (or all) copies get flagged.
- Duplicate keys silently break VLOOKUP/XLOOKUP (only the first match returns) and double-count sums.
- Count the total duplicate keys with a SUMPRODUCT over the flags.
Duplicate keys are silent lookup poison. VLOOKUP returns only the first match for a duplicated key, so a second invoice with the same number quietly returns the wrong amount — no error, just bad data. Auditing keys for uniqueness before relying on them in lookups or joins prevents the most insidious class of spreadsheet bug.
Try it: interactive demo
Keys, one per line.
Variations
Count duplicate keys
How many repeat:
First vs later
Keep first:
Duplicate within group
Key + category:
Pitfalls & errors
Breaks lookups. VLOOKUP returns only the first match for a duplicated key.
Whole column. Use a fixed range so every copy is counted, including the first.
Normalize keys. “INV-100” and “inv-100 ” may be the same — clean before counting.
Practice workbook
Frequently asked questions
How do I audit duplicate keys in Excel?
Why are duplicate keys dangerous?
How do I keep only the first occurrence?
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