Google Sheets / 5 min read

Google Sheets UNIQUE Function Examples

Use UNIQUE in Google Sheets to build distinct lists, remove repeated values, and prepare clean dropdown or report inputs.

UNIQUE returns distinct rows or values from a range. Clean spaces and hidden characters first if values that look identical still appear more than once.

Check this first

Check whether repeated-looking values contain extra spaces.
Use TRIM before UNIQUE when imported labels are messy.
Leave room below the formula for the unique list to expand.
Decide whether you need unique rows or unique values from one column.

Working examples

Return a unique list from one column

=UNIQUE(A2:A)

Trim values before creating a unique list

=UNIQUE(ARRAYFORMULA(TRIM(A2:A)))

Best uses for UNIQUE

UNIQUE is useful when you need a clean list of categories, names, tags, vendors, or statuses from a larger table. The output can feed dropdowns, summaries, charts, and quick audits.

It is also a safer alternative to deleting duplicates when you want to preserve the raw source data.

Why duplicates sometimes remain

If two values look the same but UNIQUE keeps both, the cells are probably not truly identical. Extra spaces, non-printing characters, or inconsistent capitalization often explain the difference.

Use TRIM for extra spaces.
Use CLEAN for hidden characters.
Use LOWER or UPPER if capitalization should not matter.

A clean reporting workflow

Create the unique list in a helper area, then use COUNTIF, SUMIF, or FILTER against that list. This gives you a simple reporting base without editing the raw table.