How to check if a column has any duplicates in Excel
Formula (Excel and Google Sheets)
=IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique")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").
- Result on the example
- Has duplicates in
C2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
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.
=IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique")Result"Has duplicates"
A2:A87 rows × 1 col: "B-1042", "B-1043", "B-1051", "B-1043", …A range: 7 cells in one column.
COUNTIF(A2:A8,A2:A8){7 rows × 1 col} 1, 2, 1, 2, …COUNTIF: Counts cells that meet one condition.- range
A2:A87 rows × 1 col: "B-1042", "B-1043", "B-1051", "B-1043", … - criteria
A2:A87 rows × 1 col: "B-1042", "B-1043", "B-1051", "B-1043", …
- range
COUNTIF(A2:A8,A2:A8)>1{7 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The > operator tests greater than.
-(COUNTIF(A2:A8,A2:A8)>1{7 rows × 1 col} 0, -1, 0, -1, …Makes the value negative.
--(COUNTIF(A2:A8,A2:A8)>1{7 rows × 1 col} 0, 1, 0, 1, …Makes the value negative.
SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))2SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(COUNTIF(A2:A8,A2:A8)>1{7 rows × 1 col} 0, 1, 0, 1, …
- array1
SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0TRUEThe > operator tests greater than.
IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique")"Has duplicates"IF: Returns one value if a test is true, another if false.- logical_test
SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0TRUE - value_if_true
"Has duplicates" - value_if_false
"All unique"not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique"). We ran it in Google Sheets mode on the same data and it returns Has duplicates.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(ROWS(A2:A8)>ROWS(UNIQUE(A2:A8)),"Has duplicates","All unique") | Has duplicates | 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") | Has duplicates | 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".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Check if a column has any duplicates
C2, show "Has duplicates" when any badge ID in A2:A8 appears more than once, otherwise "All unique".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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.