UNIQUE in Google Sheets
Use UNIQUE to return a list with duplicates removed — a clean set of the distinct values in a column or range.
=UNIQUE(A2:A)
Returns each distinct value from A2:A once, in the order it first appears. The result spills down automatically.
How it works
UNIQUE takes a range and returns only the first occurrence of each row, dropping exact duplicates. With a single column you get a clean distinct list; with several columns it keeps rows that are unique as a whole combination. It spills, so leave the cells below empty. To also sort the result, wrap it in SORT.
Variations
Unique and sorted A-Z
=SORT(UNIQUE(A2:A))
SORT orders the distinct list alphabetically or numerically.
Unique combinations across columns
=UNIQUE(A2:B)
Keeps each distinct pair of values, not just distinct single cells.
Count how many of each value
=ARRAYFORMULA(QUERY(A2:A, "select A, count(A) group by A"))
QUERY gives a distinct list plus a count per value.
Ignore blank cells
=UNIQUE(FILTER(A2:A, A2:A<>""))
FILTER strips empty cells before UNIQUE runs.
Examples
| Scenario | Formula |
|---|---|
| List of distinct customers | =UNIQUE(Orders!B2:B) |
| Distinct product + size pairs | =UNIQUE(A2:B) |
| Sorted distinct categories | =SORT(UNIQUE(C2:C)) |
FAQ
Does UNIQUE sort the results?
No. It keeps values in the order they first appear. Wrap it in SORT, e.g. SORT(UNIQUE(A2:A)), if you want them ordered.
Why is a blank showing in my unique list?
Empty cells in the range count as a distinct value. Filter them out first with UNIQUE(FILTER(A2:A, A2:A<>"")).
What's the difference between UNIQUE and Remove duplicates?
UNIQUE is a live formula that updates as data changes and leaves the source intact; Data > Remove duplicates edits the original range once.