Cleanup formula page
Flag Duplicate Values Formula
Use this when each source row needs a Duplicate label next to it.
Flag duplicate values in Excel or Google Sheets.
Rows containing maya@example.com are flagged as Duplicate.
Copy formulas
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))The fixed COUNTIF range flags every nonblank copy of a repeated value, while the blank guard keeps empty rows unlabeled in both Excel and Google Sheets.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
Rows containing maya@example.com are flagged as Duplicate.
How the formula works
- The blank guard skips empty rows.
- COUNTIF counts the current value across the full range.
- Values appearing more than once get the Duplicate label.
| Syntax piece | Role in the formula |
|---|---|
| A2="" | The blank guard that leaves empty source rows unlabeled. |
| $A$2:$A$100 | The fixed full range scanned for every copied formula. |
| A2 | The current row value being checked. |
| >1 | The threshold that identifies values appearing at least twice. |
Verified examples
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Excel: Fill the formula down beside the sample column containing a repeated email, blanks, and unique values. Returns: Duplicate, blank, Duplicate, blank
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Google Sheets: Enter Maya and maya as two nonblank values in A2:A3. Returns: Both rows show Duplicate
=IF(A2="", "", IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", ""))Excel: Fill down beside a source column where every nonblank value occurs once. Returns: Blank labels
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| Values with hidden spaces are not grouped | COUNTIF sees text with different spaces as different values. | Clean the source values with TRIM before applying the flag formula. |
| Maya and maya are both flagged | COUNTIF comparisons are case-insensitive. | Use a case-sensitive helper calculation when letter case defines identity. |
| Copied formulas stop checking the full list | The source range was not locked with absolute references. | Keep $A$2:$A$100 fixed while leaving the current A2 reference relative. |
When not to use this formula
- Do not use this pattern when only the second and later copies should be flagged; use the expanding-range version instead.
Alternatives
| Alternative | When to use it |
|---|---|
| Flag Second and Later Duplicates Formula | Use when the first occurrence should remain unlabeled. |
| Duplicate Checker Formula Builder | Use when the duplicate label and source range need interactive configuration. |
Related formulas
Official references
- Use the COUNTIF function in Microsoft Excel from Microsoft
FAQ
Are both copies flagged?
Yes. Every nonblank value with a count above 1 receives the Duplicate label.
Are blank rows flagged?
No. The A2="" guard returns a blank label for empty source rows.