How to fix the #DIV/0! error
About the #DIV/0! error
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0! errors when data is being entered, but is not yet complete. For example, a cell in the worksheet is blank because data is not yet available.
Although a #DIV/0! error is caused by an attempt to divide by zero. It may also appear in other formulas that display the #DIV/0! error. For example, if any cell in A1:A5 contains a #DIV/0! error, the SUM formula below will display #DIV/0!:
The best way to prevent #DIV/0! errors is make sure data is complete. If you see an unexpected #DIV/0! error, check the following:
- All cells used by a formula contain valid information
- There are no blank cells used to divide other values
- The cells referenced by a formula do not already display a #DIV/0! error
Note: if you try to divide a number by a text value, you will see a #VALUE error not #DIV/0!.
Trapping the #DIV/0! error with IF
A simple way to trap the #DIV/0! is to check required values with the IF function. In the example shown, the #DIV/0! error appears in cell D6 because cell C6 is blank:
=B6/C6 // #DIV/0! because C6 is blank
To check that C6 has a value, and abort the calculation if no value is available you can use IF like this:
=IF(C6="","",B6/C6) // display nothing if C6 is blank
You can extend this idea further and check that both B6 and C6 have values using the OR function:
See also: IF cell is this OR that.
Trapping the #DIV/0! error with IFERROR
Another option for trapping the #DIV/0! error is the IFERROR function. IFERROR will catch any error and return an alternative result. To trap the #DIV/0! error, wrap the IFERROR function around the formula in D6 like this:
=IFERROR(B6/C6,"") // displays nothing when C6 is empty
Add a message
If you want to display a message when you trap an #DIV/0! error, just wrap the message in quotes. For example, to display the message "Please enter hours", you can use:
=IFERROR(B6/C6,"Please enter hours")
This message will be displayed instead of #DIV/0! while C6 remains blank.