The UNIQUE function in Excel
UNIQUE returns each distinct value in a range once, and the list spills into the cells below. It's the formula version of Remove Duplicates, except it leaves the data alone and updates automatically. Use it to build a list of customers, regions or products for a summary table, or wrap it in COUNTA to count distinct values.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
| array | The range to take distinct values from, like B2:B9. Several columns compare whole rows. |
|---|---|
| by_col | Optional. FALSE compares rows (the default). TRUE compares columns instead. |
| exactly_once | Optional. TRUE returns only values that appear exactly once. FALSE (the default) returns every distinct value. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In F2, use UNIQUE to list each department from C2:C8 once. The list will spill down from F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Department | Departments | |||||
| 2 | Finance | ||||||
| 3 | Sales | ||||||
| 4 | Ops | ||||||
| 5 | Sales | ||||||
| 6 | Finance | ||||||
| 7 | HR | ||||||
| 8 | Sales | ||||||
| 9 | |||||||
| 10 | |||||||
| 11 |
Tip: UNIQUE only needs one argument here: the Department column.
Examples
=COUNTA(UNIQUE(B2:B9))→ 5Counts the distinct customers. Harbor Café ordered three times but is counted once.
=TEXTJOIN(", ",TRUE,UNIQUE(C2:C9))→ "West, East, North"Lists each region once, in the order it first appears. TEXTJOIN shows the spilled list in one cell.
=TEXTJOIN(", ",TRUE,UNIQUE(B2:B9,FALSE,TRUE))→ "Pine & Co, Kestrel Labs, Oak Street Books"With exactly_once set to TRUE, only customers with a single order are returned.
Example data
| 1 | Order | Customer | Region | Amount |
|---|---|---|---|---|
| 2 | SO-301 | Harbor Café | West | 420 |
| 3 | SO-302 | Lumen Studio | East | 315 |
| 4 | SO-303 | Harbor Café | West | 280 |
| 5 | SO-304 | Pine & Co | North | 990 |
| 6 | SO-305 | Kestrel Labs | East | 145 |
| 7 | SO-306 | Lumen Studio | East | 610 |
| 8 | SO-307 | Harbor Café | West | 375 |
| 9 | SO-308 | Oak Street Books | North | 230 |
Common mistakes
#SPILL! error
UNIQUE needs empty cells below the formula for its results. Clear whatever is in the way, or put the formula in an empty column.
Blank rows in the range
An empty cell inside the range comes back as a 0 in the list. Use a range that stops at the last row of data, or remove the blanks with FILTER first.
Near-duplicates stay separate
UNIQUE ignores upper and lower case, but
"West "with a trailing space is a different value from"West". Clean the data with TRIM first.Selecting several columns by accident
UNIQUE(B2:C9)returns distinct combinations of customer and region, not distinct customers. Select one column for a simple list.
FAQ
How do I count unique values in Excel?+
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(B2:B9)). In versions without UNIQUE, people used SUMPRODUCT with COUNTIF instead.
How do I get a unique list in alphabetical order?+
Wrap it in SORT: =SORT(UNIQUE(B2:B9)).
Which versions of Excel have UNIQUE?+
Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions show #NAME?, so use Remove Duplicates on the Data tab there.
Lesson
Master UNIQUE step by step: UNIQUE: list and count distinct values
Pull a clean list of distinct regions or customers with UNIQUE, then count them with COUNTA.