MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Formula for Unique or Distinct


September 21, 2020 - 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


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.