The opposite of flagging duplicates: highlight the values that appear exactly once. A COUNTIF = 1 rule surfaces the one-offs — useful for finding singletons in a list.
= 1 means it occurs once — a unique (non-repeated) value.
The example
Values appearing only once are flagged.
| A | B | |
|---|---|---|
| 1 | Value | Count |
| 2 | A-100 | 2 |
| 3 | A-103 | 1 |
The formula
Flag the one-offs:
How it works
COUNTIF distinguishes singletons from repeats:
COUNTIF(range, value)counts occurrences of the current value.= 1is TRUE only for values that appear once — the uniques.- Lock the range absolutely; keep the test cell relative.
- Built-in shortcut: Highlight Cells Rules → Duplicate Values has a “Unique” option in its dropdown.
Distinct vs unique: “unique” here means appears exactly once. If you want a distinct list (each value once, including repeated ones), that’s the UNIQUE function or the count-unique recipe — a different question.
Try it: interactive demo
One-offs highlight.
Variations
Duplicates instead
Repeated values:
Built-in
Highlight Cells Rules → Duplicate Values → Unique.
Count the uniques
How many one-offs:
Pitfalls & errors
Lock the range. $A$2:$A$20 absolute; relative test cell.
Unique ≠ distinct. This flags values seen once; it doesn’t build a de-duplicated list.
Case & spaces. COUNTIF ignores case; trailing spaces split a value into two.
Practice workbook
Frequently asked questions
How do I highlight only unique values in Excel?
What's the difference between unique and distinct?
Is there a built-in option?
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