How to Fix the #Div/0 Error in Excel? 2 Easy Methods
(Note: This guide on how to fix the #Div/0 Error in Excel is suitable for all Excel versions including Office 365)
Ignoring errors in Excel is like stepping on landmines. You never know when they are going to explode and cause enormous confusion. We have been covering the major errors in Excel in our blogs, helping you fix them easily. The next important error in our list is the #Div/O error in Excel.
In this guide, let us see what it means and how to fix it easily.
You’ll learn:
What Causes the #Div/0 Error in Excel?
Well, the reason behind the #Div/0 error is rooted in mathematics. We all know from our high school math textbooks that dividing any number by zero is undefined and hence not allowed.
The same principle applies to Excel. Dividing any cell value by zero is illegal and hence Excel will flag it by displaying the #Div/0 error. However, most users will not deliberately divide values by zero. It may so happen that you accidentally use a denominator reference that contains a null or zero value. In most cases, this is the real cause behind the DIV error in Excel.
Another major plausible cause is the AVERAGE function. If you try to use the average function on non-numeric values, it will return a Div/0 error, since the denominator is not present.
Also, if you use an AVERAGEIF or AVERAGEIFS where the function fails to find even one satisfying criterion, a Div/0 error will pop up. Same reason as before. A valid denominator is non-existent.
How to Clear #Div/0 Errors in Excel?
Clearing Div/0 errors in Excel is pretty straightforward. All you have to do is locate it and change or correct the denominator which is causing the error. Fortunately, unlike the REF error, the #Div/0 error will continue to display the original error-causing references inside the formula.
But, as we discussed earlier, if it is caused by a blank space or an accidental zero in the reference cell, it is advisable to look at alternative methods of dealing with this error.
Let us briefly look at two such methods.
Trapping #Div/0 Errors Using IFERROR
Most #Div/0 errors are harmless because they simply point out an error in data input. It is more than sufficient to just trap them and display a custom error message instead. Some users prefer just a blank. It is totally up to you.
Just wrap your error causing formulas inside the IFERROR function like this:
IFERROR(FORMULA(),”Error_Message”) where FORMULA is the error causing formula and Error_Message is the unique message you want to display. Leave it blank if you prefer it that way.
In this example, the formula will be IFERROR(C3/B3,” Not Found”). In case you want a blank space, the formula will be IFERROR(C3/B3,” “). After entering the appropriate IFERROR formula in the first cell, drag it to the rest of the range.
This will trap all #Div/0 errors including #N/A, #REF, #VALUE, and #NAME errors. That is, it will display the result of the FORMULA() if there is no error. If it detects any error, it will return the unique error message or blank space.
Please note that the IFERROR is available only in Excel 2007 and later versions. In case you are using an older version of Excel, you have to use a combination of IF and ISERROR functions. The concept is the same; instead of one function, we now use two functions to check for errors.
Checking for Null and Zero Values in the Denominator
In case you are using a very simple formula like C3/B3, IFERROR is completely unnecessary. You can directly check if the denominator is invalid by using a simple IF statement. Just type: IF(B3,C3/B3,”Message”). You can replace the message with a space or 0 if needed.
For example, If you want a blank space, use the following:
IF(B3, C3/B3, “ “)
Or if you want a zero, use the following formula:
IF(B3,C3/B3,”0”)
Closing Thoughts
Although the #Div/0 Excel may not look troublesome, they are quite problematic if you are planning to use the resultant values in future calculations. These error values can enter your other formulas and cause havoc. Hence, it is always advisable to nip them in the bud, as soon as they appear.
I hope you found this guide useful. If you have any questions, please feel free to ask them in the comments section. We are always happy to help.
If you need more high-quality Excel Guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
