The UNIQUE function in Excel
UNIQUE returns a list without duplicates: every customer, every region, every product once. Unlike Remove Duplicates, it doesn't touch the original data and updates when new rows arrive.
The result spills into the cells below. Available in Excel 365 and Excel 2021 or newer.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
| Argument | What it means |
|---|---|
| array | The range to take distinct values from. |
| by_coloptional | TRUE compares columns instead of rows. Default FALSE. |
| exactly_onceoptional | TRUE returns only the values that appear exactly once. |
Examples
1. Each value once
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Regions | |
| 2 | 1001 | North | 250 | Paid | North | |
| 3 | 1002 | South | 90 | Paid | South | |
| 4 | 1003 | North | 420 | Open | East | |
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
The values appear in the order they're first found. Wrap in SORT for A to Z.
2. How many different values?
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Number of regions | |
| 2 | 1001 | North | 250 | Paid | 3 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Count the unique list with COUNTA: three regions.
3. Values that appear only once
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | One order only | |
| 2 | 1001 | North | 250 | Paid | South | |
| 3 | 1002 | South | 90 | Paid | East | |
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
With exactly_once TRUE, North (three orders) is left out.
Common mistakes
"Duplicates" that aren't removed. Extra spaces or different spelling make values distinct.
Fix: Clean the data first, e.g.
=UNIQUE(TRIM(B2:B6)).#SPILL! error. The cells below aren't empty.
Fix: Clear the space the list needs.
Frequently asked questions
Can UNIQUE work on several columns?
Yes: =UNIQUE(B2:C6) returns each distinct combination of the two columns.
Related functions
- SORTSORT returns a sorted copy of a range that updates when the data changes, ascending or descending, by any column.
- FILTERFILTER returns the rows of a range that meet a condition, and spills them into the cells below.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.