Spreadsheet error
How to fix the #DIV/0! error
TL;DR
#DIV/0! means the formula divided by zero or by an empty cell. Fix: wrap in IFERROR, or guard with IF(denominator=0, 0, numerator/denominator).
What it means
The #DIV/0! error appears when a division operation has zero (or an empty cell, which Excel treats as zero) as the denominator. AVERAGE on an empty range also triggers this.
Common causes
- •Dividing by a cell that is empty or contains zero.
- •AVERAGE or AVERAGEIF where the criteria matches nothing.
- •Percentage calculations where the base is zero.
- •Formulas dragged to rows that do not have data yet.
How to fix it
1.Wrap in IFERROR
- Change =A2/B2 to =IFERROR(A2/B2, 0)
- Or return empty string: =IFERROR(A2/B2, "")
- Or a message: =IFERROR(A2/B2, "N/A")
2.Guard with IF
- =IF(B2=0, 0, A2/B2)
- More explicit than IFERROR because it only handles the zero case and not other unrelated errors.
3.For AVERAGE, check the range
- If AVERAGEIF returns #DIV/0!, the criteria matched no cells.
- Wrap: =IFERROR(AVERAGEIF(A:A, "x", B:B), 0)
- Or pre-check count: =IF(COUNTIF(A:A, "x")=0, 0, AVERAGEIF(A:A, "x", B:B)).
Example
Broken
=A2/B2Fixed=IFERROR(A2/B2, 0)If B2 is empty or zero, the division fails with #DIV/0!. IFERROR returns 0 instead.
Prevention
- For ratios, decide what zero denominator should mean (0, N/A, blank) and encode it with IF or IFERROR.
- Use data validation to require non-zero values in denominator columns.
Related errors
Let FormulaPad debug it for you
Paste your broken formula and the error message — get a diff and an explanation in seconds.
Debug a formula