Google Sheets UNIQUE: Extract Unique Distinct Values & Deduplicate
UNIQUE extracts distinct values from a column or table, stripping duplicates automatically. When source records change, unique list recalculates in real time.
=UNIQUE(range, [by_column], [exactly_once])Technical Overview & Engine Mechanics
Formula Comparison: UNIQUE vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| range | Range | Required | The input range to deduplicate. |
| by_column | Boolean | Optional | FALSE = Filter by rows (default), TRUE = Filter by columns. |
| exactly_once | Boolean | Optional | FALSE = Return distinct items (default), TRUE = Return only items appearing exactly once. |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Extract Unique Customer List from Orders Table
=SORT(UNIQUE(Orders!B2:B))Example 2: Isolate Multi-Column Unique Key Combinations
=UNIQUE(A2:C500)Example 3: Find Truly Unique One-Time Buyers
=UNIQUE(Orders!B2:B, FALSE, TRUE)Example 4: Deduplicated Customer List Sorted Alphabetically
=SORT(UNIQUE(Customers!B2:B5000))Example 5: Multi-Column Distinct Product and Variant Roster
=UNIQUE(Orders!C2:E1000)Common Errors & Solutions
Performance Pro Tips
- Feed output of UNIQUE into Data Validation dropdown lists to provide dynamic auto-updating choices.
Frequently Asked Questions: UNIQUE
Is UNIQUE case-sensitive in Google Sheets?
Yes. Google Sheets UNIQUE treats 'John' and 'john' as distinct. Use =UNIQUE(ARRAYFORMULA(PROPER(range))) to unify case.
Can UNIQUE extract only values that appear exactly once (no duplicates at all)?
Yes. Set the third argument (exactly_once) to TRUE: =UNIQUE(A2:A100, FALSE, TRUE).
How do I count how many unique values exist?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A100)).
Can UNIQUE deduplicate horizontally across columns?
Yes. Set the second argument (by_column) to TRUE: =UNIQUE(A1:Z1, TRUE).
Does UNIQUE support filtered data?
Yes. Wrap FILTER inside UNIQUE: =UNIQUE(FILTER(A2:A100, B2:B = "Active")).
Related Google Sheets Formulas
QUERY
The QUERY function is Google Sheets' most versatile formula, executing a SQL-like Google Visualization API Query Language string across spreadsheet tables to filter, aggregate, group, pivot, and sort data in a single formula.
Data TransformationARRAYFORMULA
ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are entered, ARRAYFORMULA calculates them instantly without requiring manual formula dragging.
Data TransformationFILTER
FILTER dynamically outputs only the rows or columns from a source range that satisfy one or more logical conditions. As source data updates, filtered results re-calculate automatically.