UNIQUE function in Excel - Excel off the grid

I can’t even begin to count the number of times I have created a unique list in Excel. I have performed it manually using the remove duplicates from the Ribbon and also using complex formulas, but that is now a thing of the past. When Microsoft announced changes to Excel’s calculation engine on the 24 September, they also announced a host of new functions. One of those new functions is UNIQUE.

At the time of writing, Microsoft has only made this new functionality available to Office 365 subscribers on the Insider channel. The updates will be rolled out to all Office 365 subscribers at a future point, but first Microsoft need to assess the impact the changes to the calculation engine will have. The new dynamic array functions and features are not available in Excel 2019 or previous versions, so it is definitely worth getting an Office 365 subscription, otherwise, you will be waiting until Excel 2022, and that’s just too long to wait!

The UNIQUE function is straightforward to apply, as shown by the animation below.

The arguments of the UNIQUE function

UNIQUE has just three arguments, but the two optional arguments are so obscure that you will probably only ever use the first.

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

array: the range or array to return values from.
[by_col]: an optional argument where FALSE = compare by row and TRUE = compare by column. If excluded, it will default to FALSE. I can’t think of many circumstances where this would be useful, but at least the option is there.
[occurs_once]: This argument depends on your interpretation of the word unique. If you want a list which includes only the items which appear once then use TRUE. If you want a list which contains one instance of each item (i.e., a distinct list), then use FALSE. This is an optional argument and if excluded will default to FALSE.

Examples of using the UNIQUE function

The following examples illustrate how to use the UNIQUE function.

Example 1 – The difference between unique and distinct values

The last argument of the UNIQUE function determines if it returns a distinct or a unique list.

The Formula in Cell G3 is:

=UNIQUE(B3:B10)

As the third argument has not been used, it has defaulted to FALSE and is therefore showing a list of distinct results. Sally, Jack, Billy, Ryan, Chau and David all appear in Cells B3-B10.

The formula in Cell I3 is:

=UNIQUE(B3:B10,,TRUE)

The third argument is TRUE, therefore UNIQUE will return the results which appear only once. Sally, Billy Ryan, and David all appear once within Cell B3-B10. Jack and Chau appear twice in Cells B3-B10 and are therefore excluded from the result.

This is an important difference to understand. Though, my guess is that in most situations we will using the distinct version of UNIQUE, which is the default option anyway.

Example 2 – UNIQUE linked to an Excel Table

Example 2 shows how UNIQUE responds when linked to an Excel Table.

The UNIQUE function in the animation above is using tblExams[First] as it’s source column. When a new record is added, UNIQUE expands to include the additional value in the spill range.

Example 3 – UNIQUE across 2 columns

UNIQUE is not restricted to a single column. In this example, we are combining the First and Last name columns, to get a unique list of full names.

Method 1

The formula in Cell G3 is:

=UNIQUE(B3:C10)

This includes the First and Last name columns in the array and returns both columns in the result. One instance of Chau Yang has been excluded as it appears twice in the list.

Method 2

The second method uses functionality from the new calculation engine to combine the columns together before applying the UNIQUE function.

The formula in Cell J3 is:

=UNIQUE(B3:B10&" "&C3:C10)

Again, one instance of Chau Yang has been removed to provide a unique list.

In the past, this type of formula would only be possible by using Ctrl + Shift + Enter, but that is now a thing of the past.

Example 4 – Combining UNIQUE with SORT in a data validation list

Example 4 demonstrates how to combine the UNIQUE and SORT functions together.

The formula in Cell G3 is:

=SORT(UNIQUE(C3:C10&", "&B3:B10))

The formula is returning the alphabetically sorted list based on the last name then the first name.

Often the purpose of a unique sorted list is for use within a data validation drop-down list. To do this, we can use the # symbol after the cell reference containing the formula, this is how we refer to the entire spill range.

In the screenshot above, the formula is contained in Cell G3. Therefore =G3# has been used as the source for a data validation list. When the spill range increases or decreases in size, so does the drop-down list. It’s like magic!

Example 5 – Simple formula based Pivot Report

As a final example, we can create a simple Pivot Report using the UNIQUE function combined with some other common functions.

The formula in Cell G3 is:

=UNIQUE(E3:E10)

This is the standard UNIQUE function applied to the Pass/Fail column.

The formula in Cell H2 is:

=TRANSPOSE(UNIQUE(D3:D10))

TRANSPOSE switches the output of UNIQUE from displaying in rows to displaying in columns. The 3 distinct dates in the Exam Date column are now listed as column headers.

The formula in H3 is:

=COUNTIFS(E3:E10,G3#,D3:D10,H2#)

The COUNTIFS function includes the # references and therefore automatically spills in the same way as the cells it is dependent upon.

With 3 simple formulas have been able to create a complete report – amazing!. Remember, if the source is an Excel Table, the output will expand as new data is added.

Want to learn more?

There is a lot to learn about dynamic arrays and the new array functions. Check out my other posts here to learn more:

Introduction to dynamic arrays – learn how the excel calculation engine has changed.
UNIQUE – to list the unique values in a range
SORT – to sort the values in a range
SORTBY – to sort values based on the order of other values
FILTER – to return only the values which meet specific criteria
SEQUENCE – to return a sequence of numbers
RANDARRAY – to return an array of random numbers