# How to flag duplicates in a column in Google Sheets

Count each value in the column with COUNTIF inside ARRAYFORMULA: `=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))`.

**Google Sheets:** `=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))`

**Excel:** In Excel 2021 and later the same formula without ARRAYFORMULA spills: `=IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))`.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Email | Check |
| ana@ruiz.example |  |
| raj@patel.example |  |
| lee@chen.example |  |
| ana@ruiz.example |  |
| kim@ortiz.example |  |
| mo@diaz.example |  |
| raj@patel.example |  |
| sam@cole.example |  |
|  |  |
| eve@lund.example |  |

Formula in B2: `=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))`

Result (spills 10 × 1): Duplicate; Duplicate; ; Duplicate; ; ; Duplicate; ; ; 

## How it works

With the whole column as its criteria, COUNTIF counts every email at once: 2 for each copy of ana@ and raj@, 1 for the others. ARRAYFORMULA lets that happen in one formula, and the label goes on every copy, the first one included. The blank test keeps row 10 empty.

## Other ways

- `=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))>1,"Repeat","")))`: Flags only the second and later copies, so you can delete those rows and keep the first. (Only rows 5 and 8 say Repeat.)
- `=IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))`: The Excel version, which spills on its own.

## Common mistakes

- COUNTIF ignores case, so "Ana@Ruiz.example" counts as a duplicate of "ana@ruiz.example". That's usually right for emails.
- A trailing space makes a copy look different. Wrap the column in TRIM inside the formula if the data was pasted.

## Questions

**How do I highlight duplicates instead?**

Use conditional formatting with the custom formula `=COUNTIF($A$2:$A$11,A2)>1`.

**How do I remove the duplicates?**

List each email once in another column with `=UNIQUE(A2:A11)`, or use Data > Data cleanup > Remove duplicates.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/flag-duplicates-in-a-column