How many different products did we sell, and which ones? The manual answer is Remove Duplicates or a pivot table, and both need repeating whenever the data changes. UNIQUE returns the distinct values as a live spill: add a product to the source and it appears in the list; remove the last one and it drops out. It is the fastest way to answer “what distinct values are in this column”.
What UNIQUE does
UNIQUE takes a range and returns each distinct value once, in the order it first appears. It can also return distinct rows (whole rows that differ), or the values that appear exactly once. The result spills, so it needs empty cells below — and it updates itself whenever the source changes.
The syntax
=UNIQUE(array, [by_col], [exactly_once])
- array — the range to de-duplicate, for example B2:B20.
- by_col — FALSE compares rows (the usual case); TRUE compares columns.
- exactly_once — FALSE returns every distinct value; TRUE returns only values that appear exactly once.
Step-by-step example
List the distinct products from this order list.
- Click an empty cell where the list should start.
- Type
=UNIQUE(B2:B11)where B is the Product column. - Press Enter. Each product appears once, in first-appearance order.
- To count how many distinct products there are, wrap it:
=COUNTA(UNIQUE(B2:B11)). - For distinct combinations, pass two columns:
=UNIQUE(A2:B11)returns each unique region-and-product pair.
Worked example
| Product |
|---|
| Notebook |
| Pen set |
| Notebook |
| Stapler |
| Notebook |
| Pen set |
=UNIQUE(A2:A7) returns three values: Notebook, Pen set, Stapler. The duplicates are gone, the order is the order they first appeared, and adding a new product to the list extends the result automatically.
Common errors
#SPILL! — no room for the result. The distinct list needs empty cells below the formula. Clear the area, including cells that look empty but hold a space.
It still shows duplicates. The values differ invisibly — a trailing space or a number stored as text. Clean the source with TRIM first, or the “duplicates” are genuinely different values.
Blanks appear in the list. UNIQUE treats an empty cell as a value and lists it once. Filter blanks out first with FILTER, or accept the single blank row at the end.
Exactly-once returns nothing. With exactly_once TRUE, a value must appear exactly once in the source. If every value repeats, the result is #CALC! — add a fallback with IFERROR if that is expected.
Tip
UNIQUE plus COUNTA is the standard distinct-count pattern: =COUNTA(UNIQUE(range)). For distinct counts with a condition, filter first: =COUNTA(UNIQUE(FILTER(B2:B20,A2:A20=”East”))) counts the distinct products sold in the East. And if the list must stay in its original order with duplicates removed in place, Remove Duplicates on the Data tab is still the right tool — UNIQUE is for live lists, Remove Duplicates is for one-time cleaning.
UNIQUE rows, and values that appear exactly once
Pass a multi-column range and UNIQUE compares whole rows: =UNIQUE(A2:B11) returns each distinct region-and-product pair, which is how you answer “which combinations actually occurred” without a pivot table. With exactly_once set to TRUE, =UNIQUE(B2:B11,,TRUE) returns only the products that appear a single time — the one-off items, which is a useful audit: products ordered once might be typos, test orders, or genuinely rare items worth checking. Both modes stay live, so the audit updates as orders arrive.
Practice file
Download the UNIQUE practice file (.xlsx) — it contains an order list with a live UNIQUE formula, a distinct-count version, and a blank sheet for you to try it yourself.
Google Sheets
Google Sheets has UNIQUE with the same syntax, and it spills live results. Sheets also has COUNTUNIQUE, which counts distinct values in one step — the equivalent of COUNTA(UNIQUE(…)) without the helper.
Example prices and figures are illustrative, not market data.