How do I count unique values?

Use =COUNTA(UNIQUE(range)), or =SUM(1/COUNTIF(range,range)) in older Excel.

Can UNIQUE sort the list?

Wrap it in SORT: =SORT(UNIQUE(A2:A7)).

Does UNIQUE work on several columns?

Yes. It returns unique rows when the array has multiple columns.

Is UNIQUE case sensitive?

No. "east" and "East" are the same value.

← All functions

Dynamic arrays

UNIQUE

List without duplicates.

Syntax

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

Example: =UNIQUE(B2:B100)

Available in: Excel 2021+, Microsoft 365

UNIQUE returns the distinct values from a range and spills them into the cells below: =UNIQUE(array, [by_col], [exactly_once]). It is the fast way to get a list of categories, customers or regions without duplicates.

When to use it

You need a clean list of categories to drive a dropdown or summary table.
You want to count how many different customers, products or regions you have.
You want only values that appear exactly once.
You are replacing the Remove Duplicates button with something that stays live.

Arguments explained

array. The range or array to remove duplicates from. With several columns, a row counts as a duplicate only when all its values match.
by_col. Optional. FALSE or omitted compares rows, which is the normal case. TRUE compares columns.
exactly_once. Optional. FALSE or omitted returns each distinct value once. TRUE returns only values that appear a single time in the data.
Text matching. Comparison ignores upper and lower case, but extra spaces count as a difference, so clean the data with TRIM first.

Worked examples

Distinct regions

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =UNIQUE(A2:A7)

Result: Spills down: East, West, North

Keeps the first occurrence of each region, in order of appearance.

Count of distinct regions

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =COUNTA(UNIQUE(A2:A7))

Result: 3

COUNTA counts the items in the unique list.

Values that appear once

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =UNIQUE(B2:B7,,TRUE)

Result: Spills down: Ben, Cara, Dan, Eli

Setting exactly_once to TRUE drops Ana, who appears twice.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =UNIQUE(A2:A7)

Example spreadsheet for UNIQUE
	A	B	C	D	E	F
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		East
3	West	Pen	80	2.5		West
4	East	Book	45	12		North
5	North	Book	60	12
6	West	Lamp	30	25
7	East	Lamp	15	25

Result in F2: East

Practice this in a lesson: UNIQUE and SORT

Common errors and how to fix them

#SPILL!. Cells below the formula are not empty. Clear them or move the formula.
Duplicates still appear. Values that look equal differ by extra spaces. UNIQUE ignores upper and lower case but not spaces, so clean the data with TRIM first.
#NAME?. UNIQUE is available in Excel 2021 and Microsoft 365 only.
Blank shows as 0. An empty cell inside the range is returned as 0. Filter blanks out first.

Related functions

Frequently asked questions

How do I count unique values?: Use =COUNTA(UNIQUE(range)), or =SUM(1/COUNTIF(range,range)) in older Excel.
Can UNIQUE sort the list?: Wrap it in SORT: =SORT(UNIQUE(A2:A7)).
Does UNIQUE work on several columns?: Yes. It returns unique rows when the array has multiple columns.
Is UNIQUE case sensitive?: No. "east" and "East" are the same value.