Excel practice › Excel functions › UNIQUE
UNIQUE in Excel: how it works, with examples you can edit
UNIQUE returns each different value of a list once. To list the sales reps named in B2:B8, however many orders each one has: =UNIQUE(B2:B8).
You type the formula in one cell and the list spills into the cells below it. Add an order for a new rep and the list grows by itself. Microsoft lists UNIQUE for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
array- The range to take the values from. With several columns, UNIQUE compares whole rows.
by_col- Optional.
FALSE, or nothing, compares rows with each other, which is what a list going down the sheet needs.TRUEcompares columns. exactly_once- Optional.
FALSE, or nothing, returns every different value once.TRUEreturns only the values that occur exactly once in the data.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | State | Reps | |
| 2 | 5001 | Avery | TX | Avery | |
| 3 | 5002 | Jordan | CO | Jordan | |
| 4 | 5003 | Avery | TX | Riley | |
| 5 | 5004 | Riley | AZ | Casey | |
| 6 | 5005 | Jordan | NM | ||
| 7 | 5006 | Avery | OK | ||
| 8 | 5007 | Casey | CO |
B2:B8 to C2:C8 for the list of states.How Excel reads it
UNIQUE goes down the range and keeps a value the first time it sees it. Avery appears three times in the orders and once in the result. The values come out in the order they first appear in the data, not in alphabetical order. For that, wrap the formula in SORT: =SORT(UNIQUE(B2:B8)).
Only the first cell holds the formula. The rest of the list belongs to it, and the cells it needs have to be empty, or Excel shows #SPILL! (see the #SPILL! error).
Several columns: unique rows
Give UNIQUE two columns and it compares whole rows. A row is dropped only when both of its values repeat an earlier row. Avery sold to Texas twice, and that pair appears once. Avery and Jordan still appear twice each, because their second rows have a different state:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Rep | State | Rep | State | |
| 2 | 5001 | Avery | TX | Avery | TX | |
| 3 | 5002 | Jordan | CO | Jordan | CO | |
| 4 | 5003 | Avery | TX | Riley | AZ | |
| 5 | 5004 | Riley | AZ | Jordan | NM | |
| 6 | 5005 | Jordan | NM | Avery | OK | |
| 7 | 5006 | Avery | OK | Casey | CO | |
| 8 | 5007 | Casey | CO |
Two mistakes that give the wrong list
1. Setting the third argument to TRUE. exactly_once sounds like "show each value once". It means something else: return only the values that occur a single time in the data. Avery and Jordan have several orders, so they disappear:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | State | Reps | |
| 2 | 5001 | Avery | TX | Riley | |
| 3 | 5002 | Jordan | CO | Casey | |
| 4 | 5003 | Avery | TX | ||
| 5 | 5004 | Riley | AZ | ||
| 6 | 5005 | Jordan | NM | ||
| 7 | 5006 | Avery | OK | ||
| 8 | 5007 | Casey | CO |
TRUE to FALSE, or delete the last two arguments, and all four come back.2. Values that only look the same. UNIQUE compares the cells as they are. In this copy of the log, someone typed a space after Jordan in B6. You can't see it, but "Jordan " and "Jordan" are two different values, so the name shows up twice:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | State | Reps | |
| 2 | 5001 | Avery | TX | Avery | |
| 3 | 5002 | Jordan | CO | Jordan | |
| 4 | 5003 | Avery | TX | Riley | |
| 5 | 5004 | Riley | AZ | Jordan | |
| 6 | 5005 | Jordan | NM | Casey | |
| 7 | 5006 | Avery | OK | ||
| 8 | 5007 | Casey | CO |
=UNIQUE(TRIM(B2:B8)) returns four names.Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
Which states did the team ship to this week? In E2, write one formula that lists every state in column C once.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | State | States | |
| 2 | 5001 | Avery | TX | ||
| 3 | 5002 | Jordan | CO | ||
| 4 | 5003 | Avery | TX | ||
| 5 | 5004 | Riley | AZ | ||
| 6 | 5005 | Jordan | NM | ||
| 7 | 5006 | Avery | OK | ||
| 8 | 5007 | Casey | CO |
Show the answer
=UNIQUE(C2:C8) gives TX (5 rows, starting with TX).
UNIQUE keeps each state the first time it appears: TX and CO occur twice in the orders and once in the list.
In E2, list the reps in column B once each, in alphabetical order.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | State | Reps A-Z | |
| 2 | 5001 | Avery | TX | ||
| 3 | 5002 | Jordan | CO | ||
| 4 | 5003 | Avery | TX | ||
| 5 | 5004 | Riley | AZ | ||
| 6 | 5005 | Jordan | NM | ||
| 7 | 5006 | Avery | OK | ||
| 8 | 5007 | Casey | CO |
Show the answer
=SORT(UNIQUE(B2:B8)) gives Avery (4 rows, starting with Avery).
UNIQUE runs first and returns the four names. SORT then puts that list in ascending order, which for text is A to Z.
How many different states are in column C? In E2, return the number with one formula: remove the duplicates, then count what is left.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | State | States | |
| 2 | 5001 | Avery | TX | ||
| 3 | 5002 | Jordan | CO | ||
| 4 | 5003 | Avery | TX | ||
| 5 | 5004 | Riley | AZ | ||
| 6 | 5005 | Jordan | NM | ||
| 7 | 5006 | Avery | OK | ||
| 8 | 5007 | Casey | CO |
Show the answer
=COUNTA(UNIQUE(C2:C8)) gives 5.
UNIQUE returns the five different states, and COUNTA counts the items of that list. The list never has to appear on the sheet.
Questions people ask
How do I count unique values in Excel?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(C2:C8)) removes the duplicates and counts what is left. On the orders above it returns 5. Use it on a range with no empty cells.
What is the difference between unique and distinct values in Excel?
Distinct values are every different value, each listed once: that is what =UNIQUE(B2:B8) returns. Values that are unique in the strict sense occur a single time in the data: that is =UNIQUE(B2:B8,FALSE,TRUE), with the third argument, exactly_once, set to TRUE.
How do I get a unique list sorted alphabetically?
Put SORT around UNIQUE: =SORT(UNIQUE(B2:B8)). UNIQUE alone returns the values in the order they first appear. See SORT for sorting largest first or by another column.
Does UNIQUE work in Excel 2019 or 2016?
Microsoft lists UNIQUE for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.