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])
πŸ’‘ Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

The UNIQUE function scans an array or range and returns distinct rows with all duplicate records removed. Operating as a core dynamic array utility, UNIQUE inspects multi-column records in their entirety; a row is only considered duplicate if every value across all selected columns matches an earlier row. UNIQUE can also return values that occur exactly once when configured with its optional flags.

Formula Comparison: UNIQUE vs. Alternatives

Unlike the static 'Data > Data cleanup > Remove duplicates' UI menu which permanently destroys source rows, the UNIQUE formula is non-destructive and dynamic. As new records are added, the deduplicated list refreshes immediately. Pairing UNIQUE with SORT or FILTER creates automated summary rosters and dropdown validation lists.

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.

Example 4: Deduplicated Customer List Sorted Alphabetically

=SORT(UNIQUE(Customers!B2:B5000))
How it works: Extracts distinct customer company names from a raw lead log and sorts them A-Z.

Example 5: Multi-Column Distinct Product and Variant Roster

=UNIQUE(Orders!C2:E1000)
How it works: Extracts unique combinations of Category, SKU, and Color from thousands of individual order rows.

Common Errors & Solutions

❌ Trailing whitespace causing false uniqueness
Resolution: Wrap range in TRIM(): =UNIQUE(TRIM(A2:A)).
❌ #REF! Spilling blocked by existing cell data
Resolution: Clear any manual entries below and to the right of the UNIQUE formula to allow the array to expand.
❌ Case sensitivity surprises in deduplication
Resolution: UNIQUE in Google Sheets is case-sensitive: 'APPLE' and 'apple' are treated as distinct values. Wrap in ARRAYFORMULA(UPPER(range)) for case-insensitive deduplication.

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