#DIV/0! in Excel: what it means and how to fix it
#DIV/0! means you're dividing by zero or by an empty cell.
Try the first exercises for freeIn the lesson Understanding error messages
What does #DIV/0! mean?
#DIV/0! means that you're dividing by zero. That also happens if the cell you're dividing by is empty, because to Excel an empty cell counts as 0.
Dividing by zero has no result, and Excel would rather show that than a wrong number. You also see the error with AVERAGE if there isn't a single number in the range, and with AVERAGEIF if no row meets the condition.
When do you get #DIV/0!?
- You calculate an amount per visitor, but the number of visitors is still empty: =A2/B2.
- You calculate growth compared with a previous year without revenue: =(B2-A2)/A2 with 0 in A2.
- You take the average of a column that doesn't contain anything yet.
- AVERAGEIF doesn't find a single row that meets your condition.
How to fix #DIV/0!, step by step
This is how to fix the error:
- Click the cell with the error and see what you're dividing by: that's what comes after the division sign /.
- Is there a 0 in that cell, or is it empty? Fill it in if it should be.
- If it's normal for that cell to be empty sometimes, catch it with IF: show nothing as long as there's nothing to divide.
- Or use IFERROR to show a 0 or a text instead of the error.
Examples
The error
C2 contains =A2/B2. B2 is empty, and that counts as 0.
Catching it with IF
C2 contains =IF(B2=0,"",A2/B2). As long as B2 is empty or 0, the cell stays empty. Type 60 in B2, and you get 20.
Catching it with IFERROR
C2 contains =IFERROR(A2/B2,0). IFERROR shows 0 instead of the error.
Where it often goes wrong
- AVERAGE gives the same error if there isn't a single number in the range.
Practise this in the lesson Understanding error messages. The first exercises are free.
Frequently asked questions
Why isn't it just 0?
Dividing by zero has no answer. Excel shows that, so you don't draw the wrong conclusion.