As seen in the figure above, there are a few different ways to achieve the “#DIV/0!” error in excel. 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/AIFERROR() 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.
How to handle Excel VLOOKUP #REF! ErrorFix: Facebook is currently unable to handle this request.Fix: Invalid File Handle on Windows 10Fix: The Handle is Invalid Error Message When Logging Into Windows 10
