Syntax
UNIQUE(array, [by_col], [exactly_once])
| Parameter | Description |
|---|---|
| array | Parameter of the UNIQUE function. |
| [by_col] | (Optional.) Parameter of the UNIQUE function. |
| [exactly_once] | (Optional.) Parameter of the UNIQUE function. |
Examples
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Unique Regions |
| 2 | East | Widget | East |
| 3 | West | Gadget | West |
| 4 | East | Widget | North |
| 5 | North | Gizmo | |
| 6 | West | Widget |
Get unique values from a column
Formula
=UNIQUE(A2:A100)
Returns: a spilled list of all distinct values in column A, with duplicates removed
Unique values that appear exactly once
Formula
=UNIQUE(A2:A100, FALSE, TRUE)
Returns: values that appear only once in the range (third argument TRUE = exactly_once)
Unique rows across multiple columns
Formula
=UNIQUE(A2:C100)
Returns: unique combinations of values across columns A, B, and C — rows are compared as a whole
Common Errors
#SPILL!
The cells where UNIQUE needs to output results are not empty. Clear the cells below and to the right of the formula.
#CALC!
The result is an empty array (no data in the source range). Ensure the source range contains data.
Tips
Dynamic dropdowns
Use =UNIQUE(A:A) as a data validation source to create dropdowns that automatically update when new values are added.
Count unique values
Wrap with COUNTA: =COUNTA(UNIQUE(A2:A100)) returns the number of distinct values.
Sort unique values
Combine with SORT: =SORT(UNIQUE(A2:A100)) returns a sorted list of distinct values.
Try UNIQUE in Viztab
Import your data and use UNIQUE with 370+ other formulas. No signup required.
Open Viztab