Ever had some of your Microsoft Excel spreadsheets show a #DIV/0 error in various cells? It brings back memories of math class for some. In this tutorial, I’ll explain what causes them and how to remove #div/0 errors in Excel from showing that dreaded text. We’ll solve this problem by adding a simple IF function argument.
What Are Excel Divide by Zero Errors
It’s a logical error that happens when either of these conditions occurs:
- a number is divided by zero (0)
- your Excel formula refers to a blank or empty cell
Suppose you were to click one of the #DIV/0! cells, you would see a Microsoft Excel tooltip 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.
I got the Excel error in cell E7 because I tried to divide my Cost value in C7 by my Catalog Count in D7. It’s a division by zero error message.
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 tooltip 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
You’re probably wondering how to remove the #div/0 error. After all, empty cells and zeros are valid entries. There are several ways to fix this error, including the IFERROR function. However, I prefer to use the Excel 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 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 by 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 this 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. Using the Cost value would be better than “0” or a blank in my example of catalog conversion costs.
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.
Remember, to make your spreadsheets more useful, you need to prevent and remove the #DIV/0 error in Excel. You can do this by using the IF function in Excel that will tell your spreadsheet what to do when it encounters a specific condition—in this case, an error. So, the next time you write a formula where there is a possibility of getting the dreaded #DIV/0!, remember these tips for removing them before they happen or correcting them after they have happened. Chances are, whichever method you choose will be less annoying for your users than seeing the error.