Excel UNIQUE function

Function              UNIQUE

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 will return rows or columns that occur exactly once in the range or array. This is the database concept of uniqueness.

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

Example#1:

Here I have an example array and I am using the UNIQUE function on the first column.

Example#2:

Next, I shall show another example. Here I shall select a row where some duplicate value occurs.

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.

Note: 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.