New Excel Magic Formula #1: UNIQUE

Microsoft has included some new formulas in Microsoft Excel that I call "magic formulas".

They are:

• UNIQUE
• FILTER
• RANDARRAY
• SEQUENCE
• SORT
• SORTBY

In today's post I am going to introduce you to UNIQUE. It can be the end of your troubles with PivotTable for use with growing/changing dataset. And it can be the butter to your SUMIFS, COUNTIFS and many other high-level report creation formulas.

Scenario

Imagine you need to create a live report of sales from raw data that keep getting updated (or is growing). Download the practice along file at https://urbizedge.blob.core.windows.net/urbizedge/UNIQUE%20-%20new%20formula.xlsx 

You could use PivotTable to create this:

The sad part is that whenever new data is added, you will have to manually include the new data range in the PivotTable  or do manual refresh if you had used "Format as Table" on your raw data before Pivot Table creation.

Alternatively, you could have used Remove Duplicates, SUMIF and COUNTIF. Again, upon new product addition, you'll have to redo the Remove Duplicates.

Meet UNIQUE

UNIQUE is the solution you want.

It does remove duplicate but in a dynamic way. If you don't want to select unused cells in order to capture new entries, then just apply "Format as Table" on your data. But for this demonstration, I am going to show without formatting as table while in the video I demonstrate both situations.

Simply typing UNIQUE(range_of_cells) creates the remove duplicate equivalent of the product field, giving me the unique list of products sold. 

Now I can merge it with other regular formulas like SUMIF and COUNTIF to get the sales per product and count of transactions. 

In the criteria, I select the entire output of the UNIQUE formula. This automatically changes it to first_cell_address_# (e.g. K2#). That # is a new feature to let you know that it's working on an array output.

I do likewise for SUMIF to get the sum of quantity and sales amount per product.

You can watch a short video demonstration of this: https://youtu.be/QLgwXVkALFg