UNIQUE function in Excel

UNIQUE returns the different values in a range, with each one listed once.

A column of data repeats the same values many times: the same customers, the same products, the same cities. UNIQUE boils it down to one of each. It is available in Excel 2021 and Microsoft 365.

Syntax

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

Argument	What it is
`array`	The range to take values from.
`by_col`	TRUE to compare columns. Leave it out to compare rows.
`exactly_once`	TRUE to return only the values that appear exactly once. FALSE or left out returns every different value once.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=UNIQUE(B2:B13)
Result:
Asha
Ravi
Meena
Kabir
The four salespeople, each listed once, in the order they first appear.
=UNIQUE(C2:C13)
Result:
North
South
East
The regions.
=SORT(UNIQUE(D2:D13))
Result:
Bag
Notebook
Pen
The products in alphabetical order.
=COUNTA(UNIQUE(B2:B13))
Result: 4
How many different salespeople there are.
=UNIQUE(C2:D13)
Result:
North Notebook
South Pen
North Bag
East Pen
South Notebook
East Notebook
North Pen
South Bag
With two columns, UNIQUE returns each different combination of region and product.
=UNIQUE(B2:B13,,TRUE)
Result: #CALC!
With the third argument TRUE, only the values that occur exactly once. Every salesperson appears more than once, so nothing is returned: #CALC!.
=UNIQUE(FILTER(D2:D13,C2:C13="North"))
Result:
Notebook
Bag
Pen
The different products sold in the North.
=TEXTJOIN(", ",TRUE,SORT(UNIQUE(C2:C13)))
Result: East, North, South
The list of regions as one piece of text.

How it works

UNIQUE reads down the range and returns each different value the first time it meets it. The result spills into the cells below the formula. The order is the order of first appearance. Wrap it in SORT for alphabetical order.

The list is live. Add a row with a new salesperson and the name appears in the list at once. The Remove Duplicates command on the Data tab, by contrast, deletes rows from your actual data and has to be run again each time.

Distinct or truly unique?

The third argument changes the question.

Left out: every different value, once each. This is what people usually mean by a unique list.
TRUE: only the values that occur exactly once in the data. Values that repeat are left out altogether.

The second form finds one-time customers, or entries with no duplicate. In the sample every salesperson has several sales, so the sixth example returns nothing and shows #CALC!.

Several columns

Give UNIQUE a range of two or more columns and it compares whole rows. =UNIQUE(C2:D13) lists each combination of region and product that occurs. Rows are treated as the same only when every column matches.

Counting different values

"How many different customers bought something?" is =COUNTA(UNIQUE(range)). If the range may contain empty cells, filter them out first, or the blank is counted as a value: =COUNTA(UNIQUE(FILTER(range, range<>""))).

Building a summary table

UNIQUE and SUMIF together make a report with two formulas.

In H2: =UNIQUE(B2:B13) lists the salespeople.
In I2: =SUMIF(B2:B13, H2#, F2:F13) gives each one's total.

H2# means "the whole list that H2 spilled", so the second formula returns a total for every name and grows when a new name arrives. The same pattern works with COUNTIF and AVERAGEIF.

A drop-down list that updates itself

Put =SORT(UNIQUE(range)) in a spare column. In Data Validation, choose List and give the source as the first cell followed by a hash, such as =$H$2#. The drop-down then always offers the current set of values.

How values are compared

Capital and small letters are treated as the same: "Asha" and "ASHA" are one value.
Spaces are not ignored. "Asha" and "Asha " are two values, and both appear in the list. If a list shows what looks like the same name twice, stray spaces are the cause. Clean the data with TRIM.
A number and the same number stored as text are different values.

Common mistakes

#SPILL! when something blocks the cells below.
#CALC! when the result is empty, as with the exactly-once option above.
#NAME? in Excel 2019 and earlier. There, a PivotTable, Remove Duplicates or the Advanced Filter with "Unique records only" does the job.
Including the heading in the range, so that it appears as a value.

In Google Sheets

UNIQUE exists in Google Sheets with the same three arguments.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.