UNIQUE Function in Google Sheets
Returns the distinct rows from a range, removing duplicates. Used for deduplication, finding distinct categories, or building lookup tables from raw data.
Syntax
UNIQUE(range, [by_column], [exactly_once])Returns: A range containing distinct rows from the input.
Excel equivalent: UNIQUE (identical, requires Excel 2021+ or Microsoft 365)
Parameters
| Name | Required | Description |
|---|---|---|
| range | Required | The range to deduplicate. |
| by_column | Optional | TRUE to compare by column (find unique columns), FALSE (default) to compare by row. |
| exactly_once | Optional | TRUE to return only rows that appear exactly once in source. Default FALSE returns one of each distinct row. |
Examples
List of distinct categories
=UNIQUE(A2:A1000)Returns each unique value from column A, one per row, in the order it first appears.
Unique customer-product pairs
=UNIQUE(A2:B1000)Returns distinct combinations of columns A and B. Both columns must match for a row to count as duplicate.
Sorted unique list
=SORT(UNIQUE(A2:A1000))Distinct values, sorted alphabetically. The classic combo for building a clean reference list.
When to use an alternative
- QUERY with GROUP BY — You want unique values WITH aggregates (counts, sums) for each.
- COUNTUNIQUE — You only want the count of distinct values, not the list.
- Remove duplicates menu action — You want to mutate the original range, not produce a separate result.
Common errors and how to fix them
Case-sensitive surprise
Cause: UNIQUE is case-insensitive by default in newer Sheets versions but historically wasn't — "apple" and "Apple" deduplicate to one entry.
Fix: If you need case-sensitive uniqueness, wrap values: UNIQUE(ARRAYFORMULA(EXACT(...))) — but usually case-insensitive is what you want.
Spaces treated as different
Cause: "Apple" and "Apple " (trailing space) count as different.
Fix: Wrap with TRIM: UNIQUE(ARRAYFORMULA(TRIM(A2:A1000))).
Related functions
Frequently Asked Questions
How does UNIQUE order the output?
By order of first appearance in the source range, NOT alphabetically. To get sorted output, wrap with SORT: SORT(UNIQUE(A2:A1000)).
What does the exactly_once argument do?
When TRUE, UNIQUE returns only rows that appear EXACTLY ONCE in the source — useful for finding one-off entries. When FALSE (default), it returns one of each distinct row. The default is what most people want.