Formula example
Find Duplicates in Excel
You want to flag duplicate SKUs in column A before importing or looking up product data.
Copyable formula
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Useful variations
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))This version leaves the first occurrence blank and flags repeat rows only.
Sample data
| SKU | Item | Category | Price |
|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 |
| A-101 | Mouse | Hardware | 25 |
| B-200 | Desk Mat | Office | 18 |
| B-201 | Notebook | Office | 7 |
Formula explanation
- COUNTIF counts how many times the current value appears in the full SKU range.
- Values with a count greater than 1 are flagged as Duplicate.
- The outer IF keeps blank rows from being marked as duplicates.
- The absolute range keeps the check stable when filling the formula down.
Common errors
- Forgetting absolute references can shift the count range while filling down.
- Leading or trailing spaces can hide duplicates.
- Use the second formula if you only want to flag duplicates after the first occurrence.
Build your own version
Use the deterministic builder for this pattern: Duplicate Checker Formula Builder.
Related formulas
FAQ
Can I flag only the second duplicate?
Yes. Use COUNTIF($A$2:A2,A2)>1 to flag repeats after the first occurrence.
Does this work in Google Sheets?
Yes. COUNTIF and IF use the same basic syntax here.