How to Use UNIQUE in Google Sheets: Comprehensive Guide

UNIQUE returns the distinct rows of a range, removing duplicates. It is the quickest way to build a list of categories, customers or tags from a column that repeats them, and it updates automatically as the data changes.

Syntax

=UNIQUE(range, [by_column], [exactly_once])

range: The data to deduplicate. With several columns, a row counts as a duplicate only if all its columns match another row.
by_column [Optional]: FALSE (default) compares rows. TRUE compares columns instead and removes duplicate columns.
exactly_once [Optional]: FALSE (default) keeps one copy of every distinct item. TRUE returns only the items that appear exactly once, dropping anything that was repeated.

Basic examples

With a column of departments in B2:B100 (Sales, IT, Sales, HR, IT, ...):

=UNIQUE(B2:B100)

Returns Sales, IT, HR: each department once, in the order it first appears.

With several columns, rows are compared as a whole. This returns each distinct Department and Region combination:

=UNIQUE(B2:C100)

Items that appear only once

Set the third argument to TRUE to find the loners:

=UNIQUE(A2:A100, FALSE, TRUE)

If the column holds a, b, a, c, this returns b and c, because a is repeated. Handy for spotting one-off entries, such as customers who ordered only once.

Common patterns

Sorted list of distinct values

UNIQUE keeps the order of first appearance. Wrap it in SORT to alphabetize:

=SORT(UNIQUE(B2:B100))

Remove blanks from the result

UNIQUE treats an empty cell as a value and returns a blank. Filter them out first:

=UNIQUE(FILTER(B2:B100, B2:B100 <> ""))

Count the distinct values

=COUNTA(UNIQUE(B2:B100))

Or, if blanks may be present: =COUNTA(UNIQUE(FILTER(B2:B100, B2:B100 <> ""))).

Sum by category

Combine with SUMIF to build a summary table: put UNIQUE in one column and use the results as the criteria:

=ARRAYFORMULA(SUMIF(B2:B100, D2:D4, C2:C100))

Here D2:D4 holds the output of a UNIQUE. QUERY can do the grouping and the sum in one step if you prefer.

Dropdown list from live data

Use the UNIQUE output as the source for a data validation dropdown. Create the list on a helper sheet with =SORT(UNIQUE(Data!B2:B)), then point the validation at that range. New categories appear in the dropdown automatically.

Deduplicate across several columns

To dedupe on a key made of two columns while still returning other columns, use QUERY or FILTER with a counter, since UNIQUE compares the entire row.

Case sensitivity and whitespace

UNIQUE is not case-sensitive, so Apple and apple are the same item (the first one seen is returned). It is sensitive to spaces: "Apple" and "Apple " are different. To dedupe loosely, clean the data first:

=UNIQUE(ARRAYFORMULA(TRIM(LOWER(A2:A100))))

Common errors

#REF!: Array result was not expanded. The cells under the formula are not empty. Clear them.
Unexpected extra items. Usually trailing spaces, non-breaking spaces or text-versus-number differences ("1" vs 1). Apply TRIM or VALUE.
A blank row in the output. The source range includes empty cells. Filter them out as shown above.
Headers treated as data. Start the range below the header row, or the header counts as a value.

UNIQUE vs. other ways to remove duplicates

The menu command Data > Data cleanup > Remove duplicates deletes rows permanently from the sheet. UNIQUE leaves the source untouched and gives you a live, formula-driven copy. Use the menu when you want to clean the data once, and UNIQUE when you want the list to follow the data.

Frequently asked questions

Does UNIQUE keep the original order? Yes, it returns items in the order they first appear. Use SORT for alphabetical order.

Can UNIQUE work across multiple columns? Yes, a row counts as duplicate only when every column matches.

How do I find duplicates instead of removing them? Use =UNIQUE(A2:A100) and compare counts, or use =FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1) to list the values that are repeated.

Related Functions

SORT: Sorts the rows of a range.
FILTER: Returns the rows that meet a condition.
QUERY: Group and aggregate data with SQL-like syntax.
COUNTIF: Counts cells that meet a condition.
ARRAYFORMULA: Applies a formula to a whole range.

UNIQUE