The Excel UNIQUE function returns a list with the duplicates removed — live. Unlike the Remove Duplicates command, it never touches your source data, and the deduplicated list updates itself the moment new rows arrive. It also has a second trick: with exactly_once it returns only the values that appear one single time.
=SORT(UNIQUE(A2:A9)).
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to deduplicate. Multi-column ranges are compared as whole rows. |
by_col | Optional | FALSE (default) compares rows against each other. TRUE compares columns — for horizontal data. |
exactly_once | Optional | FALSE (default) returns every distinct value once. TRUE returns only values that occur exactly one time in the source. |
Available in: Excel for Microsoft 365, Excel 2021+, and Excel for the web. Older versions show #NAME?. Results keep the order of first appearance — UNIQUE does not sort.
Remove duplicates — without touching the data
The customer column A2:A9 contains repeats. C2 holds plain UNIQUE; E2 adds the exactly_once switch, so it keeps only the customers who appear a single time:
| A | C | E | |||
|---|---|---|---|---|---|
| 1 | Customer | UNIQUE | Exactly once | ||
| 2 | Acme | Acme | Cedar | ||
| 3 | Bolt | Bolt | Delta | ||
| 4 | Acme | Cedar | Echo | ||
| 5 | Cedar | Delta | |||
| 6 | Bolt | Echo | |||
| 7 | Delta | ||||
| 8 | Acme | ||||
| 9 | Echo |
Note the empty by_col slot — the double comma keeps the arguments in position. Add a row with a new customer and both spilled lists grow on their own.
Try it: interactive UNIQUE demo
Toggle exactly_once and watch which customers survive from the list above.
Distinct counts and dynamic-array combos
The classic "how many distinct customers?" question becomes one formula:
UNIQUE nests beautifully with the other dynamic array functions:
Given several columns, UNIQUE deduplicates whole rows — perfect for distinct customer/region pairs:
This makes UNIQUE the natural engine for report row labels: spill the distinct values, then point SUMIFS at the spill with C2#.
Errors & common pitfalls
#SPILL! — the landing zone isn’t empty. UNIQUE needs one blank cell per distinct value. Clear whatever sits in the spill range — click the error’s warning icon to see the blocker.
#CALC! — nothing to return. With exactly_once TRUE, if every value repeats there are no survivors and Excel returns #CALC! (an empty array). Wrap in IFERROR to show a friendly message instead.
#NAME? — older Excel. UNIQUE requires Excel 2021+ or Microsoft 365. In Excel 2019 and earlier, use Remove Duplicates (destructive) or a PivotTable instead.
Pitfall: invisible differences create "duplicate" survivors. "Acme " with a trailing space and "Acme" are different values, and a number stored as text differs from the real number. Clean with TRIM or convert text-numbers first. (Letter case, on the other hand, is ignored — "ACME" and "acme" count as the same value.)
Pitfall: blank cells become 0. Empty cells inside the range come back as a 0 in the result. Filter them away first: =UNIQUE(FILTER(A2:A100, A2:A100<>"")).
Practice workbook
Frequently asked questions
What's the difference between UNIQUE and Remove Duplicates?
What does exactly_once actually do?
=UNIQUE({1;2;2;3}) returns 1, 2, 3 — but with TRUE it returns just 1 and 3, because 2 repeats.How do I count distinct values?
=ROWS(UNIQUE(A2:A100)). Use COUNTA instead of ROWS if you might pass a horizontal array.How do I get a sorted unique list?
=SORT(UNIQUE(A2:A100)). UNIQUE alone keeps first-appearance order.Is UNIQUE case-sensitive?
Can UNIQUE work across multiple columns?
=UNIQUE(A2:B100) lists every unique combination of the two columns.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class