How to fix the #DIV/0! error in Excel
#DIV/0! appears when a formula divides by zero or by a blank cell. It also shows up when AVERAGE, AVERAGEIF or AVERAGEIFS have nothing to average.
#DIV/0!
Why it happens
Dividing by a blank cell
=F2/G2 when G2 hasn’t been filled in yet.
Dividing by zero
Growth formulas like =(new-old)/old fail when old is 0.
Averages with no matches
=AVERAGEIF(B2:B9,"South",F2:F9) returns #DIV/0! if there are no South orders.
How to fix it
Check the divisor first
=IF(D2=0,"",F2/D2) → 12
Hide it with IFERROR
=IFERROR(F2/G2,0) → 0
A share of total that can’t fail
=F2/SUM(F2:F9) → 0.0730593607
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Tip: Returning "" (blank) instead of 0 keeps charts and averages from being dragged down by fake zeros.
Other Excel errors
- #N/A“Not available”: a lookup couldn’t find what you asked for.
- #VALUE!A formula got the wrong kind of data, usually text where it needed a number.
- #REF!The formula points at a cell that no longer exists.
- #NAME?Excel doesn’t recognise a word in the formula.
- #SPILL!A formula that returns several results has no room to spill into.
- #NUM!The math is impossible or a number is out of range.