How to Handle #Div/0! Errors in Excel

Everyone at some point will come across the infamous “#DIV/0!” error in excel, it’s only a matter of time. When you get this error message, it means that the formula in the cell this error is displayed in has a divisor of 0, or otherwise is attempting to divide one value by 0. As we know from elementary school this isn’t possible (unless you’re a mathematician of course).

As seen in the figure above, there are a few different ways to achieve the “#DIV/0!” error in excel.

  1. You can simply have static values not referencing a cell such as 1 / 0. The numerator equals 1 while the denominator equals 0, this won’t work.
  2. You can use the QUOTIENT() formula which allows you to specify the numerator and the denominator. These can be cell references or static values. The result however, the same.
  3. You can use a cell reference which the value of the cell being referred too is 0.

As you can see, there are various ways in which you can encounter the “#DIV/0!” error in excel and often, it’s unavoidable. So how do we handle this? There will be plenty of times you will have formulas referencing cells that do not contain values yet.

Check out the value to the right. As you can see, some of the hours worked have not been entered in yet, and thus, Column C is showing “#DIV/0!”. For professional sheets, this could be an eye sore and otherwise make the sheet difficult to read.

Evaluate before dividing by: 0, “”, or NULL

The most viable way to suppress a “#DIV/0!” error is to use the IF() function. If the value you are checking equals 0, “”, or NULL, then you can handle this by having the display show whatever you would like.

For example:

If the formula resulting in an error is similar to =B2/A2 or =1/0, then you can use the IF() function. IF() can be written in a variety of ways:

  • IF(B2,A2/B2,0)
  • IF(B2=0,0,A2/B2)
  • IF(B2<>0,B2/A2,0)

It comes down to your preference. You can display a 0, a custom message like “No Value” or you could have it return blank.

In the below illustration, you will many variations of formulas and results. As mentioned in the above example, you can use 0, a custom message or no value at all for handling the referenced cell. After all, in basic mathematics you can’t divide your numerator by a denominator that equals 0.

#DIV/0! Error suppression

There are other ways to handle “#DIV/0!” other than using the IF() function. Using IFERROR(), IFERR() or IF(ISERROR()) are perfectly acceptable functions to use.

This is error suppression, different from an IF() function in the sense that these functions check for blanket errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. So, make sure you validate your formulas are working before including error handling. It could be possible that your formulas is returning a different error but you would not know this.

Quick Notes:

  • IF(ISERROR()) is used for versions of Excel 2007 and prior.
  • IFERR() will check for all of the above blanket errors except #N/A
  • IFERROR() will check for all blanket errors

If you have error checking turned on in Excel, you can click the yellow exclamation point next to the cell that shows the error. This will give you several different options, one of which is “Show Calculation Steps” if it is available.

Kevin Arrows
Kevin is a dynamic and self-motivated information technology professional, with a Thorough knowledge of all facets pertaining to network infrastructure design, implementation and administration. Superior record of delivering simultaneous large-scale mission critical projects on time and under budget.
Back to top button

Expert Tip

How to Handle #Div/0! Errors in Excel

If the issue is with your Computer or a Laptop you should try using Restoro which can scan the repositories and replace corrupt and missing files. This works in most cases, where the issue is originated due to a system corruption. You can download Restoro by clicking the Download button below.

Download Now

I'm not interested