How to Hide Divide by Zero Errors in Excel

To hide a divide-by-zero error in Excel, wrap the division in IFERROR, or use an IF test when you want to suppress only a zero or blank denominator. For example, with the numerator in A2 and denominator in B2, =IFERROR(A2/B2,"") displays a blank instead of any error. The steps below show how to choose the right approach, apply it to a range, and avoid hiding problems you still need to fix.

What You’ll Need

An Excel workbook with a formula that divides one value by another. These instructions are for Microsoft Excel for Microsoft 365 on Windows.
The cell or range containing the formula, plus a decision about what should appear when the denominator is zero: a blank, zero, or a message.
Optional: a copy of the original formula or a duplicate worksheet, especially if the workbook contains important calculations.

No add-ins or special settings are required. The formulas work in current desktop Excel and Excel for the web; the formula syntax is the same on Mac. Ribbon labels and some keyboard shortcuts can differ by platform.

Step-by-Step

Find the formula producing the error. Select a cell showing #DIV/0! and check the formula bar. A divide-by-zero error usually means the formula’s denominator is zero or refers to an empty cell. For example, a formula dividing A2 by B2 will return the error if B2 is 0 or blank. Identify the numerator and denominator references before changing anything; do not assume the error is in the cell you expect if the formula refers to another sheet or a calculated result.
Choose what the cell should show instead. Use a blank if the result is not meaningful until an input is entered, zero if the report must display a numeric value, or a short message if readers need an explanation. A blank-looking result is often suitable for a report, but it does not repair the missing or zero input. If the denominator should never be zero, consider correcting the source data rather than merely concealing the warning.
Use IFERROR for a quick fix that hides any formula error. Suppose the numerator is in A2 and the denominator is in B2, and you want the cell to appear blank if the division returns an error. Enter =IFERROR(A2/B2,"") in the result cell. If A2 contains 120 and B2 contains 0, Excel displays a blank. If B2 contains 4, the result is 30. Replace the final empty text, "", with 0 to display zero, or with text such as "Not available" to display a message. IFERROR catches errors other than #DIV/0! too, including errors that may signal a separate problem, so use it only when that broader behavior is acceptable.
Use IF when you want to handle a zero denominator specifically. With the numerator in A2 and denominator in B2, enter =IF(B2=0,"",A2/B2) in the result cell. When B2 is zero or genuinely empty, the formula returns a blank; when B2 is 4 and A2 is 120, it returns 30. This keeps other errors in the numerator or division visible rather than automatically masking them. If B2 might contain a formula that returns an empty string, use =IF(OR(B2="",B2=0),"",A2/B2) instead; with A2 equal to 120 and B2 returning an empty string, the result is blank. Change the blank result to 0 or a quoted message if that is more useful.
Replace the original formula carefully. If you are editing the cell that already contains a division formula, preserve its numerator and denominator references inside the wrapper. For example, if the existing formula divides C5 by D5, use =IFERROR(C5/D5,"") for broad error handling, or =IF(D5=0,"",C5/D5) to check only for a zero denominator. Press Enter to apply the change, then confirm that the cell now shows the intended result. Keep any existing operators, parentheses, and sheet references intact.
Apply the formula to the rest of the column or range. Select the updated formula cell and drag its fill handle, the small square at the lower-right corner, down through the cells that need the same calculation. Excel normally adjusts relative references row by row. Alternatively, select the formula cell and the cells below it, then press Ctrl+D on Windows to fill down. On Mac, use Command+D. Check the first and last filled rows to make sure the references shifted correctly and that no unrelated cells were overwritten.
Check both normal and error cases. Test a row with a nonzero denominator and one with a zero or blank denominator. For the example using 120 and 4, the result should be 30; with 120 and 0, the blank-handling version should display nothing. Also check a row where the numerator or denominator contains an unexpected error, particularly if you chose IFERROR. This confirms that the formula is returning the desired output and not simply hiding a wider data issue.

Common Mistakes

Using IFERROR without realizing it hides more than divide-by-zero errors. It also substitutes the chosen result for errors such as #VALUE! and #N/A. If those errors matter, use an IF test for the denominator instead, or investigate each error type before deciding how to handle it.

Returning zero when zero means something different. A zero can be interpreted as a real measured result in a summary, chart, or later calculation. If the calculation is not available, a blank or a clear message may be less misleading. Agree on the meaning of the substitute with anyone who uses the workbook.

Removing the division instead of fixing its condition. Deleting a formula or replacing it with a typed blank stops the visible error but also stops future calculations. Keep the calculation and add a condition around it so it can update when the missing denominator is supplied.

