What it does
UNIQUE writes each value of the range once, in the order it first appears, and spills the result below the formula. If you give it several columns, whole rows are compared: the same customer in two different regions stays as two rows.
When the third argument is TRUE, only values that occur exactly once come back, such as customers who ordered just once. When the second argument is TRUE, columns are compared instead of rows, for lists that run across. The comparison is not case-sensitive.
For an alphabetical list, wrap the result in SORT: =SORT(UNIQUE(B2:B8)). To see how often each value occurs, put COUNTIF next to it; for a unique list that meets a condition, apply FILTER first. In Excel 2019 and earlier, Data > Remove Duplicates gives a fixed list.
Syntax
=UNIQUE(array, [by_col], [exactly_once])=BENZERSİZ(dizi; [sütuna_göre]; [yalnızca_bir_kez])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range to remove repeats from, e.g. B2:B8. With several columns, rows are compared as a whole. |
by_col | Optional | FALSE or omitted: compares rows (lists running down). TRUE: compares columns (lists running across). |
exactly_once | Optional | FALSE or omitted: lists every distinct value once. TRUE: lists only values that occur exactly once. |
Examples
1List customers without repeats and count them
UNIQUE in E2 spills each customer name once. COUNTIF in F2 is filled down and counts how many orders each customer placed.
=UNIQUE(B2:B8)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | Orders | |
| 2 | 1-Oct-2026 | North Market | 3,200 | North Market | 3 | |
| 3 | 3-Oct-2026 | Atlas Ltd | 1,850 | Atlas Ltd | 2 | |
| 4 | 8-Oct-2026 | North Market | 4,100 | Blue Electric | 1 | |
| 5 | 12-Oct-2026 | Blue Electric | 2,600 | West Build | 1 | |
| 6 | 15-Oct-2026 | Atlas Ltd | 2,900 | |||
| 7 | 21-Oct-2026 | West Build | 1,450 | |||
| 8 | 27-Oct-2026 | North Market | 3,750 |
- E2 (the result spills into E2:E5)
=UNIQUE(B2:B8) - F2 (filled down to F5)
=COUNTIF($B$2:$B$8,E2)
Seven orders come from four different customers; the list spills into E2:E5, ending with West Build. North Market placed 3 orders.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Find customers who ordered only once
With the third argument set to TRUE, values that occur more than once are dropped entirely. The second argument is left empty (,,).
=UNIQUE(B2:B8,,TRUE)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | One-time Customers | |
| 2 | 1-Oct-2026 | North Market | 3200 | Blue Electric | |
| 3 | 3-Oct-2026 | Atlas Ltd | 1850 | West Build | |
| 4 | 8-Oct-2026 | North Market | 4100 | ||
| 5 | 12-Oct-2026 | Blue Electric | 2600 | ||
| 6 | 15-Oct-2026 | Atlas Ltd | 2900 | ||
| 7 | 21-Oct-2026 | West Build | 1450 | ||
| 8 | 27-Oct-2026 | North Market | 3750 |
- E2 (the result spills into E2:E3)
=UNIQUE(B2:B8,,TRUE)
North Market and Atlas Ltd ordered more than once, so they are left out; only Blue Electric and West Build remain. It is a handy list of customers to follow up with.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #SPILL!
Cause: One of the cells the result spills into is not empty.
Fix: Clear the cells below the formula; the list grows as the number of distinct values grows.
- Same value twice
Cause: One of the values has an invisible trailing space, or one is stored as text and the other as a number.
Fix: Clean the source as you pass it in:
=UNIQUE(TRIM(B2:B8)).- #CALC!
Cause: exactly_once is
TRUEand no value occurs only once.Fix: Wrap the result in IFERROR and show a message.
Good to know
- Empty cells in the range show up as 0 in the result; filter them out first:
=UNIQUE(FILTER(B2:B50,B2:B50<>"")). - To count distinct values, use
=ROWS(UNIQUE(B2:B8))(ROWS).
In Nexell
Get Nexell to do it from a sentence
List the customers in the order list without repeats and write how many orders each one placed
Convert to tableBring your order list into the tool and type this. Nexell lists each customer once, writes their order counts next to them with conditional count formulas and shows the result in a preview first.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between UNIQUE and Remove Duplicates?
Data > Remove Duplicates deletes the repeated rows from the source table for good. UNIQUE leaves the source alone; it builds the list without repeats somewhere else with a formula and keeps it up to date as the source changes.
How do I count distinct values in a column in Excel?
In Microsoft 365 and Excel 2021, use =ROWS(UNIQUE(B2:B8)). In older versions, =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8)) gives the same result, as long as the range has no empty cells.
Related functions
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- SORTSIRALA · Sorts a range (1 ascending, -1 descending).
- COUNTIFEĞERSAY · Counts cells that meet a condition.
- VSTACKDÜŞEYYIĞ · Stacks arrays vertically.
- SORTBYSIRALAÖLÇÜT · Sorts a range by the values in another array.