Excel #DIV/0! Error: How to Hide It Without Hiding Data

To stop Excel showing a #DIV/0! error, wrap a division formula in IFERROR or check that its denominator is not zero first. For example, with the number to divide in A2 and the divisor in B2, enter =IF(B2=0,"",A2/B2) to show a blank when B2 is zero or empty and calculate normally otherwise. This keeps the worksheet cleaner while letting you choose how to handle the underlying calculation.

What You’ll Need

An Excel workbook with a formula that divides one value by another, or a cell where you want to enter a division formula.
The cells containing the numerator and denominator, so you can check what input is causing the error.
Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web; the formula separator may vary with your regional settings.

Step-by-Step

Find the formula that produces the error. Select a cell displaying #DIV/0! and look at the formula bar. A division formula contains a slash, such as a reference divided by another reference. The error usually means the divisor is zero or refers to a blank cell. Note which cell is the divisor before changing the formula; hiding the message does not correct the inputs or establish what the result should mean.
Choose what should appear instead of the error. Decide whether an empty-looking cell, a message such as “Check divisor,” or a numeric zero is appropriate. A blank is useful when the calculation is not ready yet, such as a monthly report whose later months have no data. A message makes missing or invalid inputs more visible. Zero can be appropriate when your reporting rules explicitly treat an unavailable result as zero, but it can also be mistaken for a genuine calculated value.
Use IFERROR for the quickest general fix. With the numerator in A2 and the denominator in B2, replace the existing division formula with =IFERROR(A2/B2,""). Excel attempts the division and displays a blank if the formula returns any error. If both cells contain 12 and 3, the result is 4; if B2 contains 0 or is empty, the result appears blank. This is quick to apply, but it also suppresses errors other than #DIV/0!, including errors that could point to a separate problem.
Use an explicit zero check when you only want to hide division by zero. With the numerator in A2 and the denominator in B2, enter =IF(B2=0,"",A2/B2). The formula checks B2 first: if it is zero, Excel returns an empty text value; otherwise, Excel divides A2 by B2. In the example, A2=12 and B2=3 returns 4, while B2=0 returns a blank. Excel treats a truly empty denominator cell as zero in this comparison, so it also avoids dividing by a blank. Other errors in the division are not deliberately hidden by this check.
Replace the formula in the affected cell. Select the formula cell, click the formula bar or press F2 on Windows to edit it, and replace the old formula with the version you chose. Press Enter. On Mac, use the formula bar or double-click the cell to edit; keyboard shortcuts can differ depending on the keyboard and system settings. If you edit directly in the cell, make sure you replace the whole formula rather than typing the new one after the old formula.
Fill the corrected formula through the rest of the column. If the same calculation appears on multiple rows, use the fill handle at the lower-right corner of the selected cell to copy the formula down, or copy and paste it into the intended range. Check that the references adjust by row: a formula in row 3 should normally refer to that row’s numerator and denominator, not keep pointing to row 2. If your data is an Excel Table, entering the corrected formula in a calculated column will usually populate the column automatically.
Test both a valid and an invalid case. Check a row with a nonzero divisor to make sure it still returns the expected quotient. Then check a row with a zero or blank divisor to confirm the chosen replacement appears. For a deliberately visible message instead of a blank, with numerator in A2 and denominator in B2, use =IF(B2=0,"Check divisor",A2/B2). With A2=12 and B2=0, the result is “Check divisor”; with B2=3, the result is 4.

Common Mistakes

Using IFERROR without realizing how broadly it works. IFERROR replaces any error produced by its first argument, not only #DIV/0!. If a reference is misspelled or another part of the expression is invalid, a blank result may conceal that issue too. Use the explicit denominator check when your goal is specifically to handle a zero or empty divisor and keep other errors visible.

Hiding a meaningful zero. A zero result can have a different meaning from “not calculated.” For example, a blank may mean no data was entered, while zero may mean a real measured value. Choose a replacement that matches the meaning of your worksheet, especially when someone else will read or use the results.

Changing the wrong cell or only part of the range. A report may contain many independent formulas, and fixing one does not automatically fix all of them unless you fill the formula through the intended range. Conversely, replacing an entire column can overwrite labels, manually entered values, or formulas that use a different calculation. Check the selected range before filling or pasting.

