Syntax, a live playground, a measured distinct-count gallery, the #SPILL! trap, exactly_once, and a sourced error library: the dynamic-array UNIQUE function.
Returns the distinct values from a range, spilled into neighboring cells. Available in Excel 365 / 2021 and Excel Online.
UNIQUE removes duplicates and spills the distinct list into neighboring cells. The live gallery uses aggregate wrappers (COUNTA, ROWS, INDEX) so each result is a single measurable value; the full spill is shown below. Every row was measured live in HyperFormula. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =COUNTA(UNIQUE(A2:A8)) | 4 | Count the distinct names |
| =ROWS(UNIQUE(A2:A8)) | 4 | Height of the spilled list |
| =COUNTA(UNIQUE(B2:B8)) | 2 | Only two distinct regions |
| =INDEX(UNIQUE(A2:A8),1) | Alice | First unique value |
| =INDEX(UNIQUE(A2:A8),2) | Bob | Second unique value |
| =INDEX(UNIQUE(A2:A8),4) | Dave | Fourth unique value |
| =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) | 4 | Pre-365 unique count (no spill) |
| =UNIQUE(A2:A8) | Alice | Top-left of the spill (Excel shows all 4) |
| Expression | Result | Why |
|---|---|---|
| A9 | Alice | first occurrence kept |
| A10 | Bob | second distinct value |
| A11 | Carol | third distinct value |
| A12 | Dave | fourth distinct value |
| Expression | Result | Why |
|---|---|---|
| =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) | 4 | works in every Excel version |
| =UNIQUE(A2:A8,,TRUE) | (only-once) | exactly_once keeps values appearing once |
Edit the table or type any formula — the HyperFormula engine evaluates it instantly. Load a preset to count distinct values, read the spill height, and use the pre-365 SUMPRODUCT method.
8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
UNIQUE requires dynamic arrays: Excel 365 / 2021, Excel Online and Google Sheets. Excel 2019 and earlier return #NAME?; use SUMPRODUCT(1/COUNTIF) there.
| Platform | Support | Notes | Gotcha |
|---|
UNIQUE is one piece. The full course walks the toolkit end to end with live, measured examples.