Copying a formula with incorrect references. When filling down, Excel changes relative references automatically. If a denominator should always come from one fixed cell, use an absolute reference such as $B$2 in the formula. Confirm the reference behavior before filling many rows.

Assuming a formula result of "" is a truly empty cell. It looks blank, but it still contains a formula. Functions or checks that distinguish empty cells from cells containing formulas may treat it differently. Keep this in mind when counting entries, exporting data, or building another formula from the range.

Tips

Prefer targeted handling when you know the exact cause. A formula that checks the denominator makes the rule easy for another person to understand: do not divide when the divisor is zero. Use IFERROR when the intention is genuinely to show a fallback for any calculation error, not simply to make a sheet look cleaner.

Consider making the fallback informative. For an internal calculation table, a blank may be enough. For a shared report, a message such as “Check denominator” can prompt someone to review the input. If downstream formulas expect numbers, a text message may create a new #VALUE! error, so test dependent calculations before choosing text.

If the denominator is calculated from other cells, trace those inputs. It may be zero because a source value is missing, because a total was filtered out, or because the underlying result really is zero. The wrapper should control presentation; it should not replace a check of whether the data and business rule are correct.

For a workbook with many formulas, change one representative formula first and verify it. Then fill or copy it to the intended range. If the cells are part of an Excel table, entering a formula in a calculated column may automatically fill the column; inspect the result to make sure Excel applied it to the rows you intended.

Keep a visible error indicator somewhere if people must know that values are missing. For example, a separate status column can flag rows that need attention while the calculation column stays clean. This balances a readable report with the need to fix incomplete inputs. Do not use a display-only trick, such as changing font color to match the background, as a substitute: the error remains and can be overlooked or reappear when formatting changes.

Troubleshooting

The cell still shows #DIV/0!. Confirm that you edited the cell containing the displayed error, then inspect the formula bar for the updated formula. If the error appears in a different cell, that cell may have its own formula. Check whether the denominator reference is correct and whether the formula was copied to all relevant rows. If the formula uses IF with a zero test but the denominator is produced by another formula, try the version that also checks for an empty string.

The cell is blank even though you expected a number. Check the denominator first. It may be zero, genuinely empty, or a formula returning an empty string, all of which can trigger the blank result in the examples above. Also check that the numerator and denominator references point to the intended row. If you used IFERROR, a different error may be triggering the blank; temporarily inspect the original calculation or replace the fallback with a message while diagnosing it.

A different error is hidden, or downstream calculations now fail. If the fallback is a blank or text, later formulas may treat it differently from a number. Use a numeric fallback only if zero is an honest value for the calculation, or keep the output blank and adjust dependent formulas to account for it. If an unexpected error has disappeared under IFERROR, switch to a targeted IF test or inspect the original expression to identify the underlying issue.

The formula is shown as text or Excel rejects it. Make sure the cell is not formatted as Text. Change it to General, edit the formula, and press Enter again. In some regional settings, Excel uses semicolons instead of commas between function arguments; use the separator your installation expects if a formula copied from this guide is not accepted. Do not remove the leading equals sign or the quotation marks around a text result.

The result does not update after the denominator changes. Check that calculation is set to Automatic under Formulas > Calculation Options > Automatic. If the workbook is set to Manual, recalculate with F9 on Windows; on Mac, use the recalculation shortcut available for your keyboard and Excel version, or choose Formulas > Calculate Now. Excel for the web generally recalculates automatically, but a workbook can still contain formula or reference issues.

Frequently Asked Questions

What causes #DIV/0! in Excel?
It appears when a formula divides by zero or by a blank cell treated as zero. Check the divisor and the source data before deciding whether to hide the result.

What is the simplest way to hide only a divide-by-zero error?
With the numerator in A2 and denominator in B2, use =IF(B2=0,"",A2/B2). It returns a blank when the denominator is zero or empty and calculates the division otherwise. If the denominator can be a formula returning an empty string, include the additional empty-string check described above.

Does IFERROR hide other Excel errors?
Yes. It returns the fallback for any error produced by its first argument, not just #DIV/0!. If you want unrelated errors to remain visible, test the denominator with IF instead.

Can I show a zero instead of a blank?
Yes. Replace "" in the fallback part of the formula with 0. Do this only when a displayed zero will not be mistaken for a real calculation result.

Can I hide the error without changing the formula?
There is no general worksheet display setting that safely removes just these formula errors while preserving their meaning. Conditional formatting or font color can obscure the message but leaves the error in place. A formula-level IF or IFERROR is usually clearer and more reliable.

Do these formulas work in Excel for Mac and Excel for the web?
Yes. The formulas use standard Excel functions available in Microsoft 365 on Windows, Mac, and the web. The formula syntax is the same, though ribbon locations and keyboard shortcuts can vary by platform.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.