MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Formula for Unique or Distinct


September 16, 2019 - by Bill Jelen

Excel Formula for Unique or Distinct. Photo Credit: rawpixel at Unsplash.com

The UNIQUE function will provide either a list of unique or distinct values.

=UNIQUE has three arguments: array, by_col and occurs_once. Only the array is required.

If you ask me for the list of unique values from this list: Apple, Apple, Banana, Cherry, Cherry, I would tell you that the list of unique values is Apple, Banana, Cherry. If you ask 100 accountants, about 92 of them would agree with me. But there is a segment of the population who disagrees and says that the only unique thing in the list is Banana because it is the only item that appears once.


This unusual definition of "unique" comes from the SQL Server world, where database pros would say that "Apple, Banana, Cherry" is a list of distinct values and Banana is the only unique value.

The new UNIQUE function will return either list. If you simply ask for =UNIQUE(R5:R9), you get my definition of all values that occur one or more times. But, if you are a database pro or Casey Kasem, then you can put a True as the third argument.

You start with a list of Apple, Apple, Banana, Cherry, Cherry. Use =UNIQUE(R5:R9) to get Apple, Banana, Cherry. Use =UNIQUE(R5:R9,,TRUE) to get just Banana.

Here is a list of all the Billboard Top 10 Hits from 1979 - 1993.

A database of Billboard Top 10 Music Hits from 1979-1993. ID is in column A, Artist in B, Track in C, and Genre in D.

To get a list of genres, use =UNIQUE()

=UNIQUE(D4:D6132) returns a list of genres such as Rock, Easy, Vocal, Pop, R&B, Country, Folk

Title Photo: rawpixel.com / Unsplash


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

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