I now understand the difference between an Excel tip and an Excel annoyance. It’s an annoyance if the person you send your spreadsheet to doesn’t know the tip and you spend more time explaining the error than it takes to fix it. Next time, I’ll take the five minutes to fix my Excel formula and divisors so it doesn’t display the #DIV/0!error message.
What Does #DIV/0! mean in Excel?
It’s a logical error that happens when:
A. a number is divided by zero (0)
B. your Excel formula refers to a blank or empty cell.
If you were to click one of the #DIV/0! cells you would see a tool tip that reads: “The formula or function used is dividing by zero or empty cells”. You can see an example in the screenshot below using Microsoft Office 365.
The first reason I got the Excel error in E7 was because I tried to divide my Cost value in C7 by my Catalog Count in D7.
I ran an ad that cost $77.45 and generated 0 catalog requests. In other words, I was asking Microsoft Excel to divide $77.45 by zero. Oops.
The second reason Excel displays this error for cell E11 was because cell D11 is blank. It’s hard to see because the tool tip overlays the cell value.
While it may seem that this error would only display when division was involved, it can be inherited from a range. In the example below, I tried to SUM column E. I also get the error because there was #DIV/0! in the cell range.
Add Simple Logic to Your Excel Formula
There are several ways to fix this error including the IFERROR function. However, I prefer to use the IF function. IFERROR can mask all errors besides this annoyance.
This is a logic function where you can direct Excel to do one action IF a condition is TRUE and another action if the condition is FALSE.
In this case, I want Excel to take a different action IF I have a Catalog Count of “0”. Otherwise, Excel can continue as normal. Specifically, I want to replace the error message with $0.00.
How to Show a Zero Value instead of #DIV/0!
- Create a column for your formula. (e.g. Column E Conv Cost)
- Click the next cell down in that column. (e.g. E2)
- Click the Formulas tab on the Excel ribbon.
- Click the Insert Function button on the far left.
- In the Insert Function dialog, select IF.
- Click OK.
- In the Function Arguments dialog, click in the Logical_test field.
- Click the top cell in the column which you’re dividing by. (e.g. D2)
- In the same text field after the cell reference type =0. (The field should show something like D2=0)
- Leave the Value_if_true field blank to get $0.00.
- In the Value_if_false field, enter your formula such as C2/D2
- Click OK.
- Copy the Excel formula down to each cell in the column.
You can see in the example below, in each case where I had a “0” or a blank cell in Column D (Catalog Count), Excel left the Conv Cost value in Column E as $0.00. This function also fixed my sum value in cell E14.
In short order, you’ll start entering this function directly in the Excel formula bar rather than using the Insert Function dialog.
Other Formula Substitutions for #DIV/0!
Just as some people don’t like the divide by zero error messages, others might object to blank cell values or the $0.00 reference. They might prefer something like a text value or even the contents of another cell.
You can substitute text or a cell reference instead of having the cell be $0.00. For example, I could’ve used “NA”. Please note the quote marks or you could get formula errors.
or left blank
or used another cell value
In this last example, Excel would insert the Cost value in the Conv Cost cell instead. Depending on your situation this may be more accurate. In my example of catalog conversion costs, using the Cost value would be better than “0” or a blank.
Another solution from readers mentioned that the ERROR.TYPE method works well. Microsoft has assigned an error value of “2” for this #DIV/0 example. You can find more details on ERROR.TYPE on the Microsoft site.
As you can see there are several ways you can fix the divide by zero error in Excel. Chances are whichever method you choose will be less annoying for your users than seeing #DIV/0!