How to get Unique values in Excel with one simple formula: UNIQUE

 In the newer versions of Excel (2019 and 365) you can finally extract unique values with one simple function: UNIQUE. In previous versions, you either had to do it manually with the advanced filter, with a pivot table, or with a super-complicated formula that was impossible to remember



Now it’s easy: Just use the UNIQUE function!

UNIQUE is one of the new Spill functions in Excel, which means that you type the formula in a cell, e.g. D2, and it populates as many cells below as it needs. So keep in mind that you need to have a sufficient number of empty cells to make room for the results.

Here’s how you do it:

In this example, I have a list of transactions. There are 20 rows of data, but we only have six unique sales reps, so I want to create a small table with one row for each rep. This formula is all I need:

=UNIQUE(A2:A26)

It will find the unique values and spill down automatically. We don’t even have to copy down!


To add sales figures per sales rep, just use the SUMIFS:


And if you want the names as column header, you can wrap the TRANSPOSE function around SORT and UNIQUE:

=TRANSPOSE(SORT(UNIQUE(A2:A26)))


Comments

New comments are not allowed.

Popular posts from this blog

SUM across multiple sheets in Excel

Image
If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it. The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that. A better way to do it is to use this formula: =SUM(Jan:Dec!B2) (and copy down and across) With this formula, Excel looks at all the tabs from Jan through Dec and picks up the value in B2 in all of them. Note that you only need an exclamation mark after the second sheet reference. You have to be careful when you use this approach, though. If you insert or move another sheet between the ones you want to refer to in the formula, Excel assumes that you want to include that too. On the picture below you can see what might happen if you move a phone ...
Read more