Hide #DIV/0! Errors in Excel Formulas

To hide a #DIV/0! error in an Excel formula, wrap the calculation in IFERROR: with the numerator in A2 and the denominator in B2, enter =IFERROR(A2/B2,""). Excel then displays a blank when the division produces an error; if you want to hide only a zero denominator, use an IF check instead.

Why Excel shows #DIV/0!

Excel returns #DIV/0! when a formula tries to divide a number by zero. It can also appear when the divisor refers to a blank cell, because Excel treats a blank divisor as zero in division. A formula such as a percentage calculation may therefore show the error before the user has entered the required denominator.

The error is Excel’s way of indicating that the calculation cannot produce a valid numeric result as written. For example, a return rate calculated as returns divided by units sold cannot be calculated for a row with no units sold. The right response depends on what that row means: it might be incomplete data, a genuine zero-volume case, or an input mistake that should be investigated.

Hiding the error changes what the worksheet displays; it does not make division by zero mathematically valid or supply missing information. Before suppressing errors across a whole workbook, decide whether users should see a blank, a zero, or an explanation so they can tell how to interpret the result.

Use IFERROR for the quickest fix

IFERROR evaluates a calculation and returns an alternative value if the calculation results in an Excel error. With the numerator in A2 and the denominator in B2, use this formula to show a blank for an error:

=IFERROR(A2/B2,"")

If A2 contains 120 and B2 contains 0, the formula displays nothing. If B2 contains 4, it returns 30. The empty text between the quotation marks is the value Excel displays when the calculation fails. You can replace it with a message. With the same cell layout, this version displays an explanation instead:

=IFERROR(A2/B2,"Check inputs")

This approach is useful when a formula can encounter different kinds of errors and you want all of them to have the same fallback. It also keeps a worksheet’s visible results tidy while data is incomplete. However, its broad coverage is important: IFERROR catches errors beyond #DIV/0!, including errors such as #VALUE! and #N/A. That can conceal a problem unrelated to division, so it is not always the best choice for an audited calculation or a sheet where input mistakes need attention.

Hide only a zero denominator

If the specific problem is a zero or blank divisor, test the divisor before doing the calculation. With the numerator in A2 and the denominator in B2, enter:

=IF(B2=0,"",A2/B2)

When B2 is zero or blank, the formula returns a blank; when B2 is 4 and A2 is 120, it returns 30. Unlike IFERROR, this formula does not intentionally hide other errors in the division. For example, if A2 contains text that cannot be used in the calculation, Excel can still show the resulting error rather than silently replacing it with an empty display.

Use this method when a zero denominator is an expected condition but other formula errors should remain visible. It is also a clear way to express the rule: calculate only when the divisor is nonzero. A blank divisor is treated like zero by this comparison, so the formula also suppresses the result while B2 has not yet been filled in.

If zero has a meaningful business interpretation, consider displaying a message instead of a blank. With the same A2 and B2 layout, this version labels a missing or zero divisor:

=IF(B2=0,"No denominator",A2/B2)

The message makes the reason for the missing result visible to readers. As with the blank-returning version, unrelated errors in the calculation are not caught by this test.

Choose between a blank, zero, and a message

The replacement value determines how clear and useful the worksheet will be. A blank appearance is compact, but it does not explain why no result is shown. A zero is explicit, but it can be mistaken for a real calculated value. A short message gives context, though it may make a numeric column harder to sort, chart, or use in later calculations.

Return a blank when the result is not yet available and a clean display matters. Remember that "" is an empty text value returned by a formula, not a cell with no formula in it.
Return zero only when zero is the intended meaning of the result. With the numerator in A2 and the denominator in B2, an IFERROR fallback of zero looks like this: =IFERROR(A2/B2,0). It hides every error from the calculation, not just division errors.
Return a message when people need to know that a divisor is missing or invalid. A text result is informative, but downstream formulas expecting a number may need to handle that text.
Leave the error visible when it signals a data-quality issue that must be corrected, rather than a normal condition to suppress.

