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.

Highlight an Entire Row in Excel Based on One Cell Value

Posted on April 20, 2013 by Audun Danielsen

If you want to highlight a cell in Excel based on its value, it’s pretty straight forward: Just choose Conditional Formatting from the Home ribbon. But what if you want to highlight the entire row based on the value in just one of the cells? We’ll use Conditional Formatting here too, but with a slightly different approach than we’re used to.

This is what we want: Choose one of the names in the table below and highlight the entire row for each occurence of that name.

The Easiest Way to Hide Zeros in Excel

Posted on April 19, 2013 by Audun Danielsen

An Excel table with a lot of numbers in it can be difficult to read. Often, a lot of the numbers are zeros, and it is sometimes a good idea to hide them in order to make the important numbers more visible. Of course, you could write IF-formulas that return nothing if the result is zero, but usually the way I am going to show here is better and easier.

This is what my table looks like:

Show File Path in Excel

Posted on April 4, 2013 by Audun Danielsen

If you want to see where your Excel file is located on the computer or the server, you can display the file path in the Quick Access Toolbar. With the file path displayed here, you can also copy and paste it into an e-mail and send it to a colleague (provided they have access to the same server). A great way to avoid sending attachments!

You only have to do this once – if you add something to the Quick Access Toolbar, it will be displayed in every Excel document on the same computer.

Easy-Excel.com

Find Easy Solutions to Your Excel Problems!

Category Archives: Easy Tricks