UNIQUE Function in Excel

September 27, 2018 - by Bill Jelen

The new UNIQUE function in Excel debuts this week as part of the Dynamic Arrays formula collection. UNIQUE will return the unique values from an array.

Announced at Ignite 2018, the function is one of several new Excel functions:

• Monday covered the new =A2:A20 formula, the SPILL error, and the new SINGLE function required in place of Implicit Intersection
• Tuesday covered SORT and SORTBY
• Wednesday covered FILTER
• Today will cover UNIQUE
• Friday will cover SEQUENCE and RANDARRAY functions

The UNIQUE function syntax is =UNIQUE(Array,[By_Column],[Occurs_Once])

• Array is any array
• For By_Column, the choices are True to compare by column or False to compare by row. It seems that comparing by row is the logical choice and it is the default if you leave the second argument out.
• The optional Occurs_Once argument is interesting and it is time for a small rant.

Consider this list: Apple, Apple, Banana, Cherry, Cherry. What would you say are the unique items in the list?

Clearly, the correct answer is Apple, Banana, Cherry.

But several features in Excel would say that Banana is the only item in the list that has no duplicates. For example, Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values, Unique would only highlight Banana.

Who would ever care about a product that was sold once? Unless you are Casey Kasem listing off the One Hit Wonders on the Billboard Top 40 Chart, this definition of Unique seems useless.

The great news is that the UNIQUE function can return either Apple, Banana, Cherry (the default), or you can change the third argument and get just Banana. We'll send that one out as a long distance dedication to Casey Kasem in the afterlife.

The following examples will be using this data set:

Using UNIQUE is simple. Since the second and third arguments are optional, use =UNIQUE(C4:C29) to return the unique list of products.

Mike Girvin's chapter on getting UNIQUE values just became one sentence.

What if you need every unique combination of Name and Product? Point to a two-column array: =UNIQUE(B4:C29)

Nesting Array Functions

These examples are amazing. Need to sort the list of unique products? No problem: =SORT(UNIQUE(C4:C29,FALSE,FALSE))

How about this one: Filter the names in column B so you get just the rows where the team in A is "blue". Then get just the unique values. Then sort them. =SORT(UNIQUE(FILTER(B4:B29,A4:A29="Blue"),FALSE))

Watch Video

So far this week, you've seen SINGLE, SORT and SORTBY, FILTER and UNIQUE. Tomorrow, I will blow your mind with an article about SEQUENCE and RANDARRAY.

Title Photo: Farsai C. on Unsplash