UNIQUE() function in Excel is yet another very useful function with Dynamic array and spill range capabilities. UNIQUE() function helps in handling data deduplication and extraction of distinct values efficiently. In this blog post, we’ll look into the details of the UNIQUE() function. We will learn how to use this function, its syntaxes and other tips and tricks related to this function by providing examples. We will also briefly look in to few real world scenarios where this function can be useful.

What is the UNIQUE() Function:

The UNIQUE() function in Excel is designed to extract distinct values from a range, eliminating duplicates and presenting a clean list of unique items. Its distinctive feature lies in its ability to spill results into adjacent cells automatically when used in an array formula.

Syntax

Let’s look in to its syntax and understand each of its input parameters.

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

How to use UNIQUE() function in Excel?

Let’s jump in to the examples and understand how to use this function. We will learn its usage by different types of examples.

Example 1: Get unique values from a single Column Range

Let’s suppose, you have list of fruits in Excel Column B, as shown in below picture.

Table : Fruits Orders

You want to extract all the unique fruits, there are there in column C. This is the simplest example to use UNIQUE() function in excel.

=UNIQUE(C3:C12)
As we learned previosuly, UNIQUE() function has only one mandatory parameter and that is the range where you want to check all the unique values, I have provided the Cell range which has all the fruit names in the table. After the typing the above formula in a cell all the unique fruits are spilled over adjacent cells as shown in the below gif file.

Example 1 : UNIQUE() Function in Excel – Single Column

Example 2: Get unique values from a multiple Column Range

Lets extend the previous example, and add one more column city. Now lets find out the unique values from multiple columns. In this case, unique() function will spill over unique combination of both the column values. In the below example, as you can see although, the values in each columns are still having duplicates. But the combination of the two columns are unique. For example, Mango-Mumbai combination has appeared 3 times in the table, but UNIQUE() function returns this combination only once.

=UNIQUE(G3:H12)

Example 2 : UNIQUE() function on multiple Columns

Example 3: Filtering Unique Values Based on Criteria

Suppose you have a sales dataset with product names in column A and sales in column B. You want to extract unique products with sales greater than a certain threshold, let’s say 100 units. Use the following formula:

=UNIQUE(FILTER(A:B, B:B>100))
This formula combines UNIQUE() with FILTER() to provide a dynamic list of unique products with sales exceeding 100 units. Refer the below gif.

Example 3: Filtering Unique Values Based on Criteria

Example 4: Extracting Unique Values Exactly Once

Lets understand the use of its optional parameter Exactly_Once. Lets take the same Sales data from the above example. Here if you want to identify only those products which are sold only once then in such case, you can use this flag and set it to TRUE

=UNIQUE(K3:L12;FALSE;TRUE)

Example 4: Extracting Unique Values Exactly Once

Example 5: Example 2: Extracting Unique Values by Column

Imagine you have a dataset like shown in the below picture where all the countries and the year they won the world cup, are arranged column wise.
In such case, You can use UNIQUE() function to extract all the countries uniquely which have won world cup so far.

=UNIQUE(C30:N30;TRUE)

Example 5: Extracting Unique Values by Column

Your Playground: Excel Workbook

Now in the below excel workbook, you can play around with the UNIQUE() formula by providing different types of parameters.