Function guides · Lookup
UNIQUE function
Returns the distinct values in a range, removing duplicates.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
| Argument | What it is |
|---|---|
array | The range to take values from. |
by_col | Optional. TRUE compares columns instead of rows. Almost always omitted. |
exactly_once | Optional. TRUE returns only values that appear exactly once. |
What UNIQUE does
UNIQUE returns each different value in a range once. Duplicates are dropped and the list appears in the order the values first occur.
It spills the result into the cells below the formula, so it needs room. The common uses are building a list of categories, as the source for a dropdown, and counting distinct items.
The optional third argument flips the meaning. With TRUE, it returns only the values that occur exactly once, which is a way to find the odd ones out. It requires Microsoft 365 or Excel 2021.
A classic use is the source for a dropdown. Put UNIQUE in a spare column, then point a data validation list at the first cell followed by the hash sign, which means “the whole spill range”. The dropdown then grows and shrinks as the data changes.
Combine it with the counting functions for summary tables. A column of customers from UNIQUE, with SUMIFS beside it, gives a sales total per customer with no manual list to maintain. Add SORT around the UNIQUE to put the customers in alphabetical order.
UNIQUE examples
1. A list of the regions
| A | B | C | |
|---|---|---|---|
| 1 | Region | ||
| 2 | West | West | |
| 3 | East | East | |
| 4 | West | North | |
| 5 | North | ||
| 6 | East | ||
| 7 | West |
The column holds six entries with repeats. UNIQUE keeps the first occurrence of each value, in order of appearance, and spills the list down.
2. How many distinct regions
| A | B | C | |
|---|---|---|---|
| 1 | Region | ||
| 2 | West | 3 | |
| 3 | East | ||
| 4 | West | ||
| 5 | North | ||
| 6 | East | ||
| 7 | West |
Wrap UNIQUE in ROWS to count the distinct values. There are three regions in six rows. If blank cells could be in the range, filter them out first with FILTER, because a blank counts as one value.
3. Values that appear only once
| A | B | C | |
|---|---|---|---|
| 1 | Region | ||
| 2 | West | North | |
| 3 | East | ||
| 4 | West | ||
| 5 | North | ||
| 6 | East | ||
| 7 | West |
Setting the third argument to TRUE returns only the entries with no duplicates. North appears once, so it is the only one listed.
Common UNIQUE mistakes
- #SPILL! Anything in the cells where the list should appear blocks it. Clear them.
- Blank cells in the range. A blank counts as a value and returns 0 in the list. Use FILTER to remove blanks first.
- Case sensitivity. UNIQUE ignores case, so West and WEST are one value. Spaces still count, so "West " is different.
- Counting the blank as a value. ROWS(UNIQUE(range)) and COUNTA(UNIQUE(range)) both count a blank as one distinct value. Filter blanks out first:
ROWS(UNIQUE(FILTER(A2:A20,A2:A20<>""))). - Using it in older Excel. UNIQUE is in Microsoft 365 and Excel 2021. Excel 2019 does not have it. Use Remove Duplicates or an Advanced Filter.
UNIQUE on Excel job tests
Prompts such as "list each customer once", "how many different products were sold" or "which items appear only once" call for UNIQUE. If the workbook must open in an older Excel, use Data > Remove Duplicates instead, which changes the data itself.
The counting version is ROWS(UNIQUE(range)). With a condition: ROWS(UNIQUE(FILTER(B2:B50,A2:A50="West"))).
Check for near-duplicates before trusting a count. "Acme" and "Acme " are two values, so run TRIM on the column first if the task allows it.
Related functions
UNIQUE questions
- What does UNIQUE do?
- It returns each different value in a range once, in the order it first appears.
- How do I count distinct values?
=ROWS(UNIQUE(A2:A100)). If the range has blanks, filter them out first:=ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))).- How do I list values that appear only once?
- Set the third argument to TRUE:
=UNIQUE(A2:A100,FALSE,TRUE). - How do I remove duplicates in older Excel?
- Use Data > Remove Duplicates, which changes the data itself.
Practice UNIQUE
The lesson UNIQUE: distinct lists and one-timers (module 19 of Analyst) has checked UNIQUE exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample