UNIQUE Function
Returns a list with duplicates removed.
Category
Lookup & Reference
Available in
Microsoft 365, Excel 2021+
Returns
A spilled list of unique values
Overview
UNIQUE gives you each distinct value once. Unlike Remove Duplicates, it doesn't change your data — and it updates as the list changes.
It can also return values that appear exactly once, which is handy for spotting one-offs.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The range or array to de-duplicate. |
| by_col | Optional | FALSE = compare rows (default), TRUE = compare columns. |
| exactly_once | Optional | TRUE = return only values that occur once. FALSE (default) = every distinct value. |
Examples
Example 1 — Distinct regions
List each region once.
C2fx=UNIQUE(A2:A6)
| A | B | C | |
|---|---|---|---|
| 1 | Region | Unique | |
| 2 | East | East | |
| 3 | West | West | |
| 4 | East | North | |
| 5 | North | ||
| 6 | West |
=UNIQUE(A2:A6) Explain
Example 2 — Sorted, counted list
Combine with SORT and COUNTA for a clean, sorted list and its size.
=SORT(UNIQUE(A2:A100)) Explain
And =COUNTA(UNIQUE(A2:A100)) tells you how many distinct regions there are.
Pass two columns to get unique combinations: =UNIQUE(A2:B100).
Common mistakes
- A 0 in the result. Blank cells count as a value. Use UNIQUE(FILTER(A2:A100, A2:A100<>"")).
- Case-insensitive. "east" and "East" are treated as the same value.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.