MrExcel Publishing
Your One Stop for Excel Tips & Solutions

UNIQUE Function in Excel


September 27, 2018 - by Bill Jelen

UNIQUE Function in Excel

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:

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.


Which would you say is the true unique list?
Which would you say is the true unique list?

The following examples will be using this data set:

Team, Name, Product, Score
Team, Name, Product, Score

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

Return the unique list of products
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)

Every unique combination of two values
Every unique combination of two values

Nesting Array Functions

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

Get the unique values and sort
Get the unique values and sort

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))

Nesting array functions is no problem
Nesting array functions is no problem

Watch Video

Download Excel File

To download the excel file: unique-function-in-excel.xlsx

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.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Omit needless formats"

Title Photo: Farsai C. on Unsplash


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.