UNIQUE Function

by | Feb 14, 2019 | Excel Formulas

UNIQUE Function

The Excel UNIQUE function is a lookup and reference formula that returns a list of unique values from a list or range. This formula is one of the newest Dynamic Array functions to be released with Excel 2019. In this guide, we’re going to show you how to use the Excel UNIQUE function and also go over some tips and error handling methods.

The Dynamic Array functions can populate an array of values in a cell range based on another formula. This behavior is called spilling and can help overcome the limitations of array formulas.

Supported versions

  • At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it's ready, the feature is planned for release for Office 365 users.

Excel UNIQUE Function Syntax

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

Arguments

array

The range or array you want to parse unique values from.

[by_col]

Optional. A Boolean value that specifyies the comparison direction.

·         By row = FALSE (default)

·         By column = TRUE

[occurs_once]

Optional. A Boolean value indicating whether occurrence number is important.

·         TRUE: list values that occur once

·         FALSE: list values regardless of the number of occurrences (default)


Examples

Note that we're using named ranges in our sample formulas to make them easier to read. This is not required.

Example 1

=UNIQUE(Type)
formula is a simple use case with the Excel UNIQUE function. Enter the range of values for the array argument and press Enter, Excel will populate the list automatically. You do not need to select the range or specify how many items are to be listed. If there is enough space (empty cells) underneath the formula, the formula will work as intended.

UNIQUE Function

Example 2

=UNIQUE(Type,FALSE,TRUE)
formula demonstrates using the UNIQUE formula to list values that only occur once in a range or array. To activate this type of listing, set the occurs_once argument to TRUE or 1. If you compare the results in Example 1 and Example 2, you can see that second list contains fewer items, because FIRE and WATER items are listed more than once in the specified range (FIRE, 2 times; WATER, 5 times).

Example 3

=UNIQUE(C3:D16)
formula shows how the UNIQUE formula acts for multiple columns. If multiple listing condition is available, the UNIQUE function evaluates all rows together rather than individual cells. In our example, even though there are multiple occurrences of the string WATER in column C, only rows with WATER in column C and I in column D are listed.

Download Workbook

Tips

  • Currently, this formula is only available on Office 365 which comes with a subscription.
  • The UNIQUE function can be used as an alternative to the Remove Duplicates feature, without modifying the actual data.
  • For horizontal ranges like A1:F1, set the by_col argument to TRUE or 1.
    =UNIQUE(A1:F1,1)
  • Use with the SORT function to create a sorted list of unique items.

Issues

#SPILL!

If there isn't enough space for adding the results below the formula, you will see a #SPILL! error. Excel marks the target range with dashed lines. Clear the contents of the cells in this range, and Excel will automatically update the results.

#CALC!

The UNIQUE function returns a #CALC! error if it can't generate an array. For example, if you use occurs_once = TRUE with an array with repeating values (e.g. {2,2,3,3,3,4,4,4}) you will get this error.