Dynamic arrays

Excel UNIQUE Function: Get a List of Distinct Values

Returns the distinct values from a range, with duplicates removed.

UNIQUE takes a range and gives you back each distinct value once. It is the formula version of Remove Duplicates, with the crucial difference that it does not touch your data — it produces a separate list that updates itself whenever the source changes.

That makes it the natural first step for building a summary. Take the region column, run UNIQUE over it to get the list of regions, then SUMIFS each one. Because both halves recalculate, a new region appearing in the data adds itself to the summary without anyone editing a formula.

It has a second, less obvious mode. The third argument switches it from 'one of each' to 'only the ones that appear exactly once', which is how you find entries with no duplicate rather than a deduplicated list.

Syntax

=UNIQUE(array, [by_col], [exactly_once])

Arguments

array Required: The range to deduplicate. Multiple columns are compared as whole rows, not column by column.
by_col Optional: FALSE (the default) compares rows. TRUE compares columns instead, for data laid out horizontally.
exactly_once Optional: FALSE (the default) returns each distinct value once. TRUE returns only values that appear exactly once in the source.

The example data

Headers in row 1, data in A2:D6.

	A	B	C	D
1	Region	Product	Status	Amount
2	North	Drill	Overdue	1240
3	South	Lead	Paid	385
4	North	Goggles	Overdue	2100
5	South	Gloves	Paid	940
6	North	Drill	Paid	156

Worked examples

=UNIQUE(A2:A6)

Result: North, South

Five rows reduced to the two distinct regions.

=UNIQUE(B2:B6)

Result: Drill, Lead, Goggles, Gloves

Drill appears twice in the source but once here.

=UNIQUE(B2:B6, FALSE, TRUE)

Result: Lead, Goggles, Gloves

The third argument set to TRUE returns only products appearing exactly once, so Drill is excluded entirely.

=SORT(UNIQUE(A2:A6))

Result: North, South

UNIQUE returns values in the order it meets them, so wrap it in SORT when you want them alphabetical.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: Pull the distinct regions out of an export

Common errors and how to fix them

#SPILL!

Why it happens: Cells below the formula already contain something.

How to fix it: Clear the block the result needs. This bites most often when a UNIQUE list grows longer than it was when you wrote it.

Near-duplicates both appear

Why it happens: UNIQUE compares exactly, so "North" and "North " with a trailing space are two different values.

How to fix it: Clean first: =UNIQUE(TRIM(A2:A6)). Import files are the usual source of this.

A blank row appears in the list

Why it happens: The range includes empty cells, which UNIQUE treats as a distinct value and returns as 0.

How to fix it: Filter them out: =UNIQUE(FILTER(A2:A100, A2:A100<>"")).

#NAME?

Why it happens: The Excel version predates dynamic arrays.

How to fix it: UNIQUE needs Microsoft 365 or Excel 2021+. Older versions need Remove Duplicates or a pivot table.

Tips worth knowing

Count distinct values with =COUNTA(UNIQUE(range)) — far clearer than the old SUMPRODUCT trick.
Feed a UNIQUE list into SUMIFS to build a summary table that grows with the data.
Pass several columns to get distinct combinations: =UNIQUE(A2:B6) treats each row as a whole.
Use the spilled range in a data validation list by referencing it with # — the dropdown then updates itself.

Frequently asked questions

What is the difference between UNIQUE and Remove Duplicates?

Remove Duplicates is a one-off action that permanently deletes rows from your data. UNIQUE is a formula that leaves the source alone and produces a separate list, recalculating whenever the data changes. Use Remove Duplicates to clean a file once, UNIQUE to build something that stays current.

How do I get a sorted unique list?

Wrap it: =SORT(UNIQUE(A2:A100)). UNIQUE returns values in the order it encounters them in the source, which is rarely the order you want to read.

Can UNIQUE work across two columns?

Yes. =UNIQUE(A2:B100) returns distinct combinations of the two columns, comparing each row as a unit rather than deduplicating the columns separately.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions