Lesson#165: What is the Excel UNIQUE function?

Excel UNIQUE function has been recently introduced by Microsoft. It’s a very good function and method to remove duplicate values in Excel. So in this post, we shall discuss the function and I shall show you some examples.

What is the Excel UNIQUE function?

Description        Excel UNIQUE function returns a list of unique values in a list or range.

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

array  Mandatory. A reference to a cell or range of cells or an array.

by_col [optional] How to compare and extract.

TRUE will compare columns against each other and return the unique columns

FALSE (or omitted) will compare rows against each other and return the unique rows

exactly_once [optional]

The exactly_once argument is a logical value that returns rows or columns that appear in the range or array exactly once. This is the concept of uniqueness in a database.

TRUE will return all distinct rows or columns that occur exactly once from the range or array

FALSE (or omitted) will return all distinct rows or columns from the range or array

How to use the Excel UNIQUE function?

Example#1:

Here’s an example array with the UNIQUE function applied to the first column.

Example#2:

Next, I’ll give you another example. In this case, I’ll choose a row with a duplicate value.

But it is not removing duplicate values in rows normally. For that, you need to use the parameter by_col

Example#3:

Now, this happens if I use the last parameter exactly_once

It will only show those values which are not repeated in this selected array.

Things to remember when using Excel UNIQUE Function

Excel only supports dynamic arrays between workbooks to a limited extent, and this scenario is only supported when both workbooks are open. When you close the source workbook, any linked dynamic array formulas that are refreshed will return a #REF! error.

The UNIQUE function is not case-sensitive. UNIQUE will treat “INDIA”, “India”, and “india” as the same text.

How to get this function?

Currently, UNIQUE is available on Excel for Microsoft 365 (Windows), Excel for Microsoft 365 (Mac), and  Excel for the web only. Only from there, you can get these new excel formulas.

Join our Telegram channel https://t.me/excelabcd