Google Sheets Unique List for Dropdowns
By Szabó Gergő · Updated
UNIQUE spills distinct values. SORT makes the dropdown readable. FILTER drops blanks so the list stays clean.
Syntax and arguments
=SORT(UNIQUE(FILTER(A2:A, A2:A<>"")))- range
- The column that contains repeats.
Unique dropdown examples
01
Sorted customers
Names repeat in A2:A.
=SORT(UNIQUE(FILTER(A2:A,A2:A<>"")))Point data validation at this spill, not at column A.
02
Unique pairs
City and owner in A2:B.
=SORT(UNIQUE(A2:B))A row is unique when both columns together are unique.
03
Appears once only
Need orphans.
=UNIQUE(A2:A, FALSE, TRUE)exactly_once keeps values that occur a single time.
Common mistakes
Validating against the raw column
The dropdown would include duplicates and blanks. Validate against the UNIQUE spill.
Putting values under UNIQUE
Clear the output column or you get #REF!.
Case variants as different items
UNIQUE is case-sensitive. Normalize with UPPER first if needed.
Unique dropdown FAQ
Can I UNIQUE another file?
UNIQUE(IMPORTRANGE(...)) works after access is granted.
How do I count distinct?
=COUNTA(UNIQUE(FILTER(A2:A,A2:A<>""))).
Does Excel do this?
Excel 365 UNIQUE is similar. Older Excel needs Advanced Filter or a PivotTable.