{ }Dynamic arrays14 min read ·

How to Use the UNIQUE Function in Excel

Learn the Excel UNIQUE function with real examples. List distinct values, count unique values, use several columns, exactly_once, SORT, FILTER and #SPILL!

Quick answer

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

UNIQUE returns each value in a range once, in the order it first appears, and spills the list into the cells below, like =UNIQUE(A2:A10). Wrap it in SORT to sort the list, or in COUNTA to count the unique values.

Arguments in [brackets] are optional.

On this page ⌄
  1. The UNIQUE syntax
  2. A basic UNIQUE example
  3. Unique values that meet a condition
  4. Sort unique values with SORT
  5. How to count unique values
  6. Count how many times each value appears
  7. Distinct vs unique: the exactly_once argument
  8. UNIQUE with multiple columns
  9. Compare columns instead of rows with by_col
  10. Why UNIQUE returns 0 for blank cells
  11. How to fix the #SPILL! error
  12. Common UNIQUE mistakes
  13. Frequently asked questions about UNIQUE
  14. Which versions of Excel have UNIQUE?
  15. Key takeaways

UNIQUE returns each value in a range once. You give it a range with repeats, and it spills a list of the distinct values into the cells below, in the order they first appear. When the data changes, the list updates by itself:

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

It does the same job as Remove Duplicates on the Data tab, except your data stays as it is, and the result is a live list you can sort, count or use in other formulas. UNIQUE is available in Microsoft 365, Excel 2021 and later, and Excel for the web.

The examples use this inventory in A1:E9:

A: SKUB: ProductC: CategoryD: PriceE: Stock
2P-01Desk lampLighting$39120
3P-02Office chairFurniture$18914
4P-03Standing deskFurniture$4490
5P-04Monitor armAccessories$7956
6P-05Floor lampLighting$890
7P-06BookshelfFurniture$12931
8P-07Cable trayAccessories$25210
9P-08Desk matAccessories$190

Each formula below goes in H2, with the cells under and beside it empty, so the results have room to spill.

The UNIQUE syntax

ArgumentWhat it meansExample
arrayThe cells to remove repeats from: one column, several columns or a rowE2:E9
by_colOptional. FALSE, the default, compares rows. TRUE compares columns, for data that runs across the sheetTRUE
exactly_onceOptional. FALSE, the default, lists every value once. TRUE lists only the values that appear exactly onceTRUE

Most of the time you only need the first argument. To set exactly_once without by_col, leave by_col empty between two commas, as in =UNIQUE(E2:E9, , TRUE). You can type 1 and 0 instead of TRUE and FALSE.

A basic UNIQUE example

Which stock levels appear in the inventory?

=UNIQUE(E2:E9)→ 120, 14, 0, 56, 31, 210

There are eight products but only six stock levels, because three products have 0 in stock: Standing desk, Floor lamp and Desk mat. UNIQUE keeps the first 0, from row 4, and drops the repeats in rows 6 and 9. You type the formula once, in H2, and the list spills into H2:H7.

UNIQUE doesn't sort. Each value goes where it first shows up in the data, which is why 0 comes third. To put the list in order, wrap it in SORT, as shown below.

The list is live. Change Floor lamp's stock in E6 from 0 to 5, and the list becomes 120, 14, 0, 56, 5, 31, 210 without you touching the formula, because row 6 now holds a value that hasn't appeared before.

Your turn. The sheet below is live and uses the same inventory. This time the exercise asks for text instead of numbers: find the column it needs, press Enter and watch the list spill.

Try it yourself

A live spreadsheet. Type a formula and press Enter to check it.

inventory.xlsx
fx
1SKUProductCategoryPriceStock
2P-01Desk lampLighting$39.00120Categories
3P-02Office chairFurniture$189.0014
4P-03Standing deskFurniture$449.000
5P-04Monitor armAccessories$79.0056
6P-05Floor lampLighting$89.000
7P-06BookshelfFurniture$129.0031
8P-07Cable trayAccessories$25.00210
9P-08Desk matAccessories$19.000
10
11

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

Unique values that meet a condition

UNIQUE has no condition argument of its own. To list the unique values from only some rows, pick the rows with FILTER first and give the result to UNIQUE. Which categories have a product under $100?

=FILTER(C2:C9, D2:D9<100)→ Lighting, Accessories, Lighting, Accessories, Accessories
=UNIQUE(FILTER(C2:C9, D2:D9<100))→ Lighting, Accessories

FILTER returns the category of all five products under $100, repeats included, and UNIQUE trims that to one of each. Furniture is missing because the cheapest piece of furniture, the Bookshelf, costs $129. The FILTER guide covers the test in detail, including several conditions with AND and OR.