A formula returning "" can behave differently from a truly empty cell in later operations. For example, COUNTA counts a cell containing a formula even when that formula displays an empty string. This can surprise users who count apparently populated cells. Numeric functions such as SUM generally ignore text values in referenced cells, but formulas that test for blanks or count nonempty cells may produce results different from what the display suggests. If other calculations depend on the result, test those calculations with the chosen fallback.

Understand what IFERROR can conceal

The convenience of IFERROR is also its main risk. It replaces any error generated by the expression, so a misspelled reference, incompatible text input, or unavailable lookup result can look just like an ordinary blank or zero. That may make a report look finished even though part of its logic is broken.

For a calculation where only division by zero is expected, an IF test is often more diagnostic. For a calculation where several error types are expected and a single fallback is acceptable, IFERROR is simpler. Do not choose between them only by formula length; choose based on which problems the workbook should show to its users.

Also distinguish hiding an error from correcting its cause. If a divisor should never be zero, trace the referenced cell and correct the source data or formula. If zero is legitimate—for example, no sales occurred in a period—decide whether the metric should be blank, labeled, or defined another way for that case. A formula can suppress the warning, but it cannot decide the business meaning for you.

Apply the formula to a column

For a table of calculations, write the formula in the first result row using that row’s references, then copy it down. For example, if each row has a numerator in column A and a denominator in column B, put the formula in C2 and fill it through the other rows. Relative references adjust as the formula is copied: the next row refers to A3 and B3.

Select the first result cell, such as C2, and enter the chosen formula.
Press Enter and check the result against a row with a nonzero denominator and one with a zero or blank denominator.
Copy the formula down using the fill handle, or select the range and use Home > Fill > Down.
Check several rows at the bottom of the range to confirm that the references moved correctly and that no relevant errors were hidden.

If the formula is part of an Excel table, entering it in a calculated column generally fills the formula through that column. Confirm the results, especially if the table includes unusual rows or existing formulas. If your Excel language or regional settings use a different list separator, Excel may require a separator other than a comma; the examples here use the comma syntax specified for standard English-language Excel formulas.

Common problems and useful checks

The cell looks blank but affects a count: a formula returning "" is still in the cell. In particular, COUNTA counts it as a nonempty cell. Use a counting approach suited to the data rather than relying only on what appears on screen.
The result is zero when you expected a blank: check that the formula’s fallback is "", not 0, and that the formula was copied into the intended cells.
Other errors disappear too: this is expected with IFERROR. Switch to an explicit zero-denominator test if other errors should remain visible.
The formula still shows an error: a denominator check prevents division when the denominator is zero, but it does not correct unrelated problems such as invalid text or an error in a referenced cell. Inspect the inputs and references.
A blank is treated as zero in a later calculation: this may be appropriate for some operations, but not all. Test dependent formulas and charts with representative data before relying on the displayed blank as a particular numeric meaning.

When checking a formula, select the result cell and inspect the formula bar to confirm the actual expression. Also check whether the input cell is genuinely blank, contains a formula returning "", or contains a numeric zero. Those values can look similar in some worksheet layouts but do not necessarily behave the same way in formulas.

Frequently Asked Questions

How do I hide only #DIV/0! in Excel?
Test the denominator with IF before dividing. If the numerator is A2 and the denominator is B2, use =IF(B2=0,"",A2/B2). It hides the result for a zero or blank divisor while allowing unrelated errors to remain visible.

Does IFERROR hide every Excel error?
Yes. It returns its fallback when the expression produces an Excel error, not only #DIV/0!. That makes it convenient but can conceal other problems that you may want to find.

Can I show 0 instead of #DIV/0!?
Yes. With the numerator in A2 and the denominator in B2, use =IFERROR(A2/B2,0). This displays zero for any error in the calculation, so use it only if that fallback is appropriate and other errors do not need to be visible.

Why does a blank result still count in COUNTA?
A formula returning "" displays an empty string, but the cell still contains a formula. COUNTA counts cells containing formulas, including formulas whose displayed result is an empty string.

Should I use IFERROR or IF to hide division errors?
Use IFERROR when you want one fallback for any error from the calculation. Use an IF check on the denominator when you want to suppress only division by zero and keep other errors available for troubleshooting.

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.