#DIV/0! means a formula tried to divide by zero or by an empty cell — Excel treats a blank as zero in division. The targeted fix is to test the divisor before dividing: =IF(B2=0,"",A2/B2) returns a blank instead of an error when B2 is empty or zero.
The broader fix is =IFERROR(A2/B2,""), which catches this and every other error type — convenient, but it will also hide a genuine #REF! or #VALUE!, so use the IF version when you only want to handle division.
Two other common sources: AVERAGE returns #DIV/0! when the range contains no numbers at all, and AVERAGEIF or AVERAGEIFS return it when no rows match the criteria — in both cases there’s nothing to divide by.
And if one cell in a range already holds #DIV/0!, any SUM or AVERAGE over that range inherits it, so fix the source cell rather than the formula reading it. To hide errors on a printout without changing formulas, use Page Layout → Print Titles → Sheet → Cell errors as: blank.
In Excel, the #DIV/0! error appears when a formula divides a number by zero, or by a cell that Excel treats as zero, such as a blank cell. It’s Excel’s way of telling you that the number you’re dividing by (the divisor) is zero.
In the following example, cell A1 has a number and cell B1 has a zero. When you enter a formula in cell C1 to divide A1 by B1, you get this error.
#DIV/0! With a Blank Cell
A blank cell counts as zero in a division, so a formula like A1/B1 returns #DIV/0! when B1 is empty. The error also carries forward: if your formula refers to a cell that already shows #DIV/0!, your formula returns #DIV/0! too.
#DIV/0! Error with an Average
If you use AVERAGE on a range where one of the cells has a #DIV/0! error, the result is #DIV/0! as well. AVERAGEA works the same way. AVERAGEIF and AVERAGEIFS return the error too when the cell with the error meets the criteria and gets included in the average.
There’s one more case to know. These functions also return #DIV/0! when there’s nothing to average. AVERAGE does it when the range has no numbers (all blank or text), and AVERAGEIF and AVERAGEIFS do it when no cell meets the criteria.
How to Fix #DIV/0! (Divide by Zero) Error
The quickest way to fix this error is to wrap the original formula in the IFERROR function. IFERROR returns a value of your choice when the formula results in an error.
- First, in cell C1, type =IFERROR and an opening parenthesis.
- After that, in the value argument, enter the division formula (A1/B1).
- Now, type a comma, and in the value_if_error argument, enter what you want to show instead of the error: a 0, a blank (“”), or a short message in double quotes.
- In the end, type the closing parenthesis and press Enter.
Now the cell shows a readable result instead of #DIV/0!. Keep in mind that IFERROR hides every error, not just #DIV/0!. If the formula breaks for another reason, such as a misspelled function name (#NAME?), you won’t see that error either.
Using IF to Fix #DIV/0! Error
You can also use IF with the ISERROR function to test for an error and replace it with something else. This was the standard method before IFERROR arrived in Excel 2007, and it works in every version.
In the example above, I used IF and ISERROR together. In the first argument of IF, ISERROR tests whether dividing A1 by B1 returns an error.
If there’s an error, IF returns the value you specified. If there’s no error, it divides the value in A1 by the value in B1.
Like IFERROR, this method hides every error. If you only want to handle division by zero and still see other errors, test the divisor instead: =IF(B1=0,"",A1/B1). A blank B1 also counts as 0 in this test, so it covers empty cells too.
Get the Excel File
Frequently Asked Questions
Check the cell you’re dividing by. If it’s 0 or blank by mistake, enter the correct value and the error goes away. If the zero is expected (for example, no sales yet), handle it in the formula instead, as explained in the next question.
Wrap the formula in IFERROR, like =IFERROR(A1/B1,0), to show a value of your choice instead of the error. Or test the divisor first with IF, like =IF(B1=0,"",A1/B1), which handles only division by zero and still shows any other error.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.