What UNIQUE does
UNIQUE extracts distinct values from a list or table without needing helper columns or manual deduplication. Because it is a dynamic array function, the result expands automatically as the source data changes.
Practical examples
Return a unique list of regions
=UNIQUE(B2:B20)This is useful for dropdown sources, summaries, or quick deduplication of a single column.
Return values that appear exactly once
=UNIQUE(B2:B20,FALSE,TRUE)With exactly_once set to TRUE, values that appear more than once are excluded from the result.
Common mistakes and notes
UNIQUE spills into neighboring cells
If the spill range is blocked, Excel returns #SPILL!. Clear the cells where the dynamic array needs to expand.
Hidden spaces create false duplicates
Values that look identical can still differ because of trailing spaces or nonprinting characters. Clean the data first if the unique list looks wrong.
exactly_once is stricter than distinct values
The default behavior returns one copy of each distinct value. With exactly_once set to TRUE, repeated values disappear entirely.