What UNIQUE Does
UNIQUE returns a list of distinct values from a range, removing all duplicates. It works on single columns, multiple columns, or entire row sets. The result updates automatically as your data changes, making it perfect for building dynamic dropdown lists, summary tables, and reports.
Syntax
=UNIQUE(range)
| Parameter | Description |
|---|---|
| range | The range of cells from which to extract unique values. Can be a single column, multiple columns, or a formula that returns an array. |
Basic Examples
Example 1: Get Unique Values from a Column
| A | |
|---|---|
| 1 | Category |
| 2 | Electronics |
| 3 | Clothing |
| 4 | Electronics |
| 5 | Food |
| 6 | Clothing |
=UNIQUE(A2:A6)
Result:
| Electronics |
| Clothing |
| Food |
Example 2: Unique Rows Across Multiple Columns
When your range spans multiple columns, UNIQUE removes duplicate rows (where all columns match).
| A | B | |
|---|---|---|
| 2 | East | Widget |
| 3 | East | Gadget |
| 4 | East | Widget |
=UNIQUE(A2:B4)
Result: 2 rows — "East, Widget" and "East, Gadget." The duplicate row 4 is removed.
Example 3: Count of Unique Values
To count how many unique categories exist:
=COUNTA(UNIQUE(A2:A100))
Advanced Examples
Sorted Unique List
Create a clean, alphabetically sorted list of departments:
=SORT(UNIQUE(C2:C500))
This is useful for building data validation dropdown lists that stay up to date.
Unique Values with a Filter
Get unique customer names from only the "Active" rows:
=UNIQUE(FILTER(A2:A200, C2:C200="Active"))
FILTER first narrows the data to active customers, then UNIQUE removes duplicates. The result is a clean list of distinct active customers.
Common Mistakes
- Expecting UNIQUE to be case-sensitive. UNIQUE in Google Sheets is case-insensitive by default. "apple" and "Apple" are treated as the same value, and the first occurrence is kept. There is no built-in parameter to change this.
- Not leaving room for results. UNIQUE spills its output downward (and rightward for multi-column ranges). If any cell in the output area contains data, you get a #REF! error. Clear the cells below the formula.
- Using UNIQUE on formatted numbers. If some cells are formatted as text and others as numbers, UNIQUE may treat "100" (text) and 100 (number) as different values. Ensure consistent formatting in your source data.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "list all unique categories from column A" — and the formula is generated for you. Install SheetAI to try it free.