Excel Formula to Flag Duplicate Values
By Szabó Gergő · Updated
COUNTIF on the whole column tells you how many times this value appears. A helper column is easier to audit than conditional formatting alone.
Syntax and arguments
=IF(COUNTIF($A$2:$A$200,A2)>1,"Duplicate","")- value
- The cell to test, such as A2.
- list
- The full column, locked with $.
Highlight duplicates examples
IDs in A2:A200.
=IF(COUNTIF($A$2:$A$200,A2)>1,"Duplicate","")Every row that shares an ID is marked, including the first.
Keep the first ID clean.
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","")The range grows, so the first occurrence stays blank.
Microsoft 365.
=UNIQUE(A2:A200)A distinct list, not a per-row flag. Use it for a clean export.
Common mistakes
Not locking the list
Use $A$2:$A$200 or each row counts a different window.
Case and spaces
COUNTIF is not case-sensitive. TRIM the column if spaces create fake uniques.
Conditional formatting only
A helper column can be filtered and summed. Formatting cannot.
Highlight duplicates FAQ
Can I flag based on two columns?
=IF(COUNTIFS($A$2:$A$200,A2,$B$2:$B$200,B2)>1,"Duplicate","").
How do I count distinct IDs?
=COUNTA(UNIQUE(FILTER(A2:A200,A2:A200<>""))) on Microsoft 365.
Does this work on numbers stored as text?
Yes if both sides are text. Mixes of 001 and 1 will not match.