# How to find duplicates in a column in Excel

Count each value in the whole column: =IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate",""). Any value that appears more than once is flagged.

**Excel:** `=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Email | Duplicate? |
| ana@northwind.example |  |
| ben@contoso.example |  |
| cara@fabrikam.example |  |
| ana@northwind.example |  |
| dev@litware.example |  |
| ben@contoso.example |  |
| eli@adatum.example |  |
| ana@northwind.example |  |
| fay@proseware.example |  |

Formula in B2, filled to B10: `=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","")`

Result: Duplicate; Duplicate; ; Duplicate; ; Duplicate; ; Duplicate; 

## How it works

COUNTIF counts how many times the email in this row appears in the whole list. The list is locked with `$` so it stays the same as you fill down, while `A2` moves to each row. More than one means the email is repeated, and every copy gets flagged.

## Other ways

- `=COUNTIF($A$2:$A$10,A2)>1`: Returns TRUE or FALSE, which you can filter on or use in conditional formatting. (TRUE and FALSE instead of labels.)

## Common mistakes

- Without $ the range shrinks as you fill down, and later duplicates are missed.
- COUNTIF ignores case and treats "ana@…" and "ANA@…" as the same. Extra spaces make them different; TRIM first.
- COUNTIF with text longer than 255 characters returns #VALUE!. Use SUMPRODUCT(--(range=A2)) for long text.

## Questions

**How do I highlight duplicates instead?**

Use the test as a conditional formatting rule: `=COUNTIF($A$2:$A$10,A2)>1`.

**How do I flag only the second and later copies?**

Count from the top to the current row: `=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","")`.

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

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