# How to check if a column has any duplicates in Excel

Count each value against the whole list and see if any count is above 1: =IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique").

**Excel:** `=IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Badge ID |  | Duplicates? |
| B-1042 |  |  |
| B-1043 |  |  |
| B-1051 |  |  |
| B-1043 |  |  |
| B-1060 |  |  |
| B-1061 |  |  |
| B-1072 |  |  |

Formula in C2: `=IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique")`

Result: Has duplicates

## How it works

`COUNTIF(A2:A8,A2:A8)` counts every ID against the whole list, giving one count per row. A count above 1 is a repeat; SUMPRODUCT adds up how many rows repeat, and IF turns that into a verdict. B-1043 appears twice.

## Other ways

- `=IF(ROWS(A2:A8)>ROWS(UNIQUE(A2:A8)),"Has duplicates","All unique")`: Excel 365 and Google Sheets: fewer unique values than rows means something repeats.
- `=IF(MAX(COUNTIF(A2:A8,A2:A8))>1,"Has duplicates","All unique")`: Excel 365 and 2021 evaluate the array directly; in Google Sheets, wrap it in ARRAYFORMULA.

## Common mistakes

- Blank cells in the range count as duplicates of each other. Size the range to the data.
- COUNTIF ignores case, so "b-1043" counts as a repeat of "B-1043".

## Questions

**How do I see which values repeat?**

Flag each row with `=COUNTIF($A$2:$A$8,A2)>1`, or list them in Excel 365 with `=UNIQUE(FILTER(A2:A8,COUNTIF(A2:A8,A2:A8)>1))`.

**How do I stop duplicates being entered?**

Use Data Validation, Custom, with `=COUNTIF($A$2:$A$100,A2)=1`.

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/check-if-column-has-duplicates