Excel errors / #DIV/0!
#DIV/0! error in Excel
The formula divides by zero or by an empty cell.
#DIV/0! means a number is being divided by zero. An empty cell counts as zero, so the usual cause is not a zero that someone typed but a cell that has not been filled in yet: next month's units, a student with no scores.
It also comes from AVERAGE and its relatives, which divide a total by a count. With nothing to count, they divide by zero.
Have a formula that shows #DIV/0!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
3 causes of #DIV/0!, and how to fix each
Dividing by an empty cell or a zero
A price per unit is sales divided by units. Where no units are entered yet, there is nothing to divide by.
A B C D 1 Sales Units 2 100 #DIV/0! In D2:
=A2/B2→ #DIV/0!How to fix it. Test the divisor first, and decide what to show when it is zero or empty.
A B C D 1 Sales Units 2 100 n/a In D2:
=IF(B2=0, "n/a", A2/B2)→ n/aAVERAGE with no numbers to average
AVERAGE adds the numbers and divides by how many there are. On a range with no numbers in it, that count is zero.
A B C 1 Score 2 #DIV/0! 3 In C2:
=AVERAGE(A2:A3)→ #DIV/0!How to fix it. Check that there is something to average before averaging it.
A B C 1 Score 2 No scores yet 3 In C2:
=IF(COUNT(A2:A3)=0, "No scores yet", AVERAGE(A2:A3))→ No scores yetAVERAGEIF with nothing that matches
AVERAGEIF and AVERAGEIFS average only the rows that meet a condition. When no row does, they have nothing to divide by.
A B C D 1 Region Sales 2 North 100 #DIV/0! 3 South 80 In D2:
=AVERAGEIF(A2:A3, "East", B2:B3)→ #DIV/0!How to fix it. Wrap it in IFERROR and say what to show when there is no match.
A B C D 1 Region Sales 2 North 100 No sales 3 South 80 In D2:
=IFERROR(AVERAGEIF(A2:A3, "East", B2:B3), "No sales")→ No sales
Hiding it on purpose
=IFERROR(A2/B2, 0) replaces the error with 0, and is the shortest fix. Use it with care: it hides every other error too, and a 0 can pass for a real result in a total or a chart. Testing the divisor with IF says exactly what you mean.
Common questions
How do I get rid of #DIV/0! in Excel?
Test the cell you divide by before dividing: =IF(B2=0, "", A2/B2) leaves the cell looking empty until B2 has a value. =IFERROR(A2/B2, "") is shorter but hides other errors as well.
How do I show 0 instead of #DIV/0!?
Use =IFERROR(A2/B2, 0), or =IF(B2=0, 0, A2/B2) to catch only the division by zero.
Why does AVERAGE return #DIV/0!?
There are no numbers in the range. Either it is empty, or its numbers are stored as text, which AVERAGE skips. =COUNT(A2:A20) tells you how many numbers Excel sees.
Practise it
Functions involved
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
- IFERROR: Returns a fallback value if a formula results in an error.
- AVERAGE: The arithmetic mean of the numbers.
- AVERAGEIF: Averages the cells that meet a condition.
- COUNT: Counts cells that contain numbers.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.