Excel #DIV/0! Error: Divide by Zero Fixes
By Szabó Gergő · Updated
#DIV/0! means a formula divided by zero or by a blank that Excel treats as zero. Guard the denominator before you hide the error.
Syntax and arguments
=IF(denominator=0, fallback, numerator/denominator)- denominator
- The cell that must not be zero or blank when used as a divisor.
- fallback
- 0, blank, or a label when a rate is not defined.
#DIV/0! examples
Revenue B2, units C2.
=IF(C2=0,"",B2/C2)A blank result is clearer than #DIV/0! on rows that have no units yet.
Done D2, total E2.
=IF(E2=0,0,D2/E2)Returning 0 lets you chart the column as a number.
Several nested rates.
=IFERROR(B2/C2,0)Use after you accept that any error in this cell should become zero.
Common mistakes
Treating blank as a missing rate but storing 0 as real volume
Decide whether blank and zero mean the same thing. Test with C2="" and C2=0 separately if they do not.
Dividing by a SUM that can be empty
Wrap the SUM: =IF(SUM(C:C)=0,0,B2/SUM(C:C)).
Using IFERROR so early you hide #VALUE!
Prefer IF on the denominator so other errors still show.
#DIV/0! FAQ
Is a blank cell zero in division?
Yes. Excel treats blank as 0 in arithmetic, which triggers #DIV/0!.
Should rates be blank or zero when undefined?
Blank is better for averages that should ignore the row. Zero is better for sums and charts that need a number.
Does AVERAGE cause #DIV/0!?
AVERAGE of a fully empty range returns #DIV/0!. Point it at numeric cells or use IF.