Google Sheets UNIQUE: Distinct Values Fast
By Szabó Gergő · Updated
UNIQUE spills distinct rows from a range. It is the standard way to feed dropdowns and summary QUERY sources in Google Sheets.
Syntax and arguments
=UNIQUE(range, [by_column], [exactly_once])- range
- The values that contain duplicates.
- by_column
- TRUE compares columns instead of rows.
- exactly_once
- TRUE keeps values that appear only once.
UNIQUE examples
01
Dropdown source
Customers repeat in A2:A.
=SORT(UNIQUE(A2:A))Point data validation at the spill range for a live list.
02
Unique pairs
City and owner in A2:B.
=UNIQUE(A2:B)A row is unique when both city and owner together are unique.
03
Appears once
Order IDs in C2:C.
=UNIQUE(C2:C, FALSE, TRUE)IDs entered twice drop out of the result.
Common mistakes
Including the header in an open range
Start at A2 or wrap with FILTER to drop the header label.
Blanks counting as a unique item
=UNIQUE(FILTER(A2:A, A2:A<>"")).
Case and spaces
TRIM the source. Sheets UNIQUE is not a substitute for data cleaning.
UNIQUE FAQ
Is Sheets UNIQUE like Excel UNIQUE?
Yes in spirit. Argument names differ slightly (by_column versus by_col).
Can I UNIQUE across files?
UNIQUE(IMPORTRANGE(...)) works after access is granted.
How do I count distinct values?
=COUNTA(UNIQUE(FILTER(A2:A,A2:A<>""))).