Excel #DIV/0! error
The formula divides by zero or by an empty cell.
Why it appears
- The divisor is empty because that data is not entered yet.
- You copied the formula and the total, which should have stayed put, moved.
- AVERAGEIF found no row that meets the condition.
How to fix it
- Anchor the divisor with $ if it is a total: =B2/$B$7.
- Show another value while there is no data: =IFERROR(B2/C2,0).
- Or check first: =IF(C2=0,"",B2/C2).
Gives the error
=B2/C2
Fixed
=IFERROR(B2/C2,0)
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
IFERROR
IFERROR(formula, value) returns the formula’s result or, if it errors (#DIV/0!, #N/A…), the value you choose. Use it to keep your tables free of errors.
In D2, divide revenue by units. When no units were sold, show 0 instead of the error.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | Units | Average price | |
| 2 | Coffee | 4,500 | 30 | ||
| 3 | Tea | 0 | 0 | ||
| 4 | Chocolate | 2,400 | 16 | ||
| 5 | Cookies | 900 | 0 | ||
| 6 | Bread | 1,300 | 26 | ||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What does #DIV/0! mean in Excel?
- The formula divides by zero or by an empty cell.
- How do I get rid of the #DIV/0! error?
- Anchor the divisor with $ if it is a total: =B2/$B$7. Show another value while there is no data: =IFERROR(B2/C2,0). Or check first: =IF(C2=0,"",B2/C2).
- What is #DIV/0! called in Spanish Excel?
- Spanish Excel shows it as #¡DIV/0!.