Excel UNIQUE Function (Distinct Values) – Examples & Practice
Practice the Excel UNIQUE function online with an interactive grid, instant feedback, and clear formula help.
Instruction
List distinct values from range A2:A6.
Formula Syntax
=UNIQUE(array, [by_col], [exactly_once])
- array: Range or array to dedupe.
- [by_col]: Optional; compare unique rows vs unique columns.
- [exactly_once]: Optional; return items that appear exactly once.
What it does
UNIQUE returns the distinct values (or distinct rows) from an array. It spills results automatically in dynamic Excel.
Excel UNIQUE Function Examples
Distinct list from one column
=UNIQUE(A2:A20)
Spills the distinct values from A2:A20.
Sorted distinct
=SORT(UNIQUE(A2:A20))
Alphabetizes after deduping.
Rows that appear only once
=UNIQUE(A2:A20, , TRUE)
Uses the third argument to focus on values that occur exactly once (verify with your Excel build docs).
dynamic-array-data.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | Region |
| 2 | A | 120 | North |
| 3 | B | 340 | South |
| 4 | A | 560 | North |
| 5 | C | 225 | West |
| 6 | B | 90 | South |
| 7 | Output |
Input Formula
Need Help?
Tips
- Wrap with **SORT** for stable presentation.
- Use **FILTER** first when only a subset should be deduped.
- Document whether blanks should be removed upstream.
UNIQUE Function Use Cases
- Build dropdown source lists without manual maintenance
- Prep data for COUNTIFS keys
- Audit duplicates before imports
- Compact summaries beside raw tables
- Pair with FILTER for powerful pipelines
Common mistakes - UNIQUE function not working
- Expecting UNIQUE to ignore case (it does not unless you normalize with LOWER in a helper)
- Forgetting blanks are values too
- Two-column uniqueness forgotten—use a wide array input
- Spill blocked by merged cells
- Confusing unique count with COUNTA of UNIQUE
FAQ
Does UNIQUE reorder values?
It returns distinct values in the order they first appear unless you wrap results with SORT.
What does exactly_once do?
When TRUE, UNIQUE can return only items that appear exactly once in the source (behavior depends on shape and options).
UNIQUE vs Remove Duplicates?
Remove Duplicates is a one-time command; UNIQUE is a live formula that updates when data changes.
Can UNIQUE work on two columns?
Yes. Widen the array argument to multiple columns to treat rows as unique tuples.
Why #SPILL!?
The spill area is blocked or too small—clear cells below/right of the formula.
Comparison
| Approach | Updates live? |
|---|---|
| UNIQUE | Yes |
| Remove Duplicates | No |
| Pivot distinct count | Refresh-driven |
Example
=UNIQUE(A2:A6)=COUNTA(UNIQUE(A2:A20))
Advanced examples
UNIQUE + FILTER
=UNIQUE(FILTER(C2:C20, B2:B20>0))
Distinct categories among rows that pass a numeric test.
Case-insensitive distinct helper
=UNIQUE(LOWER(TRIM(A2:A100))) when casing and spaces should not split keys.