Formula example
Find Duplicates in Excel Formula Example
Flag duplicate SKUs in column A before importing or looking up product data. With the sample data, both A-100 rows are marked Duplicate.
Copyable formula
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Rows with SKU A-100 return Duplicate because that SKU appears more than once.
Useful variations
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Marks every row whose value appears more than once, including the first occurrence.
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))This version leaves the first occurrence blank and flags repeat rows only.
=IF(A2="", "", COUNTIF($A$2:$A$100, A2))Use this when you want to see how many times each value appears.
Sample data
| SKU | Item | Duplicate flag |
|---|---|---|
| A-100 | Keyboard | Duplicate |
| A-101 | Mouse | |
| A-100 | Keyboard | Duplicate |
| B-200 | Desk Mat |
When to use this formula
- You need to check SKU, invoice, email, customer, or product IDs before a lookup or import.
- Blank rows should stay blank instead of being flagged.
- You want a formula result that can be filtered, copied, or audited row by row.
Flag every duplicate row
Use COUNTIF($A$2:$A$100,A2)>1 when the first copy and later copies should all be marked. This is useful before deleting or reviewing every duplicate value.
Flag second and later duplicates only
Use COUNTIF($A$2:A2,A2)>1 when the first occurrence should stay blank and only later repeats should be marked. The growing range starts at row 2 and ends at the current row.
Clean spaces before checking
Leading and trailing spaces can make duplicate-looking values count as different values. Use TRIM in a helper column or clean the source range before relying on the duplicate flag.
Returned flags from the sample data
| SKU | Full-range duplicate formula | Second-and-later formula |
|---|---|---|
| A-100 in row 2 | Duplicate | |
| A-101 in row 3 | ||
| A-100 in row 4 | Duplicate | Duplicate |
| B-200 in row 5 |
Duplicate formula choices
| Need | Formula pattern | Result |
|---|---|---|
| Mark every duplicated value | COUNTIF(full_range,current_cell)>1 | First and later copies are flagged. |
| Mark later repeats only | COUNTIF(start_to_current,current_cell)>1 | Only rows after the first copy are flagged. |
| Show frequency | COUNTIF(full_range,current_cell) | Each row shows its occurrence count. |
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.
- Check whether case sensitivity matters before treating ABC and abc as the same ID.
Build your own version
Use the formula 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.
Why are obvious duplicates not flagged?
Check for leading spaces, trailing spaces, hidden characters, or mixed number and text values.
Can I count duplicates instead of flagging them?
Yes. Use COUNTIF($A$2:$A$100,A2) to return the occurrence count for each row.