UNIQUE in Excel: how to use it
UNIQUE gives a list without duplicates. You type the formula in one cell and the result spills over automatically. Available in Excel 2021 and 365.
Try the first exercises for freeIn the lesson The UNIQUE function
What does UNIQUE do?
UNIQUE gives a list without duplicates: each value appears in it once. You type the formula in one cell, and the result spills over into the cells below automatically.
The list updates itself: if a new region turns up in your data, it appears in the list straight away. UNIQUE works in Excel 2021 and 365.
When do you use UNIQUE?
- A list of all the regions in your data: =UNIQUE(A2:A100).
- How many different customers there are: =COUNTA(UNIQUE(B2:B500)).
- An alphabetical list without duplicates: =SORT(UNIQUE(A2:A100)).
- The choices for a drop-down list that updates itself, such as all the product names: =UNIQUE(B2:B200).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =UNIQUE(A2:A6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
UNIQUE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | UNIQUE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array | The column or range you remove the duplicates from. | A2:A6 |
UNIQUE step by step
E1 contains =UNIQUE(A2:A6). The list starts with Leeds and spills into the cells below. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1, with empty cells below it, and type UNIQUE with a bracket.
=UNIQUE( - Select the regions, A2 to A6.
=UNIQUE(A2:A6 - Close the bracket and press Enter: each region appears once, underneath each other.
=UNIQUE(A2:A6)
More examples
Counting distinct values
E1 contains =COUNTA(UNIQUE(A2:A6)). UNIQUE gives three regions, and COUNTA counts them.
In alphabetical order
E1 contains =SORT(UNIQUE(A2:A6)). SORT puts the unique regions in alphabetical order: Bristol comes first.
Unique products
E1 contains =UNIQUE(B2:B6). Laptop, Monitor and Mouse: each product once.
Where it often goes wrong
- If there's something in the cells below, you get #SPILL!.
Practise this in the lesson The UNIQUE function. The first exercises are free.
Frequently asked questions
How does this work in older versions?
There you use Data > Remove Duplicates.