UNIQUE function
The UNIQUE function in Excel is used to return a list of unique values from a range or array, effectively filtering out duplicates. It’s a dynamic array function, meaning it will automatically expand to fit the results, and works well with other functions like SORT or FILTER.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
Parameters
array: The range or array from which you want to return unique values. This is the only required argument.[by_col](optional): A logical value indicating whether to compare values by rows or columns. The default isFALSE(compares values by rows).FALSE(or omitted): Compares values by row (default behavior).TRUE: Compares values by column.
[exactly_once](optional): A logical value that returns values that appear only once in the array. The default isFALSE.FALSE(or omitted): Returns all unique values.TRUE: Returns only the values that appear exactly once in the array.
How It Works
The UNIQUE function scans the given array and returns only the distinct values, eliminating duplicates. Optionally, you can specify to return values that occur only once or compare values by rows or columns.
Examples
Example 1: Return Unique Values from a Column
Let’s say you have a list of numbers in cells A1:A8:
| A |
|---|
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
| 4 |
| 5 |
| 5 |
To return only the unique values (1, 2, 3, 4, 5), use the following formula:
=UNIQUE(A1:A8)
The result will be:
| Result |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Example 2: Return Values That Appear Only Once
If you want to return values that appear exactly once (in this case, only 1 and 3), you can modify the formula like this:
=UNIQUE(A1:A8,,TRUE)
The result will be:
| Result |
|---|
| 1 |
| 3 |
Example 3: Return Unique Values by Column
If you have a dataset organized by columns, the UNIQUE function can also return unique values based on column comparison.
For example, if you have the following dataset in cells A1:C2:
| A | B | C |
|---|---|---|
| 1 | 2 | 1 |
| 3 | 2 | 4 |
To find unique values comparing by column:
=UNIQUE(A1:C2, TRUE)
This will return:
| Result |
|---|
| 1 |
| 2 |
| 4 |
Example 4: Unique Values in a Range
If you have a table in cells A1:B6 and want to return only the unique rows, you can use UNIQUE:
| A | B |
|---|---|
| Red | 1 |
| Blue | 2 |
| Red | 1 |
| Green | 3 |
| Blue | 2 |
| Yellow | 4 |
The formula:
=UNIQUE(A1:B6)
Would return:
| A | B |
|---|---|
| Red | 1 |
| Blue | 2 |
| Green | 3 |
| Yellow | 4 |
When to Use the UNIQUE Function
- To remove duplicate values from a dataset.
- To filter unique rows or columns from large datasets.
- To return values that appear only once, for analysis or reporting purposes.
Combining with Other Functions
- SORT: You can combine UNIQUE with SORT to return unique values in a sorted order. Example:
=SORT(UNIQUE(A1:A10)). - FILTER: You can combine UNIQUE with FILTER to return unique values that meet specific criteria. Example:
=UNIQUE(FILTER(A1:A10, B1:B10 > 0)).
The UNIQUE function is a powerful tool for eliminating duplicates and simplifying data analysis, especially when combined with other dynamic array functions.