#DIV/0! appears when a formula divides by zero or by an empty cell (an empty cell counts as 0). =B2/C2 returns #DIV/0! while C2 is 0 or blank. =IF(C2=0,"",B2/C2) checks the divisor first and leaves the cell empty instead.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Calls | Per call | Per call (checked) |
| 2 | Ana | 1200 | 40 | 30 | 30 |
| 3 | Ben | 900 | 0 | #DIV/0! | |
| 4 | Cy | 600 | #DIV/0! | ||
| 5 | Dee | 750 | 25 | 30 | 30 |
#DIV/0! The formula divides by zero or by an empty cell.D3 and D4 show #DIV/0!: Ben has 0 calls, and Cy's cell is empty. Column E shows 30 and 30 for Ana and Dee and nothing for the other two. Type a number into C4 and both columns fill in.
The error is often correct: dividing by zero has no answer, and here it means the data is not there yet. A blank or a 0 makes the sheet tidier, but decide which one is honest: AVERAGE counts a 0 and skips a blank "".
IF or IFERROR to fix #DIV/0!
Both formulas remove the error. They differ in what else they hide.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Calls | IF(C=0) | IFERROR |
| 2 | Ana | 1200 | 40 | 30 | 30 |
| 3 | Ben | 900 | 0 | 0 | 0 |
| 4 | Cy | 600 | 0 | 0 | |
| 5 | Dee | n/a | 25 | #VALUE! | 0 |
For Ben and Cy both show 0. Dee's sales hold the text n/a, a data error: the IF version shows #VALUE! and points you at it, the IFERROR version shows 0 as if Dee had sold nothing. Use IF(C2=0,...) when zero is the only problem you expect, and IFERROR when any error should give the same fallback. The IFERROR page has more on this choice.
The check can also be written =IF(C2,B2/C2,0): a number other than 0 counts as TRUE. It is shorter and harder to read.
#DIV/0! with AVERAGE and AVERAGEIF
AVERAGE divides a sum by a count. With no numbers to count, it divides by zero.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rep | Average | ||
| 2 | Ana | 1200 | Dan | #DIV/0! | ||
| 3 | Ben | 900 | #DIV/0! | |||
| 4 | Ana | 800 | ||||
| 5 | Cy | 600 | ||||
| 6 | Ben | 700 |
#DIV/0! The formula divides by zero or by an empty cell.Dan has no sales rows, so AVERAGEIF in F2 has nothing to average. F3 averages the empty column C, with the same result. Change E2 to Ana and F2 shows 1000, the average of 1200 and 800. Two ways to handle the empty case in Excel:
=IFERROR(AVERAGEIF(A2:A6,E2,B2:B6),0)
=IF(COUNTIF(A2:A6,E2)=0,"no sales",AVERAGEIF(A2:A6,E2,B2:B6))
The second one says why the result is missing. AVERAGEIFS and a plain =SUM(B2:B6)/COUNT(B2:B6) behave the same way. See AVERAGEIF for its criteria.
#DIV/0! in a percent change
Percent change divides by the old value. When the old value is 0, there is no percentage that describes the change.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | 2025 | 2026 | Change | Change (labelled) |
| 2 | Desk | 200 | 240 | 20% | 20% |
| 3 | Chair | 150 | 120 | -20% | -20% |
| 4 | Lamp | 0 | 80 | #DIV/0! | new |
| 5 | Shelf | 90 | 90 | 0% | 0% |
#DIV/0! The formula divides by zero or by an empty cell.The desk grew 20% and the chair fell 20%. The lamp sold nothing in 2025, so D4 is #DIV/0! and E4 says new. Text in a column of percentages stays text, so SUM and AVERAGE skip it. The percent change page covers the formula itself.
Fix an average that shows #DIV/0!
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rep | Average | ||
| 2 | Ana | 1200 | Dan | |||
| 3 | Ben | 900 | ||||
| 4 | Ana | 800 | ||||
| 5 | Cy | 600 | ||||
| 6 | Ben | 700 |
Your turn: E2 holds a rep with no sales, so =AVERAGEIF(A2:A6,E2,B2:B6) returns #DIV/0!. Write a formula in F2 that returns the average sale of the rep in E2, or 0 when the rep has no sales.
Check also runs your formula with Ana in E2, so a formula that returns 0 without averaging fails. =IF(COUNTIF(A2:A6,E2)=0,0,AVERAGEIF(A2:A6,E2,B2:B6)) passes too.
Frequently Asked Questions
What does #DIV/0! mean in Excel?
The formula divided a number by zero, or by an empty cell, which Excel treats as zero. =B2/C2 returns #DIV/0! while C2 is 0 or blank. AVERAGE, AVERAGEIF and MOD return it too when they end up dividing by zero.
How do I make Excel show 0 instead of #DIV/0!?
Test the divisor first: =IF(C2=0,0,B2/C2). For a blank cell instead of 0, use =IF(C2=0,"",B2/C2). =IFERROR(B2/C2,0) is shorter but also turns every other error in the formula into 0.
Why does my total show #DIV/0!?
One of the cells it adds holds #DIV/0!, and SUM passes errors on. Fix the division in that cell with =IF(C2=0,"",B2/C2), or add around the errors with =AGGREGATE(9,6,D2:D10), which sums D2:D10 and skips error values.
Does an empty cell cause #DIV/0! in Excel?
Yes. A blank cell counts as 0 in a calculation, so =B2/C2 returns #DIV/0! while C2 is empty. =IF(C2=0,"",B2/C2) catches both a 0 and a blank, because an empty cell equals 0 in a comparison.
