Description
The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array.
Syntax
UNIQUE( array, [by_col], [exactly_once])
Arguments
array – The range or array from which to return unique values
by_col – [Optional] A logical value indicating whether to compare rows or columns
exactly_once – [Optional] A logical value to control if only values are returned that occur once
Return value
A dynamic array of unique values
How to use the UNIQUE function in Excel
Use the Excel UNIQUE function to extract the unique values from a given range. The result is a dynamic array and will be spilled into the surrounding cells if the UNIQUE statement is directly entered into a cell (not wrapped by another function).
As an example, to extract all unique values from a vertical list of text values in B6:B15, use the following formula:
= UNIQUE(B6:B15)
To extract unique values from a horizontal range, you have to set the optional by_col argument to TRUE and use the following statement instead:
= UNIQUE(B6:H6, TRUE) //extracts unique values horizontally
Additionally, you can specify that only those values are counted that occur exactly once. For this, you have to set the optional exactly_once argument to TRUE:
= UNIQUE(B6:B15, , TRUE) // extracts values that appear only once
For an even better result, UNIQUE is often combined with other functions, like for example the SORT function to retrieve a list of sorted, unique values.
Formula examples
Related Tutorials
Related Functions
Excel FILTER function
The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.
Excel SORTBY function
The Excel SORTBY function sorts the contents of a given range or array based on the values of another range or array. It returns a dynamic array result.
Excel SORT function
The Excel SORT function sorts the contents of a given range or array and returns a dynamic array result. Values can be sorted by one more multiple columns.
