The formula
TRUE if the value appears more than once (fill down)ExcelGoogle Sheets
=COUNTIF($A$2:$A$8,A2)>1In C2 → TRUE
Flag repeats only (keeps first) (fill down)ExcelGoogle Sheets
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","")In D2 → (blank)
Count of duplicate rowsExcelGoogle Sheets
=SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))In F2 → 5
✓ Tested. All 15 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | ana@x.com | TRUE | 5 | |||
| 3 | ben@x.com | TRUE | ||||
| 4 | ana@x.com | TRUE | Duplicate | |||
| 5 | cara@x.com | FALSE | ||||
| 6 | ben@x.com | TRUE | Duplicate | |||
| 7 | dev@x.com | FALSE | ||||
| 8 | ana@x.com | TRUE | Duplicate |
How it works
- $A$2:$A$8
- The whole list, locked.
- $A$2:A2
- A growing range: only rows so far, so the first occurrence isn't flagged.
- SUMPRODUCT(--(…>1))
- Counts the rows whose value is duplicated.
Common errors and fixes
Not flagged although it looks the same?
Trailing spaces or different spelling; COUNTIF ignores case.
Long numbers flagged wrongly?
COUNTIF compares numbers only to 15 digits; IDs longer than that may match falsely. Store them as text.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.