formulastreak

#DIV/0! error: what it means and how to fix it

#DIV/0!

A number is being divided by zero or by an empty cell.

#DIV/0! appears when a formula divides by 0, or by a cell that's empty (an empty cell counts as 0 in maths). Averages of nothing do it too, such as AVERAGE of a range with no numbers.

Common causes, ranked

  1. 1

    The divisor cell is 0 or empty

    A rate with no clicks yet, a price not filled in, a row that hasn't been entered.

    Fix: Fill in the missing data, or test the divisor first and show something else when it's 0.

    Shows the error

    =C2/B2

    Fixed

    =IF(B2=0,"",C2/B2)
  2. 2

    The formula points at the wrong cell

    A divisor that slid when the formula was filled down now points at an empty row.

    Fix: Lock the divisor with $ when every row divides by the same cell.

    Shows the error

    =B2/B10

    Fixed

    =B2/$B$10
  3. 3

    Averaging a range with no numbers

    AVERAGE divides by how many numbers there are. With none, that's dividing by zero.

    Fix: Check that the range is right and has numbers in it.

See it live

Edit the formula in D2 and fix the error yourself.

Two campaigns have 0 clicks, so their conversion rate is #DIV/0! · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = 3.0%
ABCDE
1CampaignClicksSalesConversionOwner
2Spring email1200363.0%Mara
3Instagram860121.4%
4Display ads00#DIV/0!Jonas
5Search ads2300693.0%Priya
6Newsletter540275.0%
7Partner blog00#DIV/0!Kenji

How to hide it safely

When a zero divisor is expected (no data yet), test for it with IF. It only reacts to that one cause:

=IF(B2=0,"",C2/B2)

Careful: =IFERROR(C2/B2,0) also works, but Microsoft notes it suppresses all errors, not just #DIV/0!. A typo in the formula would show 0 too, and a 0 looks like a real result.

Excel vs Google Sheets

#DIV/0! is an error value in both. In Excel, IFERROR needs both arguments; in Google Sheets the second is optional and leaves the cell blank when you skip it.

Sources

Practise this in 5 minutes

Fixing #DIV/0! is taught in Lesson 14: Errors and blank cells (Unit 3, Decisions), on a live sheet with instant feedback. Pro

Open “Errors and blank cells”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.