How UNIQUE works
UNIQUE walks down the range you give it, keeps the first occurrence of each value, and drops every later duplicate. The result spills into the cells below the formula in original order, not alphabetical order. Wrap it in SORT if you want the alphabetised version.
When the array has multiple columns, UNIQUE compares whole rows. Two rows are duplicates only if every column matches. This is how you get distinct combinations, like every (department, role) pair that actually appears in the roster.
The third argument flips the meaning. exactly_once=TRUE returns only items that appear exactly one time in the source - singletons. It removes everything that has any duplicate at all, which is different from a normal distinct list.
Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| array | range | ✓ Required | The range to pull distinct values from. Can be one column, one row, or a 2D block. Multi-column input compares whole rows for uniqueness. |
| by_col | boolean | ✗ Optional | FALSE (default) treats each row as one item and dedupes vertically. TRUE compares columns instead and dedupes horizontally. Almost always left as default. |
| exactly_once | boolean | ✗ Optional | FALSE (default) returns each distinct value once - the standard distinct list. TRUE returns only values that appear exactly one time in the source, dropping everything with any duplicate. |
UNIQUE basic: pull every department once
Department column has ten rows with plenty of repeats. UNIQUE walks the column, keeps the first time it sees each value, and spills the distinct list. No sorting, no pivot table, no Remove Duplicates dialog.
=UNIQUE(A2:A11)- •
A2:A11-> the column to deduplicate (ten department rows) - •
Spill-> the distinct list fills as many rows as there are distinct values - •
Order-> preserves source order - first occurrence wins, no alphabetising - •
Live-> add a new department in the source and the spill updates instantly
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Distinct depts | ||
| 2 | Sales | Sales | ||
| 3 | Marketing | Marketing | ||
| 4 | Sales | Engineering | ||
| 5 | Engineering | HR | ||
| 6 | HR | Operations | ||
| 7 | Marketing | |||
| 8 | Sales | |||
| 9 | Operations | |||
| 10 | Engineering | |||
| 11 | Marketing |
UNIQUE on two columns: distinct dept + role pairs
Pass a 2D range and UNIQUE compares whole rows. Two rows are duplicates only when every column matches, so this returns every (department, role) combination that exists in the roster - even if the same role name exists across multiple departments.
=UNIQUE(A2:B11)- •
A2:B11-> two-column range: department + role - •
Whole-row compare-> rows are duplicates only when both Dept AND Role match - •
Spill width-> result spills two columns wide, matching the input shape - •
Order-> preserves source order again - first appearance of each combo
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Role | Department | Role | ||
| 2 | Sales | Manager | Sales | Manager | ||
| 3 | Marketing | Lead | Marketing | Lead | ||
| 4 | Sales | Manager | Engineering | Senior | ||
| 5 | Engineering | Senior | HR | Lead | ||
| 6 | HR | Lead | Sales | Rep | ||
| 7 | Marketing | Lead | Operations | Manager | ||
| 8 | Sales | Rep | Marketing | Senior | ||
| 9 | Operations | Manager | ||||
| 10 | Engineering | Senior | ||||
| 11 | Marketing | Senior |
UNIQUE with exactly_once: pull singletons only
Set the third argument to TRUE and UNIQUE flips its meaning. Instead of returning each distinct value once, it returns only values that appear exactly one time in the source. Anything with any duplicate gets dropped entirely - not just deduplicated, removed.
Useful for finding orphan records: customers with one order, employees in a department of one, products that sold a single unit.
=UNIQUE(A2:A11, FALSE, TRUE)- •
A2:A11-> same ten department rows as Example A - •
FALSE-> by_col stays at default - dedupe by row, not column - •
TRUE-> exactly_once flips the rule: keep only singletons - •
Result-> two values - HR and Operations - the only departments that appear exactly once
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Singletons only | ||
| 2 | Sales | HR | ||
| 3 | Marketing | Operations | ||
| 4 | Sales | |||
| 5 | Engineering | |||
| 6 | HR | |||
| 7 | Marketing | |||
| 8 | Sales | |||
| 9 | Operations | |||
| 10 | Engineering | |||
| 11 | Marketing |
UNIQUE + COUNTIF: distinct list with frequency
The classic one-two punch. UNIQUE spills the distinct list down one column, COUNTIF tallies how many times each distinct value appears in the source. Result is a frequency table without a single pivot table involved.
=COUNTIF($A$2:$A$11, C2#)- •
C2 (separate cell)-> holds =UNIQUE(A2:A11) and spills the five distinct departments down - •
$A$2:$A$11-> the source range to count against - locked with $ so it doesn't drift - •
C2#-> the # spill operator: 'all cells in C2's spill range' - one COUNTIF formula handles every distinct value - •
Spill chain-> D2 spills down with one count per distinct dept, automatically resizing if Department list changes
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Department | Department | Count | ||
| 2 | Sales | Sales | 3 | ||
| 3 | Marketing | Marketing | 3 | ||
| 4 | Sales | Engineering | 2 | ||
| 5 | Engineering | HR | 1 | ||
| 6 | HR | Operations | 1 | ||
| 7 | Marketing | ||||
| 8 | Sales | ||||
| 9 | Operations | ||||
| 10 | Engineering | ||||
| 11 | Marketing |
SORT(UNIQUE(...)): distinct AND alphabetised
UNIQUE preserves source order, which is rarely what dashboards want. Wrap it in SORT and you get a clean alphabetised distinct list in one formula. The two functions compose naturally - SORT receives UNIQUE's spill as its array argument.
=SORT(UNIQUE(A2:A11))- •
UNIQUE(A2:A11)-> inner spill: the five distinct departments in source order - •
SORT(...)-> outer wrap: re-orders the distinct list alphabetically (default) - •
One formula-> no helper column, no manual sort step, recalculates live - •
Pattern-> the same shape works with FILTER inside: SORT(UNIQUE(FILTER(...)))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Sorted distinct | ||
| 2 | Sales | Engineering | ||
| 3 | Marketing | HR | ||
| 4 | Sales | Marketing | ||
| 5 | Engineering | Operations | ||
| 6 | HR | Sales | ||
| 7 | Marketing | |||
| 8 | Sales | |||
| 9 | Operations | |||
| 10 | Engineering | |||
| 11 | Marketing |
Where people go wrong
- Confusing exactly_once=TRUE with a plain distinct list
Easy to read exactly_once and assume it means 'each value once in the output'. It actually means 'only values that appear exactly once in the source'. A name that repeats gets stripped entirely, not collapsed.
Fix: Leave the third argument off (or FALSE) for a normal distinct list. Use TRUE only when you specifically want to find singletons - records that have no duplicates anywhere in the source. - by_col=TRUE when you wanted row deduplication
The second argument flips the orientation: TRUE compares columns instead of rows. Pass it by accident and a 10-row list returns nothing useful, or the function dedupes a horizontal range you weren't trying to touch.
Fix: Almost always leave by_col at its default (FALSE or omitted). Only set TRUE when your data is actually laid out left-to-right and you want the columns deduplicated. - Spill blocked by content below
UNIQUE expands downward into a spill range. If any cell underneath your formula already has data, the formula returns #SPILL! and refuses to overwrite the existing content.
Fix: Clear the cells below the formula or move it somewhere with empty space. The spill range is a contiguous block from the formula's cell down (and right, for multi-column input). - Trailing whitespace makes UNIQUE think two strings are different
A row with "Sales" and another with "Sales " (trailing space) read as identical to a human and as two distinct values to UNIQUE. Same goes for hidden non-breaking spaces and trailing tabs from data exports.
Fix: Wrap the input in TRIM to strip surrounding whitespace before deduping: =UNIQUE(TRIM(A2:A11)). For hidden non-breaking spaces, add CLEAN: =UNIQUE(TRIM(CLEAN(A2:A11))).
Notes
- UNIQUE is a dynamic-array function. The result spills into adjacent cells - you can't put other content in the spill range without breaking it.
- Available in Excel 365 and Excel 2021. Older versions don't have it; share files cautiously.
- Comparison is case-insensitive: 'Sales', 'SALES', and 'sales' collapse to one entry. Use EXACT inside a helper if you need case-sensitive deduplication.
- Multi-column input compares whole rows. Two rows are duplicates only when every column matches.
- Pair with SORT for alphabetised distinct lists, FILTER for subset-then-dedupe, and COUNTIF for distinct-with-frequency tables.
- There is no if_empty argument like FILTER has. If the source might be empty, wrap the call in IFERROR or pre-filter the input.
- Trailing spaces, non-breaking spaces, and case differences all change what UNIQUE counts as distinct. Wrap in TRIM and CLEAN if your data came from a CSV or web export.
Now prove it
Reading about UNIQUE is one thing.
Building a live dashboard cell that spills the distinct customer list, counts each one, and stays in sync as the team appends new orders is completely different.
These exercises put UNIQUE in real workplace patterns: dedupe + count, distinct combos, singleton hunts, sorted reference lists.
Here's the thing about UNIQUE.
You can read this page twice and still hesitate when the team asks for a distinct, sorted, frequency-counted list of every product SKU that shipped last quarter.
That gap, between knowing what UNIQUE does and being able to compose it with SORT, FILTER, and COUNTIF fluently, is exactly what CellSkill is built to close.
Not with more reading.
With practice on scenarios that look like your actual job.
Start practicing UNIQUE for free →