Excel UNIQUE Function
UNIQUE returns distinct rows or values from a range or array.
Use it to build dynamic lists of customers, categories, regions, IDs, or other values that should appear once in a summary or validation list.
UNIQUE syntax & arguments
Syntax
=UNIQUE(array, [by_col], [exactly_once])
-
1
array
RequiredThe range or array containing values to de-duplicate.
-
2
by_col
OptionalControls whether UNIQUE compares rows or columns:
FALSE— Compare rows: Returns distinct rows. This is the default.TRUE— Compare columns: Returns distinct columns.
-
3
exactly_once
OptionalControls whether repeated values are kept once or removed entirely:
FALSE— Distinct values: Returns each distinct value once, even when it appears multiple times. This is the default.TRUE— Values that appear once: Returns only values that appear exactly one time in the source array.
Example
=UNIQUE(A2:A20)
Return each distinct value from A2:A20 once.
UNIQUE caveats
UNIQUE creates dynamic arrays, so source consistency and spill behavior both matter.
-
It returns distinct values by default
Repeated values are returned once unless
exactly_onceis set to TRUE. -
Rows and columns are different modes
Use
by_colonly when the unique items should be compared across columns instead of down rows. -
Small text differences matter
Extra spaces or inconsistent labels can make values appear unique even when they should represent the same thing.
-
The result spills into nearby cells
UNIQUE may return many rows or columns. Blocked output cells can cause a
#SPILL!error.
Need the unique list ordered? Wrap UNIQUE in SORT.
Intro UNIQUE practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced UNIQUE practice problems
Use UNIQUE alongside other Excel functions in realistic, less-prescriptive challenges.
