UNIQUE returns a list of distinct values from a range or array, removing duplicates automatically. It can also return values that appear exactly once, and it works by row or by column.
=UNIQUE(array, [by_col], [exactly_once])What does UNIQUE do?
UNIQUE is a dynamic array function that takes a range of values and returns only the distinct ones. You write one formula, and Excel spills the result into as many cells as needed. It removes duplicates without sorting, filtering, or copy-paste, and the result updates automatically when the source data changes.
Duplicate values cause real problems: totals get double-counted, dropdown lists become messy, and reports look unprofessional. UNIQUE solves this in one step. It is available in Excel for Microsoft 365, Excel 2021, and Excel for the web. In older versions, you would need advanced array formulas or manual work.
A typical situation: you have a sales table with hundreds of rows and need a clean list of regions for a summary report or data validation dropdown. Instead of scrolling and copying, you point UNIQUE at the region column and get a tidy list that stays current.
When to use UNIQUE
- Create a clean list of categories, regions, or product names from raw data.
- Build dynamic dropdown lists for data validation.
- Count distinct values by combining UNIQUE with COUNTA.
- Find values that appear only once in a list.
- Remove duplicate rows from a combined dataset before analysis.
- Feed unique values into other functions such as SORT or FILTER.
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once])| Argument | Required? | What to enter |
|---|---|---|
array | Required | The range or array from which to extract unique values. Example: A2:A10. |
by_col | Optional | Set to TRUE to compare columns and return unique columns; FALSE or omitted compares rows. Example: FALSE. |
exactly_once | Optional | Set to TRUE to return only values that appear exactly once; FALSE or omitted returns all distinct values. Example: TRUE. |
What it returns: A dynamic array of unique values. The result spills into neighbouring cells and updates automatically when the source data changes.
UNIQUE examples
1 Example 1: Unique product list from a single column
The goal is to get a clean list of product names from column A. The formula =UNIQUE(A2:A7) looks at cells A2 through A7 and removes duplicates. It returns each distinct product once, in the order it first appears. The result spills down from the target cell D2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | ||||
| 2 | Apple | Apple | |||
| 3 | Banana | ||||
| 4 | Apple | ||||
| 5 | Cherry | ||||
| 6 | Banana | ||||
| 7 | Apple | ||||
| 8 |
2 Example 2: Unique regions from a sales table
Here we need a list of regions that appear in the sales data. The formula =UNIQUE(A2:A6) scans the Region column and returns each region once. The result spills into D2:D4. This list can feed a summary report or a dropdown.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | North | ||
| 3 | South | 950 | |||
| 4 | North | 1100 | |||
| 5 | East | 1300 | |||
| 6 | South | 1000 | |||
| 7 |
3 Example 3: Values that appear exactly once
Sometimes you want only the values that occur once, not all distinct values. The formula =UNIQUE(A2:A6, FALSE, TRUE) sets the third argument to TRUE. It returns only values with a single occurrence: Cherry and Date. Apple and Banana appear more than once, so they are excluded.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | |||
| 2 | Apple | Banana | ||
| 3 | Banana | |||
| 4 | Apple | |||
| 5 | Cherry | |||
| 6 | Date | |||
| 7 |
4 Example 4: Unique customer names from orders
You have an order log and need a list of customers who placed orders. The formula =UNIQUE(B2:B6) returns each customer name once, in first-appearance order. The result spills into E2:E4. This is useful for follow-up lists or mail merges.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order ID | Customer | Amount | |||
| 2 | 1001 | Acme Corp | 250 | Acme Corp | ||
| 3 | 1002 | Beta Ltd | 400 | |||
| 4 | 1003 | Acme Corp | 150 | |||
| 5 | 1004 | Gamma Inc | 300 | |||
| 6 | 1005 | Beta Ltd | 200 | |||
| 7 |
5 Example 5: Unique product and category combinations
This example extracts unique combinations of two columns. The formula =UNIQUE(A2:B6) treats each row as a pair and removes duplicate pairs. It returns three unique combinations: Apple/Fruit, Carrot/Vegetable, and Banana/Fruit. The result spills into two columns starting at D2.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | |||
| 2 | Apple | Fruit | Apple | ||
| 3 | Carrot | Vegetable | |||
| 4 | Apple | Fruit | |||
| 5 | Banana | Fruit | |||
| 6 | Carrot | Vegetable | |||
| 7 |
Common UNIQUE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #SPILL! | The spill range is blocked by existing data or merged cells. | Clear the cells below or to the right of the target, or move the formula to an empty area. |
| #CALC! | The array contains only empty cells, so there are no unique values to return. | Check that the range contains data, or wrap the formula in IFERROR to handle empty results. |
| #NAME? | The function name is misspelled or your Excel version does not support UNIQUE. | Check the spelling and ensure you are using Excel for Microsoft 365, Excel 2021, or Excel for the web. |
| #VALUE! | An argument has the wrong type, such as text where TRUE/FALSE is expected. | Use TRUE or FALSE for by_col and exactly_once, or omit them. |
Tips for using UNIQUE
- Combine UNIQUE with SORT to get an alphabetical list:
=SORT(UNIQUE(A2:A10)). - Use UNIQUE inside data validation to create a dropdown that updates automatically.
- Count distinct values with
=COUNTA(UNIQUE(A2:A10)). - Remember that UNIQUE is case-sensitive: 'Apple' and 'apple' are treated as different values.
- Leave room for the spill range; do not type anything directly below or beside the formula.
- Use the third argument TRUE to find values that appear only once, which is useful for spotting one-off entries.
Frequently asked questions
What is the difference between UNIQUE and Remove Duplicates?
Remove Duplicates permanently deletes rows from your data. UNIQUE returns a new list and leaves the original data untouched. The UNIQUE result also updates automatically when the source changes.
Does UNIQUE work in older versions of Excel?
No. UNIQUE is available in Excel for Microsoft 365, Excel 2021, and Excel for the web. In older versions, you can use advanced array formulas or manual methods.
How do I get unique values sorted alphabetically?
Wrap UNIQUE in SORT: =SORT(UNIQUE(A2:A10)). This returns the unique values in ascending order.
Can UNIQUE return values from multiple columns?
Yes. If you give UNIQUE a two-column range, it returns unique rows (combinations). Set by_col to TRUE to compare columns instead.
Why does UNIQUE treat 'Apple' and 'apple' as different?
UNIQUE is case-sensitive. To ignore case, use a helper column with LOWER or UPPER before applying UNIQUE.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. UNIQUE reference