To mark which entries in a list repeat, count how many times each value appears with COUNTIF and flag the ones with a count above 1. The same COUNTIF test drives a conditional-formatting rule to highlight them in color.
COUNTIF counts how many times A2 appears in the whole locked list; more than one means it’s a duplicate.
The example
A list of order IDs — two of them repeat.
| A | B | |
|---|---|---|
| 1 | Order ID | Flag |
| 2 | A-100 | |
| 3 | A-101 | Duplicate |
| 4 | A-102 | |
| 5 | A-101 | Duplicate |
| 6 | A-103 |
The formula
The flag formula, filled down:
How it works
One COUNTIF does the work:
COUNTIF($A$2:$A$6, A2)counts how many cells in the locked list equal the current value.- The list range is absolute (
$A$2:$A$6) so it stays fixed as you copy; the tested valueA2is relative so it moves row by row. - If the count is greater than 1, the value occurs more than once → "Duplicate"; otherwise a blank string.
- To flag only the second and later occurrences, use a growing range:
COUNTIF($A$2:A2, A2)>1.
Try it: interactive demo
Type a comma-separated list; duplicates are flagged the way the formula would.
Variations
Highlight duplicates with conditional formatting
Select the list, then Home → Conditional Formatting → New Rule → Use a formula, and enter:
Pick a fill color. Every duplicate cell turns that color, updating live as data changes.
Flag only repeats (keep the first)
Counts occurrences up to the current row, so the first stays unflagged:
Duplicates across two columns
Concatenate the key first:
Pitfalls & errors
Everything (or nothing) flagged. The list range must be locked with $. Without it, the range drifts as you copy and the counts go wrong.
COUNTIF ignores case. "ABC" and "abc" count as the same. For case-sensitive duplicate detection use SUMPRODUCT(--EXACT($A$2:$A$8, A2))>1.
Numbers stored as text. "100" (text) and 100 (number) may or may not be treated as equal depending on the cell — clean the column first if results look off.
Practice workbook
Frequently asked questions
How do I flag duplicates in Excel with a formula?
How do I highlight duplicates instead of labeling them?
How do I flag duplicates but keep the first occurrence?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class