Cleanup formula page
Flag Second and Later Duplicates Formula
Use this when the first item should stay valid but repeated rows should be marked.
Flag duplicates after the first occurrence.
The first maya@example.com row stays blank and the later repeat is flagged.
Copy formulas
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))The expanding COUNTIF range starts at A2 and ends at the current row, so the first occurrence stays blank while second and later occurrences are labeled.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
The first maya@example.com row stays blank and the later repeat is flagged.
How the formula works
- The expanding range starts at A2 and ends at the current row.
- The first occurrence has a count of 1.
- Later occurrences have a count greater than 1.
| Syntax piece | Role in the formula |
|---|---|
| A2="" | The blank guard that skips empty source rows. |
| $A$2 | The anchored starting cell for the expanding count range. |
| A2 | The relative ending cell that expands as the formula is filled down. |
| >1 | The threshold that identifies a second or later occurrence. |
Verified examples
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))Excel: Fill the formula down beside the sample column with a repeated email, blanks, and unique values. Returns: Blank, blank, Duplicate, blank
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))Google Sheets: Enter A, A, A in A2:A4 and fill the formula down. Returns: Blank, Duplicate, Duplicate
=IF(A2="", "", IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", ""))Excel: Leave selected source rows in A2:A4 blank and fill the formula down. Returns: Blank labels
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The first occurrence is flagged | The formula uses a full fixed range instead of an expanding range ending at the current row. | Use COUNTIF($A$2:A2, A2) so the first copy has a count of 1. |
| A different row is treated as first | The source was sorted after flags were calculated or the first-seen order changed. | Sort before filling the formula and recalculate which row should be the first occurrence. |
| Visually equal values are not grouped | Hidden spaces make the source strings different. | Normalize the source values before filling the duplicate formula. |
When not to use this formula
- Do not use this formula when every copy should be labeled; use the fixed-range duplicate flag instead.
Alternatives
| Alternative | When to use it |
|---|---|
| Flag Duplicate Values Formula | Use when both the first and later copies should be flagged. |
| Duplicate Checker Formula Builder | Use when duplicate handling and labels need interactive setup. |
Related formulas
Official references
- Use the COUNTIF function in Microsoft Excel from Microsoft
FAQ
Why does the expanding range protect the first occurrence?
The first row sees its value once in $A$2:A2, while later rows see the same value more than once.
Can sorting change the result?
Yes. The first row in the current order is treated as the original, so sorting can change which row remains blank.