Excel 2020: Formula for Unique or Distinct

September 21, 2020 - by Bill Jelen

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

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.

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

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

Title Photo: rawpixel.com / Unsplash

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