UNIQUE in Google Sheets
UNIQUE returns each distinct value in a range once, removing duplicates: =UNIQUE(B2:B100) lists every customer who appears in an order log. It is a dynamic array: one formula fills as many rows as needed and updates when the data changes.
Combine it with other functions: =SORT(UNIQUE(B2:B)) gives an alphabetical list, and =COUNTA(UNIQUE(B2:B100)) counts distinct values. With several columns, UNIQUE removes rows that are duplicated across all of them.
Syntax
=UNIQUE(range)
Example
=SORT(UNIQUE(B2:B100))
A sorted list of distinct customers.
Common mistakes
- Blocking the output with values below the formula (#REF!).
- "Ana" and "Ana " are treated as different values: clean with TRIM first.
- Counting with COUNT instead of COUNTA on text results.
← SPLIT · SORT → · All lessons