UNIQUE
Returns a list of unique values from a range. Removes duplicates automatically.
UNIQUE(array, [by_col], [exactly_once])Arguments
arrayThe range to extract unique values fromby_colTRUE to compare columns instead of rows(optional)exactly_onceTRUE to return only values that appear exactly once(optional)
=UNIQUE(A1:A100)Returns list without duplicates
=UNIQUE(A1:C100)Returns unique row combinations
=UNIQUE(A1:A100,,TRUE)Only values that occur exactly once
- •Returns a dynamic spilling array
- •Case-sensitive for text values
- •Great for creating dropdown lists
- •Expecting UNIQUE to sort its output - it preserves the original order of first appearance, so it's often wrapped in SORT when an alphabetical or numeric order is wanted
- •Misusing the exactly_once argument - setting it to TRUE returns only values that appear exactly one time, not simply a deduplicated list, which surprises people expecting a distinct-values list
- •Applying UNIQUE to a range with inconsistent formatting of otherwise-identical values (extra spaces, mismatched case), which UNIQUE treats as genuinely different entries
Does UNIQUE sort its results?
No, it returns distinct values in their original order of first appearance. Wrap it in SORT, like SORT(UNIQUE(range)), to get an ordered list.
What does the exactly_once argument do?
Set to TRUE, it returns only values that appear exactly once in the range - not a general deduplicated list. Leave it FALSE or omitted for a standard list of distinct values.
Why are two visually identical values both showing up in my UNIQUE list?
They probably differ in a way that isn't visible, like a trailing space or different capitalization - TRIM or consistent casing before running UNIQUE usually fixes it.
Need to translate a formula using UNIQUE?
Use our translator to convert your complete formula