Data Cleaningbeginner
Flag duplicate values with COUNTIF
=COUNTIF($A$2:$A$100,A2)>1
Function reference
COUNTIF in Excel & Google Sheets
COUNTIF is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.
1 beginnerBuild a custom COUNTIF formula →
When to use COUNTIF
- Identify duplicate order IDs, email addresses, or product codes in an imported list before deciding which to keep or delete.
Worked examples (1)
Common errors with COUNTIF
Formula does not catch all duplicates
Make sure the range in the first argument is absolute ($A$2:$A$100) so it covers the entire column as you fill down.
Case sensitivity
COUNTIF is not case-sensitive — "Apple" and "apple" are counted as the same value.
Frequently asked questions
How do I mark only the second and subsequent occurrences, not the first?
Use =COUNTIF($A$2:A2,A2)>1 with a mixed reference — the range expands as you fill down, so it only flags rows after the first occurrence.
Need a custom variation?
Describe your specific COUNTIF problem and get a formula written for your exact data.
Open generator with COUNTIF