Assuming the underlying calculation is fixed. A blank-looking cell can make the error disappear, but the divisor may still be missing because of an incomplete data import, a wrong reference, or an incorrect formula. Inspect the input and confirm the calculation is appropriate rather than treating the display change as a data repair.

Tips

For a reusable worksheet, pick one consistent convention for unavailable calculations. For example, use blanks for future periods that have not been entered, and a clear text message when a person needs to correct an input. Explain the convention in a nearby note or column heading if the distinction matters to readers.

Keep the formula readable. If the original calculation is more involved than a simple division, wrap the existing expression rather than changing its logic. With numerator in A2 and denominator in B2, the basic pattern is =IF(B2=0,"",A2/B2); in your own workbook, preserve the original numerator, divisor, and any other operations. Do not add a check against a different cell simply because that cell looks related.

When using IFERROR, consider returning a descriptive value instead of a blank if hidden failures would be risky. With numerator in A2 and denominator in B2, =IFERROR(A2/B2,"Review formula") returns the quotient when successful and “Review formula” for any error. The text result is not numeric, so downstream sums and charts may treat it differently from a number or blank.

Keep a copy of the original formula before making a large-scale change, particularly in a shared or important workbook. You can copy the formula into a temporary note or duplicate the sheet before editing. This makes it easier to compare the original calculation with the error-handling version if the results do not look right.

Troubleshooting

The cell still displays #DIV/0!. Confirm that you replaced the formula in the cell showing the error, then press Enter. If the error is in a different cell, follow its references: the visible result may depend on another formula that still returns the error. Also check for formulas copied into rows outside the range you edited. Selecting the error cell and inspecting the formula bar helps distinguish an unchanged formula from an error coming from a referenced cell.

The formula shows an error as soon as you enter it. Check that the cell references exist and that the formula has balanced parentheses and straight quotation marks around text such as "". In some regional settings, Excel expects semicolons instead of commas between function arguments. If Excel rejects a comma-separated formula, use the separator expected by your installation; the function name and logic are otherwise the same. Avoid copying typographic curly quotation marks from formatted text.

The result is blank even though you expected a number. Check the divisor cell’s actual value and formula. It may be empty, contain a formula returning an empty string, or evaluate to zero. If the value is not zero, inspect the original numerator and divisor references for an accidental shift when the formula was filled down. You can temporarily use a visible message, as shown in the steps, to identify rows being caught by the zero check.

A different error has disappeared too. This is expected when the formula uses IFERROR: it hides all errors returned by the wrapped expression. If you want only zero divisors handled, switch to the explicit IF check. Then investigate any other error that becomes visible; it may reveal a separate reference or input issue.

The workbook’s totals or charts change. A blank, text message, and numeric zero are not interchangeable. A blank or text value may be excluded from some calculations or chart displays, while zero is a numeric value. Review any dependent totals, averages, and charts after changing the error result, and select a replacement consistent with how those outputs should behave.

Frequently Asked Questions

What causes #DIV/0! in Excel?
Excel shows it when a formula divides by zero or by a cell that is blank and therefore treated as zero for the division. Check the denominator and the formula references to find the cause.

Is IFERROR or IF better for hiding this error?
Use IFERROR for a quick fallback when you are comfortable replacing any error from the calculation. Use IF to test the divisor when you want to handle zero or blank denominators while leaving unrelated errors available to spot.

Can I show 0 instead of a blank?
Yes. With the numerator in A2 and the denominator in B2, use =IFERROR(A2/B2,0) to return zero for any error. This also masks errors besides division by zero, so use a zero-check formula if you only want to replace a zero divisor.

Will the formula work in Excel for Mac or the web?
Yes. IF and IFERROR are available in current Excel for Microsoft 365 on Windows, Mac, and the web. Editing controls may differ slightly, and regional settings can change the argument separator from a comma to a semicolon.

Does hiding the message fix the source data?
No. It changes what the formula displays when the calculation errors; it does not fill in missing values or correct a wrong reference. Verify the divisor and the intended calculation whenever the result is used for decisions or reporting.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.