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:

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)

Unhide all sheets in Excel with a simple macro that works for all your documents

Posted on December 25, 2020 by Audun Danielsen

Sometimes you have to deal with an Excel file that has a lot of hidden sheets, and Excel only lets you unhide them one by one, so it can be quite annoying. Here’s an easy trick that lets you forget that you ever had this problem. It requires VBA, but it’s very simple, and you can save it as a personal macro that works for every Excel file you open on the same computer, so you never have to do it again. I’ll explain every step below.

First, make sure you have the Developer tab in the ribbon. If you don’t have it, right-click on any of the other tabs, choose Customize Ribbon and click the Developer check-box:

How to avoid #DIV/0 and other Error Messages in Excel

Posted on April 24, 2013 by Audun Danielsen

What does the error message #DIV/0 mean? Take a look at this table. It’s a sales report, with the total sales in column B, the number of hours in column C, and finally Sales per hour in column D.

Sales per hour is the total sales divided by the number of hours, and if the Hours field is empty, we are basically telling Excel to divide something by zero. Obviously, that’s impossible, and Excel returns an error message: #DIV/0.

The error message is there for a very good reason, but if we want to show this report to someone else, it would definitely look a lot better if it wasn’t there. Let’s see what we can do about it:

How to Summarize Hours in Excel and Show the Right Result

Posted on April 23, 2013 by Audun Danielsen

Sometimes Excel doesn’t seem to add up the hours correctly. In this table I have made a table of an employee’s working hours and used the SUM function to add them together. Look what happens:

The sum is 13:45 instead of the expected 37:45. That’s 24 hours less than we wanted!

Here’s why: The result is actually 1 day, 13 hours and 45 minutes, but when you use the SUM function, Excel assumes that you want to keep the same format as above, which only shows hours and minutes.

How to fix it:

Trace Dependents Before you Delete Cell Content in Excel

Posted on April 22, 2013 by Audun Danielsen

If you delete the contents of a cell in Excel, there is a risk that other parts of your workbook are affected by it. The easy way to prevent that from happening is to check if any other cells depend on the cell you want to delete. Here’s how to do it:

Select the cell, go to the Formulas ribbon and click on Trace Dependents. The arrows show the dependent cells.

Other Easy Tricks:

Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Easy-Excel.com

Find Easy Solutions to Your Excel Problems!

Tag Archives: Easy tricks