Microsoft Excel 365 has come with a series of new array functions. These functions are very useful & the Unique function is one of them.

The Unique function helps remove duplicate values from a list and keep only the unique/ distinct values. It is very useful to create a dynamic unique list of values from a data set. This function is available only in Microsoft Excel 365. It is not available in earlier versions of excel.

Function arguments

The unique function has 3 arguments.

• Array : – It is the range or array from which to return the unique value from rows or columns
• By Column :- In most cases, the values are pulled from the rows. The default value for this argument is “False”. So, if we do not enter anything for this argument, excel would select the unique values from the rows. If we want to select the unique values from a column list, we should enter “True” in the argument.
• Exactly once :- This argument helps pick values which appear only once in a list. The default value for this argument is “False”. So, if we do not enter anything for this argument, excel would select the distinct values from the list. If we want to select only the unique values which appear only once in the list, we should enter “True” for this argument.

Let us discuss how this function can help with the help of a few examples.

Example 1 – Creating a Unique List of Customer names

Here is a list of customers of ABC Limited who visited the store in the month of January 2021.

We are asked to prepare a list of unique customer names.

To get the list of unique first name, Type

=Unique(B5:B14) and press Enter

You get the list of unique first names in the list.

The names of customer Alex, James and John is appearing more than once in the original list. When we used the unique function, we got the list with all the names appearing just once in the list.

In case, we want the list of customers whose name appears just once in the original list, We will use the first and third argument in the unique function.

Type

=Unique(B5:B14,,True)

Press Enter

You get the list of names appearing only once in the list.

Example 2 – Creating a Unique List of First & Last names of Customers

The Unique function also works with multiple columns.

We have the same list of customers with First and Last Names as in the previous example.

If we want to get the unique First and Last names in the list, Type

= Unique(B5:C14)

Press Enter

We get the unique list of First and last names.

The First names John, James and Alex are appearing twice in the unique list because their last names are different.

James Anderson appearing twice in the original list has been excluded from the list with Unique names.

Example 3 – Creating a Unique List of First & Last names in a single Cell

We can also combine the first and last names of customers while using the unique function. In the above example, if we modify the formula a bit, we get the list of first and last names of customers in a single column.

To get this combined list, type

=unique(B5:B14&” “&C5:C14)

Press Enter.

You get the below list.

Example 4 – Creating a Unique List by columns

In most cases, the Unique function is applied to a list of items in a data set, provided in the rows. The unique function, therefore, gets the unique data points from rows by default. In case we want a unique list from columns then we should use the second argument in the unique function.

In this example, we have the list of customers in rows and dates in a column.

We want to get a unique list of dates from the columns. We will use the first two arguments of the function. Leave the third argument, Exactly once as blank.

To get the list, we type

=unique(C4:H4,True)

Press Enter

You get the following list of unique dates.

In case you want this list in a single column, use the Transpose function along with the unique function.

Type

= Transpose(Unique(C4:H4,True))

Press Enter

You get the list in a single column.

This is how, we can use the unique function with other excel functions.

In our next few articles, we will cover the other new functions introduced in Microsoft Excel 365. Let us know in case you have any questions, by posting in the comment section below.

Happy learning.