Excel UNIQUE function

The Excel UNIQUE function extracts unique values from a range and returns a list of unique values in a list or range.

In a nutshell: The UNIQUE function can handle various data types. For example, you can use text, numbers, and date values and returns an array of unique values. The function is available for Excel 365 users.

Syntax and Arguments of the UNIQUE function

Here is a UNIQUE function’s syntax.

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

Arguments:

  • array is the range or array from which to return unique rows or columns.
  • by_col is a logical value. It controls how to compare and extract values. FALSE is the default value and extract values by row. In case of TRUE the function extract values by column.
  • exactly_once is a logical value. If the value is FALSE the function extract all distinct values. If the argument is TRUE the function extract values that occur exactly once. The default value is FALSE.

How to use Excel UNIQUE function

The UNIQUE function evaluates an array and extracts the unique values to another array. The result is a dynamic array that contains a list of unique values.

UNIQUE is a member of dynamic array functions in Excel. Therefore, formulas that return multiple values spill these values directly onto the worksheet. You will get the result as an array. If you add or remove values to a source range, Excel will update the result array.

The function uses three arguments. The ‘array’ is required. The by_col’ and ‘exactly_once’ are optional.

Examples

In this section, we will demonstrate how the function works through easy-to-understand examples.

Basic usage of UNIQUE function

In the example, we want to extract unique values from the range B3:B10. Use the formula without the second and third arguments:

=UNIQUE(B3:B10)

unique function basic excel example

Using the ‘By column’ argument

As we mentioned above, the UNIQUE function extracts unique values in rows by default.

If you use UNIQUE without the second argument, the function can not handle the values in columns.

Formula:

=UNIQUE(D2:D12)

UNIQUE will not handle the same values organized in columns

In the following example, we will add TRUE as a second argument; the function will handle the horizontal array properly.

Formula:

=UNIQUE(D2:D12, TRUE)

column argument

The returned array contains unique values only.

Using the ‘exactly once’ argument

Take a closer look at the differences between “distinct” and “exactly once”.

As we stated above, the third argument is optional. The “exactly_once” decides how the function handles duplicated values. By default, it uses FALSE and returns with every distinct item:

The formula returns with every distinct item.

Apply TRUE (or 1) for exactly_once argument. The function returns unique values that appear exactly once in the source range.

Both formulas below return the same result:

  • =UNIQUE(B3:B11,0,1)
  • =UNIQUE(B3:B11,,TRUE)
extract unique values that appear exactly once

Now we have learned almost all about the UNIQUE function.

Workaround with criteria

Working with Excel, you may need to extract unique values based on a logical test. Learn how to use the FILTER and UNIQUE functions in one formula.

Generic formula to use a criteria:

=UNIQUE(FILTER(range1, range2 = B1))

If you want to learn more about this formula, you can find our detailed guide here.

Additional resources

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.