Data Transformation FormulaGoogle Sheets V8 Engine Verified

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.

Formula Syntax:=UNIQUE(range, [by_column], [exactly_once])

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
rangeRangeRequiredThe input range to deduplicate.
by_columnBooleanOptionalFALSE = Filter by rows (default), TRUE = Filter by columns.
exactly_onceBooleanOptionalFALSE = 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))
How it works: Produces an alphabetically sorted list of distinct customer names.

Example 2: Isolate Multi-Column Unique Key Combinations

=UNIQUE(A2:C500)
How it works: Preserves rows where the combination of Name, Department, and Role is unique.

Example 3: Find Truly Unique One-Time Buyers

=UNIQUE(Orders!B2:B, FALSE, TRUE)
How it works: Returns only customers who purchased exactly once.

Common Errors & Solutions

❌ Trailing whitespace causing false uniqueness
Resolution: Wrap range in TRIM(): =UNIQUE(TRIM(A2:A)).

Performance Pro Tips

  • Feed output of UNIQUE into Data Validation dropdown lists to provide dynamic auto-updating choices.

Frequently Asked Questions

Does UNIQUE recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas