Excel shows #DIV/0! when a formula divides a number by zero or by an empty cell treated as zero.
Learn how to locate the cause and choose a fix that preserves useful results instead of hiding errors indiscriminately.
What causes the #DIV/0! error in Excel?
The division operator (/) needs a nonzero divisor.
If a formula’s denominator evaluates to zero, Excel returns #DIV/0!.
A blank cell used as the divisor has the same effect.
For example, if cell A2 contains 24 and B2 is blank or contains 0, the formula =A2/B2 returns #DIV/0!.
The same error can appear in formulas that calculate averages, rates, or percentages when their denominator is zero.
How to find the source of the error
Start with the cell displaying the error.
Select it and inspect the formula bar to identify the division and its denominator.
For a formula such as =C2/D2, check the value in D2.
It may be zero, blank, or calculated by another formula that returns zero.
- Use Formulas > Evaluate Formula to step through a complex formula.
- Use Trace Precedents on the Formulas tab to see which cells feed into the result.
- Check whether the denominator is a formula, such as
=B2-B2, that evaluates to zero. - For copied formulas, verify that relative and absolute references point to the intended cells.
Fix the formula by checking the denominator
If a zero denominator means there is no meaningful result yet, use IF to return a blank.
For example, if A2 contains the numerator and B2 contains the denominator:
=IF(B2=0,"",A2/B2)
When B2 is zero or blank, the formula returns an empty-looking cell.
When B2 is 4 and A2 is 24, it returns 6.
Use this approach when the denominator is a direct cell reference and you want the formula to make the zero case explicit.
If blank and zero have different meanings, test for blanks separately.
This formula returns Not entered for a blank denominator, N/A for a numeric zero, and otherwise divides:
=IF(B2="","Not entered",IF(B2=0,"N/A",A2/B2))
That distinction is useful in data-entry sheets, although a cell containing a formula that returns "" can also appear blank.
Decide whether that should count as missing input in your workbook.
Use IFERROR when any formula error needs a fallback
IFERROR catches #DIV/0! and other errors, including #VALUE! and #N/A.
To show a message for any error in the division, use:
=IFERROR(A2/B2,"Check denominator")
If A2 is 24 and B2 is 0, the result is Check denominator; if B2 is 4, the result is 6.
This is concise, but it can conceal errors unrelated to division by zero.
Use it when the same fallback is appropriate for all errors the expression might produce.
For newer Excel versions, IFNA handles only #N/A; it does not catch #DIV/0!.
To specifically handle a zero divisor while allowing other errors to remain visible, test the denominator with IF.
Fix common average and percentage errors
An average can return #DIV/0! when there are no numeric values to average.
For a range such as A2:A10, use:
=IF(COUNT(A2:A10)=0,"",AVERAGE(A2:A10))
COUNT counts numeric cells.
If there are none, the formula returns a blank; otherwise, it calculates the average.
This also avoids an error when the range contains only text or blanks.
A percentage formula can fail when the total is zero.
If B2 is the part and C2 is the total, use =IF(C2=0,"",B2/C2) and format the result as a percentage.
With B2 equal to 3 and C2 equal to 12, the result is 25%.
A zero total has no defined percentage, so choose a blank, message, or another documented value that fits the report.
Excel and Google Sheets compatibility
The formulas in this article use standard functions available in current desktop Excel, Excel for the web, and Google Sheets.
IF, IFERROR, COUNT, and AVERAGE are supported across these spreadsheet apps.
Older Excel releases may use semicolons instead of commas between function arguments, depending on regional settings.
For example, enter =IF(B2=0; ""; A2/B2) if your installation expects semicolons.
Common mistakes to avoid
- Replacing every error with zero: A zero can look like a real measurement and distort totals or averages.
Use a blank or clear message when no result is defined. - Checking the wrong cell: The divisor may come from a calculation or a referenced cell several steps away.
Trace the formula’s inputs. - Using a text placeholder in numeric calculations: A result such as
Check denominatoris text, so downstream calculations may produce another error.
Keep output columns numeric if other formulas depend on them. - Ignoring formula-filled blanks: A cell displaying nothing may contain a formula returning
"".
Decide whether that should be treated as missing data or as zero for your calculation.
Which fix should you choose?
Use a denominator check when zero has a specific meaning and you want to handle it deliberately.
Use IFERROR when any error in the calculation should produce the same fallback.
For averages, check whether the range contains numeric values; for percentages, decide how a zero total should appear.
These choices keep the worksheet understandable and help prevent an error from being mistaken for a valid result.