The Excel UNIQUE function, explained interactively.
UNIQUE returns the distinct values from a range as a spilled array — one formula in one cell, many output cells underneath. Flip the exactly_once argument and the same function returns only values that appear once, a single switch that turns deduplication into outlier-hunting.
How to use UNIQUE
- Pick an empty cell with enough blank space below it to hold every unique value. UNIQUE spills — it writes into the cells around its home.
- Type
=UNIQUE(and select the source range. Press Enter. All distinct values appear, in source order. - To find values that appear exactly once, add two more arguments:
=UNIQUE(A2:A100, FALSE, TRUE). - To sort the output alphabetically, wrap in SORT:
=SORT(UNIQUE(A2:A100)). The modern dynamic-array idiom.
Flip the demo’s exactly_once token from FALSE to TRUE — watch Tokyo and Paris disappear (they appear multiple times) while Berlin, Kyoto, and Oslo remain.
| A | B | |
|---|---|---|
| 1 | Source | Unique |
| 2 | Tokyo | Tokyo |
| 3 | Paris | Paris |
| 4 | Tokyo | Oslo |
| 5 | Oslo | Kyoto |
| 6 | Paris | Berlin |
| 7 | Kyoto | |
| 8 | Berlin | |
| 9 | Tokyo |
UNIQUE syntax and arguments
One required range plus two optional flags. See Microsoft’s official UNIQUE reference for the canonical specification.
by_col is TRUE) — not individual cells within a multi-column range.UNIQUE examples
Four patterns that cover nearly every UNIQUE you’ll ever write.
Example 1: UNIQUE for a basic dedupe
The canonical case — take a column of messy data and produce a clean list of distinct values.
Output order matches the first occurrence of each value. Trailing blank cells in the range appear as a single empty entry in the output; wrap with FILTER(..., A2:A100<>"") to drop them.
Example 2: UNIQUE with exactly_once to find outliers
Hunt for typos, one-time entries, or survey responses that only one person gave.
“Tokyo” appearing 40 times and “Tokoy” once? The second gets flagged. This is UNIQUE’s under-used killer feature — the old Remove Duplicates command can’t do this.
Example 3: SORT wrapped around UNIQUE
Produce a clean, alphabetically sorted distinct list — the most common real-world dynamic-array pattern.
Order by the source’s first occurrence is rarely what a reader wants. Wrapping in SORT is cheap and idiomatic. For descending, pass SORT(UNIQUE(A2:A100), 1, -1).
Example 4: UNIQUE on a multi-column range
Dedupe whole records, not single cells — handy for condensing a transaction log to its distinct customer + product pairs.
UNIQUE treats each row as a tuple: two records with the same customer but different products both survive. Very different from running UNIQUE on column A alone and expecting to recover the column B values.
Common UNIQUE errors and fixes
Four failure modes, each with what to check and how to recover.
UNIQUE returns #SPILL!
Cause: the cells below the formula aren’t empty, so UNIQUE has nowhere to spill. Merged cells also block the spill range.
Clear the blocking cells or move the formula. Hover the blue dashed outline on the formula cell — Excel shows exactly which cell is in the way.
UNIQUE returns #NAME?
Cause: UNIQUE isn’t available in your version of Excel. Introduced in Excel 365 / 2021; older Excel versions don’t recognise it.
Fall back to Data → Remove Duplicates, a PivotTable, or a helper column with COUNTIF. Google Sheets has always had UNIQUE, so if you’re there the function should work.
UNIQUE blending values that should be distinct
Cause: values are case-insensitive-equal or one has trailing whitespace — UNIQUE sees them as the same.
Normalise first: UNIQUE(TRIM(A2:A100)) drops whitespace; UNIQUE(LOWER(A2:A100)) unifies case. For genuinely case-sensitive deduping, combine UNIQUE with EXACT in an array formula.
UNIQUE output jumps around after editing
Cause: a spilled array re-evaluates its size on every edit — adding a row to the source shifts everything below the formula down.
Keep enough blank space below UNIQUE’s home cell. If downstream formulas need a fixed layout, reference the spilled range with A2# (the spill operator) so they always target the full output.
UNIQUE vs FILTER, SORT & COUNTIF
Four ways to reshape a list. Pick based on whether you need distinct, filtered, sorted, or counted output.
| Function | Output | Spills | Typical use |
|---|---|---|---|
| UNIQUE | Distinct values (or singletons) | Yes — dynamic array | Dedupe a list, hunt outliers |
| FILTER | Rows matching a boolean test | Yes — dynamic array | Show only rows that meet a condition without modifying the source |
| SORT | Same values, reordered | Yes — dynamic array | Alphabetise or rank output. Pairs with UNIQUE as SORT(UNIQUE(...)) |
| COUNTIF | A single integer | No — scalar | Count distinct-ish values with a helper column, or detect duplicates via COUNTIF>1 |
The modern idiom is to compose them: sort a deduped list with SORT(UNIQUE(A:A)), filter before deduping with UNIQUE(FILTER(A:A, B:B="active")), or count each distinct value with COUNTIF(A:A, UNIQUE(A:A)). Dynamic arrays make these chains readable — in legacy Excel, equivalents required helper columns or Ctrl+Shift+Enter incantations.
UNIQUE frequently asked questions
6.01What does the exactly_once argument do in UNIQUE?▸
With exactly_once set to TRUE, UNIQUE returns only values that appear exactly once in the source — duplicates are dropped entirely, not just collapsed. Great for finding typos, one-off entries, or survey responses that only one person gave.
6.02Why does UNIQUE return #SPILL!?▸
#SPILL! means the output range is blocked — something is sitting in one of the cells UNIQUE needs to fill. Clear the cells below the formula or move UNIQUE to a location with enough empty rows. Merged cells also block spilling.
6.03Is UNIQUE case-sensitive?▸
No. UNIQUE treats apple and APPLE as the same value. For case-sensitive deduplication, combine UNIQUE with EXACT via an array formula, or preprocess the column with LOWER or PROPER first.
6.04Can UNIQUE sort the output?▸
No. UNIQUE preserves the order of first occurrence in the source. Wrap in SORT for an alphabetical list: =SORT(UNIQUE(A2:A100)). This is the most common Excel dynamic-array idiom after UNIQUE itself.
6.05What version of Excel has UNIQUE?▸
Excel 365 and Excel 2021 and later. Older versions (Excel 2019 and earlier) don’t support dynamic arrays at all — you’ll need the older Remove Duplicates command or a PivotTable. Google Sheets has always had UNIQUE.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.