Unique function example

Excel Unique Function

June 16, 2020


Have you wanted to count unique values in Excel? Early this year Microsoft released to all users of Excel new array functions that make using an array A LOT easier in order to do so.  These mainly take the place of Control-Shift-Enter arrays (CSE). If you are a user of LibreOffice, I am afraid these are not yet available to that office suite. I am sure they are on their way though. The new array functions will be able to save you a lot of thinking and math.

Under the old CSE style of arrays you could come up with long and complex formulas.  I believe the longest formula I wrote in a spreadsheet was about 600 characters. Needless to say debugging or adding new considerations in the formula was time consuming and took some time. Depending on what you are trying to calculate, that same type of formula could possibly now be written in about 50 characters.

I am going to keep this simple by discussing only the new UNIQUE function here. First lets take a look at the old way. The way I would evaluate a list uses IFERROR to avoid displaying N/A errors. LOOKUP to do what it suggests, lookup values. Finally COUNTIF to evaluate the lookup data to find the unique values. I made a simple spreadsheet with a fictional employee list as seen below:

CSE style array formula to find unique values.

You can see the formula I used up in the formula bar. Using a binary (1 = yes) type of sort I labelled all the active employees. So now I want to find all the unique names. This example is rather impractical in real life, but that is why it is an example. In a nutshell that ugly formula basically says Lookup and write only once unique names from column B and list them in column D only if the value in Column A is 1.  It lists from bottom up and in column D it fills in the names by comparing the names to the name listed above in column D. I then copied that formula into every cell from D2 through D15, Rather complicated.  It works. Yes there are other ways to do this, however the formula will look just as complicated.

So what happened in 2020?  SOMETHING AMAZING! In fact it will blow you away. It will give you free time. It might win over new fans to Excel and other spreadsheet software programs. Really to be honest, this is likely very familiar to you if you use Google Sheets. Are you ready to see how to rewrite the above formula the new way?

The Unique function

UNIQUE formula with spill results.

There you have it, the new way to count unique Excel entries. To get the same list of unique names from the previous formula, just type =UNIQUE(B2:B15).  The only issue you may have with this is if the column the data spills into finds a cell with data in it.  In which case you will get a spill error. Another benefit is that since you only entered the formula one time and the resulting data “spills” down, you end up with a smaller file size that loads faster.

There you have it, one of the new functions Microsoft added to Excel.  Such a time and frustration saver. 

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>