Excel practice › Excel errors › #DIV/0! error
#DIV/0! error in Excel: what it means and how to fix it
#DIV/0! means a formula divided by zero, or by an empty cell, which Excel counts as zero. The formula isn't broken: the same one works on every row where the divisor holds a number.
If a zero or an empty divisor is normal in your data, like a campaign with no clicks yet, tell the formula what to show for those rows. =IF(B5=0,0,C5/B5) divides only when there is something to divide by. =IFERROR(C5/B5,0) is shorter, and hides every other error too.
The causes, in the order worth checking
- The divisor is 0.
- The divisor is an empty cell, often a row that isn't filled in yet.
- The formula divides by the wrong cell, usually after being copied down.
- AVERAGE over cells with no numbers in them.
- AVERAGEIF or AVERAGEIFS with no row that meets the condition.
1 and 2. The divisor is 0, or empty
Conversion rate is orders divided by clicks. The podcast spot has had no clicks yet:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Orders | Revenue | Conv. rate |
| 2 | Spring email | 1240 | 62 | 4030 | 0.05 |
| 3 | Search ads | 3100 | 93 | 6510 | 0.03 |
| 4 | Referral | 410 | 41 | 3280 | 0.1 |
| 5 | Podcast spot | 0 | 0 | 0 | #DIV/0! |
| 6 | Trade show | 18 | 2700 |
=IFERROR(C5/B5,0).3. The formula divides by the wrong cell
Each region's share is its sales divided by the total in B6. C2 holds =B2/B6 and works. Copied down one row, both references moved, and the divisor is now B7, an empty cell under the total:
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | East | 48000 | 0.3 |
| 3 | South | 32000 | #DIV/0! |
| 4 | Midwest | 24000 | |
| 5 | West | 56000 | |
| 6 | Total | 160000 |
=B3/$B$6 returns 0.2. Here an IF or an IFERROR would be the wrong fix: it would hide a formula that points at the wrong cell.4. AVERAGE over cells with no numbers
AVERAGE adds the numbers in a range and divides by how many there are. It skips empty cells and text, so over a range with no numbers at all it divides by zero. The Design team hasn't been scored yet:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Team | Q1 score | Q2 score | Average |
| 2 | Support | 4.2 | 4.5 | 4.35 |
| 3 | Sales | 3.8 | 4.1 | 3.95 |
| 4 | Design | #DIV/0! | ||
| 5 | Ops | 4.6 | 4.4 | 4.5 |
=IFERROR(AVERAGE(B4:C4),"No scores").5. AVERAGEIF with no matching row
AVERAGEIF and AVERAGEIFS return #DIV/0! when no row meets the condition. Here, the average revenue of campaigns with more than 5,000 clicks, when no campaign has that many:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Orders | Revenue | Avg revenue | |
| 2 | Spring email | 1240 | 62 | 4030 | #DIV/0! | |
| 3 | Search ads | 3100 | 93 | 6510 | ||
| 4 | Referral | 410 | 41 | 3280 | ||
| 5 | Podcast spot | 0 | 0 | 0 | ||
| 6 | Trade show | 18 | 2700 |
=IFERROR(AVERAGEIF(B2:B6,">5000",D2:D6),"None").IF or IFERROR
Both remove the error from the cell. They don't do the same thing.
| Formula | What it does |
|---|---|
=IF(B5=0,0,C5/B5) | Tests the divisor. Divides only when B5 isn't 0 or empty. Any other error in the formula still shows. |
=IFERROR(C5/B5,0) | Divides, and replaces any error with 0: #DIV/0!, but also #VALUE!, #REF! and #NAME?. |
Use IF when division by zero is the one case you expect. It leaves a mistyped reference or a text cell visible. Use IFERROR for averages, where there is no single cell to test, and when the formula is long. Microsoft writes the IF test in a shorter form, =IF(B5,C5/B5,0): a divisor of 0 or an empty one counts as FALSE.
Then decide what to show. A 0 says "no orders per click", which isn't what an untested campaign means, and it pulls averages down. An empty text ("") or a short note is often the more honest choice.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Orders | Revenue | Conv. rate |
| 2 | Spring email | 1240 | 62 | 4030 | 0.05 |
| 3 | Search ads | 3100 | 93 | 6510 | 0.03 |
| 4 | Referral | 410 | 41 | 3280 | 0.1 |
| 5 | Podcast spot | 0 | 0 | 0 | No clicks |
| 6 | Trade show | 18 | 2700 |
=C5/B5 to see the error come back.Practice: 3 formulas to fix
Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.
=C5/B5 shows #DIV/0! for the podcast spot, which has 0 clicks. In E5, write a formula that returns the conversion rate (orders divided by clicks), or the text No clicks when the division isn't possible.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Orders | Revenue | Conv. rate |
| 2 | Spring email | 1240 | 62 | 4030 | 0.05 |
| 3 | Search ads | 3100 | 93 | 6510 | 0.03 |
| 4 | Referral | 410 | 41 | 3280 | 0.1 |
| 5 | Podcast spot | 0 | 0 | 0 | |
| 6 | Trade show | 18 | 2700 |
Show the answer
=IFERROR(C5/B5,"No clicks") gives No clicks.
IFERROR returns the result of the division when there is one, and your text when the division ends in an error. =IF(B5=0,"No clicks",C5/B5) does the same and leaves other errors visible.
C2 holds =B2/B6. It was copied down, and rows 3 and 4 were then corrected by hand. C5 still holds the copied formula, =B5/B9, and shows #DIV/0!. In C5, write the formula for the West's share of the total, with the total locked so it can be copied.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | East | 48000 | 0.3 |
| 3 | South | 32000 | 0.2 |
| 4 | Midwest | 24000 | 0.15 |
| 5 | West | 56000 | |
| 6 | Total | 160000 |
Show the answer
=B5/$B$6 gives 0.35.
$B$6 stays on the total wherever the formula is copied, and B5 keeps following its own row.
=AVERAGEIF(B2:B6,">"&F2,D2:D6) averages the revenue of campaigns with more clicks than the number in F2. With 5000 in F2 no campaign qualifies, and it shows #DIV/0!. In G2, write a formula that returns the average, or the text None when no campaign qualifies.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Campaign | Clicks | Orders | Revenue | Min clicks | Avg revenue | |
| 2 | Spring email | 1240 | 62 | 4030 | 5000 | ||
| 3 | Search ads | 3100 | 93 | 6510 | |||
| 4 | Referral | 410 | 41 | 3280 | |||
| 5 | Podcast spot | 0 | 0 | 0 | |||
| 6 | Trade show | 18 | 2700 |
Show the answer
=IFERROR(AVERAGEIF(B2:B6,">"&F2,D2:D6),"None") gives None.
AVERAGEIF has no divisor you can test in a cell, so IFERROR is the practical fix: it returns the average when rows match and your text when none do.
Questions people ask
What does #DIV/0! mean in Excel?
The formula divides by zero. An empty cell counts as zero, so dividing by a cell that isn't filled in gives the same error. AVERAGE, AVERAGEIF and AVERAGEIFS show it too when they have no numbers to average.
How do I get rid of #DIV/0! in Excel?
First check that the formula divides by the right cell. If it does, and a zero or an empty divisor is expected, handle it: =IF(B5=0,0,C5/B5) or =IFERROR(C5/B5,0). Replace the 0 with "" for a cell that looks empty.
Why does AVERAGE return #DIV/0!?
There are no numbers in the range. AVERAGE skips empty cells and text, and with nothing left it divides by a count of zero. AVERAGEIF does the same when no row meets its condition. Wrap it in IFERROR to show something in that case.
Should I use IF or IFERROR to fix #DIV/0!?
IF when you can test the divisor: it handles the zero and leaves other errors visible. IFERROR when there is nothing simple to test, as with AVERAGEIF. IFERROR hides every error, so make sure the formula works on normal rows before you add it.