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

Posted on November 7, 2021 by Audun Danielsen

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:

How to include the measurement unit in the number format in Excel

Posted on April 5, 2021 by Audun Danielsen

Excel has a few built-in number formats such as $100.00 or ¥100,00. But what if you want to add another measurement unit to the number format, such as lbs, stone, kg, km/h, ft, acre or oz?

If you type “169 lbs” in the cell, Excel will interpret it as text, and you won’t be able to do any calculations. We need to find another way.

How to handle tables with empty cells in Excel

Posted on March 7, 2021 by Audun Danielsen

Sometimes you receive an Excel report or an export from another system with a lot of empty cells in a category column:

If you want to analyze the data, either with a Pivot table or with formulas such as SUMIFS and VLOOKUP, you need to populate all the empty cells in the category (dimension) columns, in this example column A.

For example, if you want the Total Sales for Northeast, the formula =SUMIFS(E:E,A:A,”Northeast”) will only work if you have the right region in every cell in column A .

This is how you do it:

SUM across multiple sheets in Excel

Posted on February 14, 2021 by Audun Danielsen

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:

Make SUMIFS and COUNTIFS in Excel more flexible with a Wildcard

Posted on February 13, 2021 by Audun Danielsen

How to use a wildcard

In the example below I have used the COUNTIFS function to count the number of members who fulfill the two criteria in the report, and the SUMIFS function to calculate the total amount paid if both criteria are met.

=COUNTIFS(B2:B11,G2,D2:D11,G3)
=SUMIFS(C2:C11,B2:B11,G2,D2:D11,G3)

Summarize a whole table in Excel without writing a formula

Posted on February 7, 2021 by Audun Danielsen

Instead of typing a SUM formula, an easier way to go is to use the shortcut Alt + Equal sign (=)

But did you know that you can use this shortcut for a whole table?

If you have a table like the one below, select all the values plus one extra row and one extra column:

Easy-Excel.com

Find Easy Solutions to Your Excel Problems!