If no row passes the test, FILTER returns #CALC! and UNIQUE passes it on. Nothing costs more than $500, so give FILTER an if_empty message to show instead:

=UNIQUE(FILTER(C2:C9, D2:D9>500, "None"))→ None

Sort unique values with SORT

UNIQUE keeps the order of the data. Wrap it in SORT to sort the list from smallest to largest, or from A to Z:

=SORT(UNIQUE(E2:E9))→ 0, 14, 31, 56, 120, 210
=SORT(UNIQUE(FILTER(C2:C9, D2:D9<100)))→ Accessories, Lighting

For largest first, or Z to A, give SORT -1 as its third argument. Leave the second argument, sort_index, empty for a single column:

=SORT(UNIQUE(E2:E9), , -1)→ 210, 120, 56, 31, 14, 0

If you only want the list to spill across a row instead of down a column, wrap it in TRANSPOSE. =TRANSPOSE(UNIQUE(E2:E9)) puts the same six stock levels in H2:M2.

How to count unique values

To count the unique values instead of listing them, wrap UNIQUE in COUNTA, which counts the items in the list. How many different stock levels are there?

=COUNTA(UNIQUE(E2:E9))→ 6

=ROWS(UNIQUE(E2:E9)) returns 6 as well. ROWS counts the rows in the list and COUNTA counts the values, and for a single column that's the same thing. The difference shows up with several columns, covered below.

Give UNIQUE a FILTER to count the unique values that meet a condition. How many categories have a product under $100?

=COUNTA(UNIQUE(FILTER(C2:C9, D2:D9<100)))→ 2

When nothing passes the test, this returns 1, not 0. Nothing costs more than $500, yet:

=COUNTA(UNIQUE(FILTER(C2:C9, D2:D9>500)))→ 1
=SUM(--(LEN(UNIQUE(FILTER(C2:C9, D2:D9>500, "")))>0))→ 0

FILTER returns #CALC!, and COUNTA counts the error as a value. Adding "" as if_empty doesn't fix it, because COUNTA counts empty text too. The second formula counts only the results that have some length. LEN gives 0 for the empty text, >0 turns each length into TRUE or FALSE, and -- turns those into 1s and 0s for SUM to add up. With the under-$100 test, it returns 2, just like COUNTA.

Excel 2019 and older don't have UNIQUE. There, the classic formula for counting unique values is:

=SUMPRODUCT(1/COUNTIF(C2:C9, C2:C9))→ 3

COUNTIF counts how often each row's category appears: 2 for the Lighting rows, and 3 for the Furniture and Accessories rows. Dividing 1 by those counts gives each Lighting row a half and every other row a third, so each category adds up to exactly 1, and SUMPRODUCT adds up the categories. It returns #DIV/0! if the range has a blank cell and gets slow on long lists, so use COUNTA and UNIQUE when you can. The COUNTIF guide explains how COUNTIF works.

Count how many times each value appears

A UNIQUE list makes a quick summary table. Say H2 holds the categories sorted A to Z, made by wrapping the exercise's formula in SORT. Then one formula in I2 counts the products in each category, and one in J2 adds up their stock:

