#DIV/0! error: what it means and how to fix it
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
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/B2Fixed
=IF(B2=0,"",C2/B2) - 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/B10Fixed
=B2/$B$10 - 3
Averaging a range with no numbers
AVERAGEdivides 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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Sales | Conversion | Owner |
| 2 | Spring email | 1200 | 36 | 3.0% | Mara |
| 3 | 860 | 12 | 1.4% | ||
| 4 | Display ads | 0 | 0 | #DIV/0! | Jonas |
| 5 | Search ads | 2300 | 69 | 3.0% | Priya |
| 6 | Newsletter | 540 | 27 | 5.0% | |
| 7 | Partner blog | 0 | 0 | #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”Need a quick reference? The formula cheat sheet is free to read and print.