The UNIQUE Function in Excel

The Excel UNIQUE function gives you each different value in a range once, so a column of East, West, East, South becomes East, West, South. You type it in one cell and the list spills into the cells below, and it updates whenever the data changes. This guide is for anyone who needs a clean list of customers, regions or products for a report or a job test. UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365.

What the Excel UNIQUE function does#

UNIQUE reads a range and returns the distinct values, in the order they first appear.

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

• array is the range to read, such as C2:C9. Leave out the header row.
• by_col is optional. FALSE (the default) compares rows, which is what you want for a list that runs down a column. TRUE compares columns, for a list that runs across a row.
• exactly_once is optional. FALSE (the default) returns every distinct value. TRUE returns only values that appear exactly once.

If your values run across a row instead, such as region names in B1:H1, use =UNIQUE(B1:H1,TRUE). The result then spills to the right instead of down.

UNIQUE is a dynamic array function: one formula returns several results, and they spill into the cells below. The spill needs empty cells to land in. If something is in the way, you see #SPILL! instead; our guide to the #SPILL! error explains every cause.

How to list unique values#

Here is an order list where customers and regions repeat:

In F2, type:

=UNIQUE(C2:C9)

The result spills into F2:F5: East, West, South, North. That is the order each region first appears in the data, not A to Z. If you change C8 from North to West, North disappears from the list straight away.

The same formula on the customers, =UNIQUE(B2:B9), returns five names: Acme Corp, Brightline, Delta Freight, Cobalt Labs, Evergreen Co.

How to count unique values#

"How many different customers do we have?" is a common report and interview question. Put UNIQUE inside COUNTA, which counts the items in a list:

=COUNTA(UNIQUE(B2:B9))

The result is 5. Use COUNTA, not COUNT: COUNT only counts numbers, so it returns 0 for a list of names. Our guide to COUNT vs COUNTA vs COUNTBLANK explains the difference.

If the list is already on the sheet, you can count it with a spill reference: the first cell of the spill followed by #. With the regions spilled from F2, =COUNTA(F2#) returns 4. Unlike a fixed range such as F2:F5, F2# grows and shrinks with the list, so the count stays right when a new region appears.

To count distinct values that meet a condition, filter first: =COUNTA(UNIQUE(FILTER(B2:B9,C2:C9="East"))) returns 2, the number of different East customers.

How to find values that appear only once#

Set exactly_once to TRUE to keep only the values that appear one time. The middle argument stays FALSE, because the list runs down a column:

=UNIQUE(B2:B9,FALSE,TRUE)

This returns Delta Freight, Cobalt Labs, Evergreen Co: the customers who ordered once. Acme Corp (three orders) and Brightline (two) are left out. To count them, wrap it in COUNTA: =COUNTA(UNIQUE(B2:B9,FALSE,TRUE)) returns 3.

This is different from the default. Without TRUE, UNIQUE removes the repeats but keeps one copy of every value. With TRUE, it removes every value that repeats.

How to get unique combinations from several columns#

Give UNIQUE more than one column and it compares whole rows. This lists each customer and region pair once:

=UNIQUE(B2:C9)

It returns six rows. Acme Corp appears twice, once with East and once with North, because those are different combinations. The two "Acme Corp, East" rows and the two "Brightline, West" rows each collapse into one.

How to sort, filter and summarise unique values#

UNIQUE works well inside other dynamic array functions.

Sort the list A to Z#

=SORT(UNIQUE(C2:C9))

This returns East, North, South, West. UNIQUE removes the repeats, then SORT puts them in order. Our guide to SORT and SORTBY covers sorting by other columns and from largest to smallest.

Unique values that meet a condition#

To list only the customers in the East region, filter first, then remove the repeats:

=UNIQUE(FILTER(B2:B9,C2:C9="East"))

This returns Acme Corp, Cobalt Labs. FILTER keeps the East rows (three orders), and UNIQUE reduces them to two customers. The FILTER function guide and our article on the FILTER function with AND and OR explain the conditions.

A summary table next to the list#

With the regions spilled in F2:F5, put a SUMIFS beside the first one and fill it down to row 5:

=SUMIFS($D$2:$D$9,$C$2:$C$9,F2)

You get the total per region: 8,280 for East, 4,250 for West, 8,900 for South and 1,500 for North. It works like a small pivot table that updates with the data.

Common problems with UNIQUE#

Most UNIQUE problems come from the spill or from the data itself:

1. #SPILL! instead of a list. A cell below the formula isn't empty. Clear it, or move the formula to a free column.
2. A 0 in the list. The range includes an empty cell, and UNIQUE returns it as 0. Leave blanks out with =UNIQUE(FILTER(C2:C20,C2:C20<>"")).
3. Values that look the same but aren't. "East" and "East " (with a space) are different values. Clean the data with TRIM first. Capitals don't matter: UNIQUE treats "east" and "East" as the same.
4. Including the header. =UNIQUE(C1:C9) adds "Region" to the list. Start the range on row 2.
5. #NAME? in older Excel. Excel 2019 and earlier don't have UNIQUE. For those files, use Data > Remove Duplicates on a copy of the data instead; see UNIQUE vs Remove Duplicates.
6. A fixed range that misses new rows. C2:C9 won't include an order added in row 10. Make the range longer than you need and filter out blanks, or turn the data into an Excel table.

Practise it#

Try the exercise on this page: one short formula turns eight orders into a list of four regions. Then practise UNIQUE in a live spreadsheet, where you list customers, count them with COUNTA and find the ones who ordered only once.