How to fix or hide #DIV/0! in Excel
=IF(C2=0,"",B2/C2) leaves the cell blank until there's a number to divide by. An empty C2 counts as 0, so this also covers rows you haven't filled in yet.Causes and fixes
- Dividing by zero or an empty cell
- Catching it with IFERROR (and when not to)
- AVERAGE or AVERAGEIF with nothing to average
- Percent change from zero
- #DIV/0! in a PivotTable
1Dividing by zero or an empty cell
This is the usual cause: a ratio, average price or rate column where some rows don't have a divisor yet. Excel treats an empty cell as 0.
How to recognise it
The formula divides by a cell (=B2/C2) and that cell is 0 or empty.
Fix
Test the divisor first and show what you want when it's 0: a blank, a 0, or a short note.
=IF(C2=0,"",B2/C2)
=IF(C2=0,0,B2/C2)
=IF(C2=0,"No orders",B2/C2)A blank made with "" is text. If other formulas do math on this column, use SUM or AVERAGE (which skip text) rather than +, or show 0 instead.
Try it live: Average order value with missing orders
D2:D6 divide sales by orders. C4 is 0 and C5 is empty, so both show #DIV/0!. Click an example to fill the column with the IF or IFERROR version.
2Catching it with IFERROR (and when not to)
IFERROR is shorter and catches every error:
=IFERROR(B2/C2,0)The catch is that it also hides errors you'd want to see, such as #VALUE! from a typo or #REF! from a deleted column. For division, the IF version above is more precise. Use IFERROR when any error should give the same fallback.
3AVERAGE or AVERAGEIF with nothing to average
How to recognise it
=AVERAGEIF(A2:A100,"East",B2:B100) returns #DIV/0! when no row says East. =AVERAGE(B2:B100) does the same when the range is empty or holds only text.
Fix
Check that there's something to average first:
=IF(COUNTIF(A2:A100,"East")=0,"",AVERAGEIF(A2:A100,"East",B2:B100))Or catch the error:
=IFERROR(AVERAGEIF(A2:A100,"East",B2:B100),"No data")If the range should have numbers but AVERAGE still fails, they may be stored as text. See why SUM shows 0.
4Percent change from zero
Growth formulas like =(C2-B2)/B2 fail when last period was 0. There's no meaningful percentage when you start from nothing, so show a label instead:
=IF(B2=0,"n/a",(C2-B2)/B2)5#DIV/0! in a PivotTable
Calculated fields and "show values as" options can divide by zero too.
Fix
Right-click the PivotTable, choose PivotTable Options, and on the Layout & Format tab tick For error values show. Leave the box empty for a blank, or type 0 or a dash.
Still stuck?
- Is the divisor 0, empty, or a formula that returns 0?
- Does AVERAGEIF have at least one matching row? Check with COUNTIF.
- Do numbers in the range look right but are stored as text? Check with
=COUNT(). - Prefer IF over IFERROR when you only want to handle division by zero.