Excel UNIQUE function

How to use the Excel UNIQUE function


The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array. 


UNIQUE( array, [by_col], [exactly_once])


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

How to use the Excel SORT function

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.

How to use the Excel SORTBY function

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.

How to use the Excel FILTER function

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.

Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email