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

  1. 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.

    ABCD
    1SalesUnits
    2100#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.

    ABCD
    1SalesUnits
    2100n/a

    In D2: =IF(B2=0, "n/a", A2/B2) → n/a

  2. AVERAGE 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.

    ABC
    1Score
    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.

    ABC
    1Score
    2No scores yet
    3

    In C2: =IF(COUNT(A2:A3)=0, "No scores yet", AVERAGE(A2:A3)) → No scores yet

  3. AVERAGEIF 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.

    ABCD
    1RegionSales
    2North100#DIV/0!
    3South80

    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.

    ABCD
    1RegionSales
    2North100No sales
    3South80

    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

Other errors