Dynamic arrays function

Excel UNIQUE function

Q: How do I get a list of unique values in Excel?

Type =UNIQUE(A2:A100) in an empty cell. It spills a list of each value once and updates when the data changes.

Q: How do I count unique values in Excel?

Use =COUNTA(UNIQUE(A2:A100)). UNIQUE builds the list and COUNTA counts it.

Q: How do I get unique values sorted alphabetically?

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

UNIQUE returns each value in a range once, with duplicates removed. The results spill down into the cells below. Use it to list your customers, regions, or products from a long order log.

=UNIQUE(array)

Arguments

array: The range to pull distinct values from.

UNIQUE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D
1	Order	Customer	Region	Total
2	SO-901	Harbor Cafe	West	220
3	SO-902	Pine & Co	East	140
4	SO-903	Harbor Cafe	West	95
5	SO-904	Northgate	North	310
6	SO-905	Pine & Co	East	180
7	SO-906	Ashby Deli	West	60

=UNIQUE(B2:B7)
Result: spills 4 rows × 1 column
Harbor Cafe
Pine & Co
Northgate
Ashby Deli
Spills each customer once: Harbor Cafe, Pine & Co, Northgate, Ashby Deli.
=SORT(UNIQUE(C2:C7))
Result: spills 3 rows × 1 column
East
North
West
Lists the regions once each, in A to Z order.
=COUNTA(UNIQUE(B2:B7))
Result: 4
Counts the distinct customers: 4.

How to use UNIQUE

Click the top cell of an empty area.
Type =UNIQUE( and select the column.
Press Enter. The list spills down on its own.
Wrap it in SORT if you want it in order.

Common UNIQUE mistakes

Getting #SPILL!

Something is in the way below the formula. Clear those cells so the list has room.

Expecting it to be case-sensitive

UNIQUE treats "east" and "East" as the same value.

Using it in Excel 2019 or older

UNIQUE needs Microsoft 365, Excel 2021 or later. Use Remove Duplicates on a copy in older versions.

UNIQUE FAQ

How do I get a list of unique values in Excel?

Type =UNIQUE(A2:A100) in an empty cell. It spills a list of each value once and updates when the data changes.

How do I count unique values in Excel?

Use =COUNTA(UNIQUE(A2:A100)). UNIQUE builds the list and COUNTA counts it.

How do I get unique values sorted alphabetically?

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

Practice UNIQUE in these lessons

Guides that use UNIQUE

Related functions

Updated October 31, 2026