Excel formula builder
Duplicate Checker Formula Builder
Create a COUNTIF formula that flags duplicate IDs, SKUs, emails, or names while ignoring blank rows. Choose whether every duplicate value should be marked or only the second and later repeats.
Builder inputs
Use the full range to check and the current row cell where the formula starts.
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))COUNTIF checks the current value against the chosen range. The blank guard prevents empty rows from being marked as duplicates.How this formula works
- COUNTIF returns how many times the current value appears in the chosen range.
- The default formula ignores blanks before running the duplicate check.
- Use the after-first mode when you want to keep the first occurrence unmarked.
Best fit
Best for
- Flagging duplicate SKUs, invoice numbers, emails, names, or imported IDs.
- Adding a row-by-row review column before a lookup, import, or cleanup step.
- Choosing between marking every duplicate row or only later repeats.
Not for
- Finding duplicate combinations across two or more columns; use a COUNTIFS pattern for that.
- Case-sensitive duplicate checks; COUNTIF treats uppercase and lowercase text as the same.
- Cleaning hidden spaces automatically; clean or trim the source values first.
Useful formula variations
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Marks the first and later copies when a value appears more than once.
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))Leaves the first occurrence blank and marks later repeats.
=IF(A2="", "", COUNTIF($A$2:$A$100, A2))Returns how many times the current value appears in the full range.
Sample data
| SKU | Item | Duplicate flag |
|---|---|---|
| A-100 | Keyboard | Duplicate |
| A-101 | Mouse | |
| A-100 | Keyboard | Duplicate |
| B-200 | Desk Mat |
Choose the right flagging mode
Flag all duplicate values when every repeated row should be reviewed. Use after-first mode when the first copy should stay clean and only later repeats need attention.
Keep blanks out of the result
The blank-row guard returns an empty string when the current cell is blank. That keeps unused rows from being marked as duplicates.
Clean the source range first
COUNTIF sees values with hidden spaces as different text. Use TRIM in a helper column or clean the source column before relying on the duplicate flag.
Sample duplicate flags
| Value | All-duplicates mode | After-first mode |
|---|---|---|
| A-100 first row | Duplicate | |
| A-101 | ||
| A-100 second row | Duplicate | Duplicate |
| Blank row |
Field guide
| Field | Example | Why it matters |
|---|---|---|
| Range to check | $A$2:$A$100 | This is the full list of possible duplicate values. |
| Current cell | A2 | This is the value being tested on the current row. |
| Flagging mode | after-first | Controls whether the first copy is marked. |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| A duplicate-looking value is not flagged | The source values may contain hidden spaces or mixed text and number formats. | Trim or normalize the source column, then rerun the duplicate check on the cleaned values. |
| Blank rows are marked as duplicates | The blank-row guard is turned off or was removed from the formula. | Keep Ignore blank rows enabled so the formula starts with IF(A2="", "", ...). |
| The formula changes when filled down | The check range is not locked with absolute references. | Use a range such as $A$2:$A$100 before filling the formula down. |
Common mistakes
- Use absolute references for the full check range before filling the formula down.
- Leading or trailing spaces can hide duplicates that look identical.
- Choose after-first mode if the first item should remain clean.
Related formulas
FAQ
Does this duplicate checker work in Google Sheets?
Yes. The generated COUNTIF and IF formula works in both Excel and Google Sheets for this one-column duplicate check.
How do I flag only later duplicates?
Choose the after-first mode. The generated COUNTIF range grows from the first row to the current row.
Can this check two columns together?
Use a COUNTIFS formula or a helper key when the duplicate definition depends on two or more columns.
Why are duplicates with spaces not flagged?
COUNTIF compares the stored text. Clean leading and trailing spaces before checking duplicates.