Array Formulasbeginner
Extract a unique list with UNIQUE
=UNIQUE(A2:A100)
Excel how-to guide
How to remove duplicates countif in Excel
Identify duplicate order IDs, email addresses, or product codes in an imported list before deciding which to keep or delete.
Step-by-step instructions
How to remove duplicates countif — 3 steps
1
Lock the range with absolute references so it does not shift as you fill the formula down.
2
Use the current row's cell as the criteria — COUNTIF counts how many times that value appears in the range.
3
The >1 comparison returns TRUE for every row that is a duplicate and FALSE for unique values.
Example data
Worked example
| Order ID | Duplicate? |
|---|---|
| ORD-001 | false |
| ORD-002 | true |
| ORD-002 | true |
| ORD-003 | false |
Common mistakes
Errors to watch out for
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.
FAQ
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.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Data Cleaningbeginner
Validate numeric entries with ISNUMBER
=ISNUMBER(A2)