On this page ⌄
- The UNIQUE syntax
- A basic UNIQUE example
- Unique values that meet a condition
- Sort unique values with SORT
- How to count unique values
- Count how many times each value appears
- Distinct vs unique: the exactly_once argument
- UNIQUE with multiple columns
- Compare columns instead of rows with by_col
- Why UNIQUE returns 0 for blank cells
- How to fix the #SPILL! error
- Common UNIQUE mistakes
- Frequently asked questions about UNIQUE
- Which versions of Excel have UNIQUE?
- 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: SKU | B: Product | C: Category | D: Price | E: Stock | |
|---|---|---|---|---|---|
| 2 | P-01 | Desk lamp | Lighting | $39 | 120 |
| 3 | P-02 | Office chair | Furniture | $189 | 14 |
| 4 | P-03 | Standing desk | Furniture | $449 | 0 |
| 5 | P-04 | Monitor arm | Accessories | $79 | 56 |
| 6 | P-05 | Floor lamp | Lighting | $89 | 0 |
| 7 | P-06 | Bookshelf | Furniture | $129 | 31 |
| 8 | P-07 | Cable tray | Accessories | $25 | 210 |
| 9 | P-08 | Desk mat | Accessories | $19 | 0 |
Each formula below goes in H2, with the cells under and beside it empty, so the results have room to spill.
The UNIQUE syntax
| Argument | What it means | Example |
|---|---|---|
| array | The cells to remove repeats from: one column, several columns or a row | E2:E9 |
| by_col | Optional. FALSE, the default, compares rows. TRUE compares columns, for data that runs across the sheet | TRUE |
| exactly_once | Optional. FALSE, the default, lists every value once. TRUE lists only the values that appear exactly once | TRUE |
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, 210There 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.
List each category once.
Write your formula in the green cell, H2.
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, AccessoriesFILTER 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"))→ NoneSort 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, LightingFor 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, 0If 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)))→ 2When 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))→ 0FILTER 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))→ 3COUNTIF 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| H | I | J | |
|---|---|---|---|
| 2 | Accessories | 3 | 266 |
| 3 | Furniture | 3 | 45 |
| 4 | Lighting | 2 | 120 |
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))→ 50 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)→ LightingFILTER 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")→ NoneUNIQUE 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))→ 8Lighting 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")))| H | I | |
|---|---|---|
| 2 | Lighting | In stock |
| 3 | Furniture | In stock |
| 4 | Furniture | Out of stock |
| 5 | Accessories | In stock |
| 6 | Lighting | Out of stock |
| 7 | Accessories | Out 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#)→ 12COUNTA 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, 0To 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, 0The 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, 0Every 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 matWhen 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
| Problem | What's going on | Fix |
|---|---|---|
#SPILL! | The cells the list needs aren't empty, or the formula is in a table | Clear the cells, or move the formula |
#NAME? | Excel 2019 or older, or a typo in UNIQUE | Use Microsoft 365 or Excel 2021 or later |
#CALC! | exactly_once found no value that appears once, or a FILTER inside found no rows | Wrap UNIQUE in IFERROR, or give FILTER an if_empty |
| A 0 at the end of the list | The range includes empty cells | Use FILTER(range, range<>"") inside UNIQUE |
| Repeats still in the list | Extra spaces, or numbers stored as text in some cells | Clean the data, or use TRIM inside UNIQUE |
| Every row comes back | With several columns, a row is only a repeat when all its columns match | Give UNIQUE only the columns that should match |
| by_col returns the column unchanged | by_col compares columns, and a single column has nothing to compare with | Leave by_col out for data in a column |
| COUNTA gives 1 when nothing matches | COUNTA counts the #CALC! error, or the "" from if_empty | Use the SUM and LEN formula above |
| The list isn't in order | UNIQUE keeps the order in which values first appear | Wrap 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 matDoes 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.
- 1Unique valuesFreeList each category once.→
- 2Count unique values PremiumHow many different categories are there? Use one formula that still works when new categories are added.→
- 3Sort by price PremiumList all product names sorted by price, most expensive first.→
- 4Filter a listFreeList every product in the Furniture category.→
- 5Top 3 with TAKE PremiumList the 3 most expensive products, most expensive first.→

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.