Excel UNIQUE Function
UNIQUE Function
The UNIQUE function is a premade function in Excel, which returns a list of the different values in a range. Each value is listed only once.
For example, the Type 1 column in the table below has 20 cells, but only five different types. UNIQUE returns those five.
It is typed =UNIQUE and has the following parts:
=UNIQUE(array, [by_col], [exactly_once])
Note: UNIQUE is available in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not available in Excel 2019 or earlier. If you type it in an older version, you get a #NAME? error.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Array: The range to get the different values from.
By_col: Optional. FALSE compares rows, which is the default. TRUE compares columns, for data that goes across instead of down. You will rarely need it.
Exactly_once: Optional. FALSE returns every different value once, which is the default. TRUE returns only the values that appear exactly one time in the range.
How to use the UNIQUE function:
- Select a cell (
H2) - Type
=UNIQUE - Double click the UNIQUE command
- Mark the range to get the values from (
C2:C21) - Hit enter
Let's have a look at an example!
This is the same Pokemon table as on the XLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
Type the label Type 1 in H1.
Use the UNIQUE function to list the different Type 1 values. Type the formula in H2:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | Type 1 | ||
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | Grass | ||
| 3 | 2 | Ivysaur | Grass | Poison | 405 | Fire | ||
| 4 | 3 | Venusaur | Grass | Poison | 525 | Water | ||
| 5 | 4 | Charmander | Fire | 309 | Bug | |||
| 6 | 5 | Charmeleon | Fire | 405 | Normal | |||
| 7 | 6 | Charizard | Fire | Flying | 534 | |||
| 8 | 7 | Squirtle | Water | 314 | ||||
| 9 | 8 | Wartortle | Water | 405 | ||||
| 10 | 9 | Blastoise | Water | 530 | ||||
| 11 | 10 | Caterpie | Bug | 195 | ||||
| 12 | 11 | Metapod | Bug | 205 | ||||
| 13 | 12 | Butterfree | Bug | Flying | 395 | |||
| 14 | 13 | Weedle | Bug | Poison | 195 | |||
| 15 | 14 | Kakuna | Bug | Poison | 205 | |||
| 16 | 15 | Beedrill | Bug | Poison | 395 | |||
| 17 | 16 | Pidgey | Normal | Flying | 251 | |||
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | |||
| 19 | 18 | Pidgeot | Normal | Flying | 479 | |||
| 20 | 19 | Rattata | Normal | 253 | ||||
| 21 | 20 | Raticate | Normal | 413 |
=UNIQUE(C2:C21)
UNIQUE goes through C2:C21 from top to bottom. It keeps each type the first time it appears, and skips it after that.
The result is Grass, Fire, Water, Bug and Normal, in the order they first appear in the table. The formula is typed only in H2, and the result spills into H2:H6.
Note: The cells that the result spills into must be empty. If they are not, UNIQUE returns a #SPILL! error.
Note: UNIQUE is not case-sensitive. Fire and fire count as the same value.
Count the Unique Values
To count how many different types there are, use COUNTA on the result.
Type the label Number of types in J1, and this formula in J2:
| H | I | J | |
|---|---|---|---|
| 1 | Type 1 | Number of types | |
| 2 | Grass | 5 | |
| 3 | Fire | ||
| 4 | Water | ||
| 5 | Bug | ||
| 6 | Normal |
=COUNTA(H2#)
H2# is a spill reference. The # after the cell means "the whole result that spills from H2". Here that is H2:H6, so COUNTA returns 5.
If you mark the spill range with the mouse while you type a formula, Excel writes the # for you.
Why not just use H2:H6? If a new type is added to the table, the UNIQUE list grows by one row. H2# includes the new row by itself. A fixed range like H2:H6 would miss it.
You can also count in one cell, without the list. =COUNTA(UNIQUE(C2:C21)) also returns 5.
Values That Appear Only Once
Set exactly_once to TRUE to list only the values that appear one time in the range.
When the array has more than one column, UNIQUE compares whole rows. Use C2:D21 to look at Type 1 and Type 2 together, and find the type combinations that only one Pokemon has.
Type the labels Type 1 in L1 and Type 2 in M1, and this formula in L2:
| L | M | |
|---|---|---|
| 1 | Type 1 | Type 2 |
| 2 | Fire | Flying |
| 3 | Bug | Flying |
=UNIQUE(C2:D21, FALSE, TRUE)
Charizard is the only Pokemon that is Fire and Flying, and Butterfree is the only one that is Bug and Flying. All the other combinations are shared. Grass and Poison, for example, belong to Bulbasaur, Ivysaur and Venusaur.
FALSE is the by_col part. You must fill it in, so that TRUE lands in the exactly_once part.
Note: If no value appears exactly once, UNIQUE returns a #CALC! error. =UNIQUE(C2:C21, FALSE, TRUE) gives #CALC!, because every Type 1 appears at least three times.
Skip Empty Cells
If the range has empty cells, UNIQUE returns them as 0. Type 2 is empty for nine of the Pokemon.
Change the label in H1 to Type 2, and the formula in H2 to this one:
| H | I | J | |
|---|---|---|---|
| 1 | Type 2 | Number of types | |
| 2 | Poison | 3 | |
| 3 | 0 | ||
| 4 | Flying |
=UNIQUE(D2:D21)
All the empty cells together show up as one 0, in H3. COUNTA in J2 counts the 0 as a type, and returns 3.
To skip the empty cells, use FILTER inside UNIQUE. FILTER removes the empty cells first, and UNIQUE lists what is left:
| H | I | J | |
|---|---|---|---|
| 1 | Type 2 | Number of types | |
| 2 | Poison | 2 | |
| 3 | Flying | ||
| 4 |
=UNIQUE(FILTER(D2:D21, D2:D21<>""))
The <> sign means "not equal to", and "" is an empty text. So D2:D21<>"" keeps only the cells that are not empty.
Sort the Unique List
UNIQUE keeps the order of the table. To sort the list from A to Z, put UNIQUE inside the SORT function.
Change the label in H1 back to Type 1, and the formula in H2 to this one:
| H | I | J | |
|---|---|---|---|
| 1 | Type 1 | Number of types | |
| 2 | Bug | 5 | |
| 3 | Fire | ||
| 4 | Grass | ||
| 5 | Normal | ||
| 6 | Water |
=SORT(UNIQUE(C2:C21))
UNIQUE finds the five types, and SORT puts them in order from A to Z.
UNIQUE vs Remove Duplicates
The Remove Duplicates command deletes the duplicate rows from the data itself. UNIQUE leaves the data as it is, and makes a new list that updates by itself.
Read more on the How to Remove Duplicates page. To color duplicate or unique values instead, see the Duplicate and Unique Values rule in conditional formatting.
