How to Fix the #DIV/0! Error in Excel
The #DIV/0! error means a formula is dividing by zero or by an empty cell. The fix is to correct the denominator, or wrap the formula in IFERROR or IF so it returns a blank or a message instead. Here is how.
The #DIV/0! error is Excel telling you a formula tried to divide by zero — which is not a valid calculation. It usually shows up when the number you are dividing by is zero, or the cell it points to is empty.
What causes #DIV/0!
- A formula like =A2/B2 where B2 is 0 or empty.
- An average of a range that has no numbers yet, such as =AVERAGE(B2:B10) on empty cells.
- A cell referenced in the denominator that gets cleared later, turning a working formula into an error.
The quick fix: handle it with IFERROR
If the error appears simply because data has not been entered yet, wrap the formula in IFERROR so it shows a blank (or a message) instead of #DIV/0!:
=IFERROR(A2/B2, "")The two quotation marks return an empty cell. You could put text there instead, such as =IFERROR(A2/B2, "No data").
The precise fix: test with IF
To handle only the zero case and leave real errors visible, check the denominator first with an IF function:
=IF(B2=0, "", A2/B2)This says: if B2 is zero, return blank; otherwise divide normally. It is more precise than IFERROR because it only reacts to the zero, not to every possible error.
Or just fix the data
Sometimes the honest fix is not a formula at all — the denominator should not be zero. Check whether a value is missing, a total is wrong, or a cell was cleared by accident. A #DIV/0! is often a useful warning that data is incomplete. For other common formula errors, see how to fix the #VALUE! error and how to fix the #REF! error.
Frequently asked questions
How do I hide #DIV/0! but keep the formula?
Wrap it in IFERROR, like =IFERROR(A2/B2, ""). The formula still works once valid data is entered, but shows a blank instead of the error while the denominator is zero or empty.
Why does AVERAGE give a #DIV/0! error?
AVERAGE divides the total by the count of numbers. If the range has no numeric values yet, the count is zero and you get #DIV/0!. Once at least one number is entered, the error clears on its own.
Is #DIV/0! a mistake I should always hide?
Not always. It can be a helpful signal that data is missing. Hide it with IFERROR or IF only when a zero denominator is expected and you want a clean sheet — otherwise, fix the underlying data.