Spot repeats at a glance. A conditional-formatting rule built on COUNTIF shades every value that appears more than once — and a tiny tweak lets you highlight only the second-and-later copies instead.
$; the test cell A2 is relative so it walks down the column.
The example
Order IDs — the repeated ones are flagged.
| A | B | |
|---|---|---|
| 1 | Order ID | Count |
| 2 | A-100 | 2 |
| 3 | A-101 | 1 |
| 4 | A-100 | 2 |
| 5 | A-102 | 1 |
The formula
The conditional-formatting rule formula:
How it works
Conditional formatting applies a format wherever the rule returns TRUE:
- Select the range, then Home → Conditional Formatting → New Rule → Use a formula.
- Enter
=COUNTIF($A$2:$A$20, A2) > 1— absolute range, relative test cell (matching the active cell of your selection). - COUNTIF counts how many times the current value appears;
> 1means it’s a duplicate. - Pick a fill color and click OK. Excel evaluates the rule for every cell, shading all duplicates.
Keep the first, flag the rest? Use =COUNTIF($A$2:A2, A2) > 1 — the range grows from the top to the current row, so only the 2nd+ occurrences are TRUE. There’s also a built-in shortcut: Conditional Formatting → Highlight Cells Rules → Duplicate Values.
Try it: interactive demo
Edit the list; duplicates highlight automatically.
Variations
Flag only repeats (keep first)
Expanding range from the top:
Unique values only
Highlight the one-offs instead:
Duplicates across two columns
Match the whole row key:
Pitfalls & errors
Lock the range, not the cell. Use $A$2:$A$20 (absolute) but a relative test cell A2. Locking the test cell too makes every row test the same value.
Match the active cell. The relative reference must match the top-left (active) cell of your selection, or the rule shifts and highlights the wrong cells.
COUNTIF ignores case and treats numbers loosely. “abc” and “ABC” count as duplicates; for case-sensitive matching use a SUMPRODUCT/EXACT rule.
Practice workbook
Frequently asked questions
How do I highlight duplicate values in Excel with conditional formatting?
How do I highlight only the duplicates after the first one?
Is there a built-in shortcut?
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