=COUNTIF(C2:C9, H2#)→ 3, 3, 2
=SUMIFS(E2:E9, C2:C9, H2#)→ 266, 45, 120
HIJ
2Accessories3266
3Furniture345
4Lighting2120

H2# means the whole list that spills from H2, so COUNTIF and SUMIFS give one answer per category and spill beside it. When a category is added or removed, all three columns update together. The SUMIF and SUMIFS guide has more on totals like these.

If you'd rather type =COUNTIF($C$2:$C$9, H2) in I2 and copy it down, keep the dollar signs so the range stays put while H2 moves. The absolute vs relative references guide explains why.

Distinct vs unique: the exactly_once argument

By default, UNIQUE returns distinct values: every value, listed once. Set exactly_once to TRUE and it returns only the values that appear exactly once, which is what "unique" means in the strict sense:

=UNIQUE(E2:E9, , TRUE)→ 120, 14, 56, 31, 210
=COUNTA(UNIQUE(E2:E9, , TRUE))→ 5

0 is left out, because three products have it. Every other stock level belongs to just one product.

This answers questions like "which category has only one product in stock?":

=UNIQUE(FILTER(C2:C9, E2:E9>0), , TRUE)→ Lighting

FILTER returns the categories of the five products in stock: Lighting, Furniture, Accessories, Furniture, Accessories. Furniture and Accessories appear twice, so only Lighting is left.

If every value repeats, there's nothing to return, and UNIQUE shows #CALC!. All three products over $100 are furniture:

=UNIQUE(FILTER(C2:C9, D2:D9>100), , TRUE)→ #CALC!
=IFERROR(UNIQUE(FILTER(C2:C9, D2:D9>100), , TRUE), "None")→ None

UNIQUE has no if_empty argument like FILTER's, so IFERROR is the way to show a message instead.

UNIQUE with multiple columns

Give UNIQUE more than one column and it compares whole rows. A row is only dropped when every one of its columns matches an earlier row. In this table, no two rows match on both category and price, so UNIQUE returns all eight rows:

=ROWS(UNIQUE(C2:D9))→ 8

Lighting appears twice, for example, but at $39 and $89, so both rows stay.

To list unique combinations, give UNIQUE only the columns that should match. Which mixes of category and stock status are there? IF labels each product In stock or Out of stock, HSTACK puts the labels beside the categories, and UNIQUE drops the repeats:

=UNIQUE(HSTACK(C2:C9, IF(E2:E9>0, "In stock", "Out of stock")))
HI
2LightingIn stock
3FurnitureIn stock
4FurnitureOut of stock
5AccessoriesIn stock
6LightingOut of stock
7AccessoriesOut of stock

Eight products give six combinations. Bookshelf repeats Office chair's Furniture and In stock, and Cable tray repeats Monitor arm's Accessories and In stock.

HSTACK needs Microsoft 365, Excel 2024 or Excel for the web. In Excel 2021, CHOOSE({1,2}, C2:C9, IF(E2:E9>0, "In stock", "Out of stock")) builds the same two columns. When the columns you want sit next to each other in the data, skip both and give UNIQUE the range directly.

To count the combinations, use ROWS, not COUNTA. With the list spilling from H2:

=ROWS(H2#)→ 6
=COUNTA(H2#)→ 12

COUNTA counts every filled cell, six rows times two columns. ROWS counts rows, so it gives the number of unique combinations.

For a single list of the distinct values found anywhere in several columns, rather than combinations, stack the columns into one first with TOCOL, as in =UNIQUE(TOCOL(range)). TOCOL has the same version needs as HSTACK.

Compare columns instead of rows with by_col

UNIQUE compares rows unless you tell it otherwise. by_col is for data that runs across the sheet, with one value per column. A common mistake is to set it for an ordinary column of data. There's only one column to compare, so nothing is removed:

=UNIQUE(E2:E9, TRUE)→ 120, 14, 0, 56, 0, 31, 210, 0

To see by_col at work, turn the stock column on its side with TRANSPOSE, as if the stock levels were typed across a row. Now each value is a column of its own, and by_col compares them:

=UNIQUE(TRANSPOSE(E2:E9), TRUE)→ 120, 14, 0, 56, 31, 210
=UNIQUE(TRANSPOSE(E2:E9))→ 120, 14, 0, 56, 0, 31, 210, 0

The first spills across H2:M2. The second leaves by_col out, so UNIQUE compares the single row with itself and returns all eight values unchanged. For data in a row, set by_col to TRUE. For data in a column, leave it out.

Why UNIQUE returns 0 for blank cells

UNIQUE treats an empty cell as a value. If the range includes empty cells, the list gets one 0 for them, in the spot where the first empty cell appears. That often happens when the range runs past the data to leave room for new rows. Here B2:B12 covers the eight products plus three empty rows:

=UNIQUE(B2:B12)→ Desk lamp, Office chair, Standing desk, Monitor arm, Floor lamp, Bookshelf, Cable tray, Desk mat, 0

Every product name is different, so all eight come back, plus a 0 for the empty cells. That 0 is part of the list, so =COUNTA(UNIQUE(B2:B12)) returns 9 when there are only eight products. To leave the blanks out, filter them first:

=UNIQUE(FILTER(B2:B12, B2:B12<>""))→ Desk lamp, Office chair, Standing desk, Monitor arm, Floor lamp, Bookshelf, Cable tray, Desk mat

When you can, a tidier fix is to format the data as an Excel table, so the range grows with the data and never includes empty rows. The FAQ below shows how.

How to fix the #SPILL! error

UNIQUE needs an empty cell for every result. The list of stock levels needs H2:H7, so with anything typed in H5:

=UNIQUE(E2:E9)→ #SPILL!

Click the formula cell and Excel draws a dashed border around the cells it wants to use. Clear H5 and the list appears. A UNIQUE list gets longer when new values show up, so leave room below it, or a cell that's out of the way today can block the list tomorrow.

Spilled formulas also don't work inside an Excel table, and merged cells in the way cause #SPILL! too. The FILTER guide covers the same error, and Microsoft's page on dynamic array formulas and spilled array behavior explains spilling in more detail.

Common UNIQUE mistakes

ProblemWhat's going onFix
#SPILL!The cells the list needs aren't empty, or the formula is in a tableClear the cells, or move the formula
#NAME?Excel 2019 or older, or a typo in UNIQUEUse Microsoft 365 or Excel 2021 or later
#CALC!exactly_once found no value that appears once, or a FILTER inside found no rowsWrap UNIQUE in IFERROR, or give FILTER an if_empty
A 0 at the end of the listThe range includes empty cellsUse FILTER(range, range<>"") inside UNIQUE
Repeats still in the listExtra spaces, or numbers stored as text in some cellsClean the data, or use TRIM inside UNIQUE
Every row comes backWith several columns, a row is only a repeat when all its columns matchGive UNIQUE only the columns that should match
by_col returns the column unchangedby_col compares columns, and a single column has nothing to compare withLeave by_col out for data in a column
COUNTA gives 1 when nothing matchesCOUNTA counts the #CALC! error, or the "" from if_emptyUse the SUM and LEN formula above
The list isn't in orderUNIQUE keeps the order in which values first appearWrap it in SORT

Frequently asked questions about UNIQUE

Is UNIQUE case-sensitive?

No. UNIQUE treats lighting and Lighting as the same value, and lists whichever comes first in the data. There's no argument to change that. Extra spaces do count, though: "Lighting " with a space at the end is a different value from "Lighting". Clean the data with TRIM, or put TRIM inside the formula, as in =UNIQUE(TRIM(range)).

What's the difference between UNIQUE and Remove Duplicates?

Remove Duplicates, on the Data tab, deletes the repeated rows from your data. It's a one-off: add more rows later and you have to run it again. UNIQUE leaves the data alone and builds a separate list that keeps itself up to date. Use Remove Duplicates to clean up a list once, and UNIQUE when you need a list that stays current. Remove Duplicates also works in older versions of Excel.

How do I make a drop-down list of unique values?

Put the list in a cell first, such as the sorted categories in H2. Then select the cell for the drop-down, open Data > Data Validation, choose List, and type =$H$2# as the source. The # makes the drop-down use the whole spilled list, so it grows when a new category appears.

Then point XLOOKUP or FILTER at the drop-down cell. With the drop-down in G1 set to Accessories, XLOOKUP returns the first accessory and FILTER returns all of them:

=XLOOKUP(G1, C2:C9, B2:B9)→ Monitor arm
=FILTER(B2:B9, C2:C9=G1)→ Monitor arm, Cable tray, Desk mat

Does UNIQUE pick up new rows?

Only if its range covers them. If you add a product in row 10, E2:E9 misses it. Format the data as an Excel table with Ctrl+T, and use the table's column names so the range grows with the data. If the table is named Inventory:

=UNIQUE(Inventory[Stock])

Put the formula outside the table, because spilled formulas can't go inside one.

Which versions of Excel have UNIQUE?

UNIQUE works in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web, on Windows and Mac. It isn't in Excel 2019 or older, where it returns #NAME?. In those versions, use Remove Duplicates, or Advanced Filter with Unique records only ticked, to make a list of distinct values. A PivotTable lists and counts each value, and the SUMPRODUCT and COUNTIF formula above counts them.

Google Sheets has a UNIQUE function too. For Microsoft's own examples, see the UNIQUE function page.

For more practice, the dynamic arrays topic has a free FILTER exercise on the same inventory, plus Premium exercises on counting unique values, sorting by price and the top three with TAKE.

Key takeaways

  • =UNIQUE(array, [by_col], [exactly_once]) lists each value once, in the order it first appears, and spills the list into the cells below.
  • Wrap UNIQUE in SORT to sort the list and in COUNTA to count it, and give it a FILTER to list or count the unique values that meet a condition.
  • Set exactly_once to TRUE for values that appear only once. With several columns, UNIQUE compares whole rows, so count the combinations with ROWS.
  • Filter out blank cells to avoid a stray 0, leave room below the list to avoid #SPILL!, and use Microsoft 365, Excel 2021 or later, or Excel for the web.

Practice

Keep going with UNIQUE exercises

Reading gets you started. Solving a few of these is what makes it stick.

  1. 1Unique valuesFreeList each category once.→
  2. 2Count unique values PremiumHow many different categories are there? Use one formula that still works when new categories are added.→
  3. 3Sort by price PremiumList all product names sorted by price, most expensive first.→
  4. 4Filter a listFreeList every product in the Furniture category.→
  5. 5Top 3 with TAKE PremiumList the 3 most expensive products, most expensive first.→
All dynamic arrays exercises →
Brian Millot

About the author

Brian Millot

Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.

Related guides

Practice makes formulas

Stop reading. Start typing formulas.

Every exercise is a real spreadsheet with instant feedback. The first two in every topic are free, no sign-up needed.