Dynamic arrays
UNIQUE function playground
Returns the distinct values in a range, removing duplicates.
The playground runs in your browser. Nothing you type is sent anywhere.
How UNIQUE works
UNIQUE goes down the list and keeps each value the first time it appears, so the order of first appearance is kept. Upper and lower case count as the same.
With several columns, a row is a duplicate only if every column matches. COUNTA(UNIQUE(range)) counts how many different values there are.
Syntax
=UNIQUE(array, [by_col], [exactly_once])| Argument | What it means |
|---|---|
arrayrequired | The range to look at. |
by_coloptional | FALSE (default) compares rows; TRUE compares columns. |
exactly_onceoptional | TRUE returns only values that appear exactly once. |
Examples
| Formula | What it does | Result |
|---|---|---|
=UNIQUE(B2:B11) | Each region once. | East (spills 3×1) |
=UNIQUE(C2:D11) | Each rep and product combination once. | Ana (spills 8×2) |
=COUNTA(UNIQUE(C2:C11)) | How many different reps there are. | 4 |
=SORT(UNIQUE(C2:C11)) | Distinct reps, sorted. | Ana (spills 4×1) |
Common mistakes
Extra spaces"East" and "East " are different values. Clean the data with TRIM first.
#SPILL!The list needs empty cells below it.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.