Avoid Divide by Zero Errors in Excel

How-to basics · Updated November 20, 2026

To avoid Excel’s #DIV/0! error, check that the divisor is not zero before dividing, or use IFERROR to provide a fallback. For a calculation in which B2 is the divisor, a blank result when B2 is zero can be produced with =IF(B2=0,"",A2/B2). Use a meaningful message or zero instead of a blank if that makes the result clearer to workbook users.

What You’ll Need

  • An Excel workbook containing the formula that displays #DIV/0!, or the data for a calculation you are setting up.
  • A clear decision about what the result should show when division is not possible: a blank, zero, a message such as “Not available,” or the original error.
  • Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web; some menus and shortcuts can differ.

Step-by-Step

  1. Find the formula and identify its divisor. Select a cell showing #DIV/0! and look in the formula bar. Division is usually written with a slash, such as a numerator divided by a cell reference or a calculation. The value after the slash is the divisor. For example, in a formula dividing A2 by B2, B2 is the divisor. If the divisor is itself a formula, inspect that cell or expression too: it may be returning zero or a blank. Make a note of what the calculation is meant to represent before changing it, because the right fallback depends on the meaning of the result.
  2. Check the divisor’s value in the affected row. Look at the cell used as the divisor, not just the cell showing the error. It may contain the number 0, be empty, or contain a formula whose result is zero. A blank divisor also causes a division-by-zero error. If the divisor should not be zero, correct the source data or the formula that calculates it. For example, a missing count might need to be entered once it is available. Do not replace a legitimate zero with an arbitrary small number just to make the error disappear; that changes the calculation.
  3. Choose the result you want when the divisor is zero. A blank is useful when the calculation is not yet meaningful and the sheet should stay uncluttered. A message such as “No denominator” is more informative in reports. A numeric zero can be suitable if downstream calculations or charts need a number, but it can be mistaken for a real calculated result. Decide whether zero and missing data mean the same thing in your workbook. If they do not, use different handling for each rather than hiding both indiscriminately.
  4. Guard the division with IF. With the numerator in A2, the divisor in B2, and the result in C2, enter this in C2 to display a blank when B2 is zero:

With the numerator in A2, the divisor in B2, and the result in C2, use:

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

This tests B2 first. If B2 is zero, Excel returns the empty text string, which appears as a blank; otherwise, it calculates A2 divided by B2. A genuinely empty B2 is also treated like zero by this test, so the formula avoids the error for both cases. If A2 is 12 and B2 is 3, the result is 4. If B2 is 0 or empty, C2 appears blank. To show a message instead, replace the empty text argument with a message, for example "Not available". To return a numeric zero, use 0 without quotation marks.

There is a repeated layout description just before the formula because it is important to know which cells the example uses: the numerator is in A2, the divisor in B2, and the result in C2. The formula does not change the input cells; it changes only what the result cell displays.

  1. Use IFERROR when you want a general fallback. If the calculation might produce other errors as well and you intentionally want the same fallback for any error, use IFERROR. With the numerator in A2, the divisor in B2, and the result in C2, enter:

=IFERROR(A2/B2,"Not available")

If A2 is 12 and B2 is 3, this returns 4. If B2 is zero or blank, it returns “Not available.” However, IFERROR also catches errors other than division by zero, such as a reference error or an invalid value. That can conceal a separate problem. Use the more specific IF check when the only condition you want to handle is a zero divisor; choose IFERROR when a common fallback for any calculation error is appropriate.

  1. Test the formula with normal and problem values. Try a row with a nonzero divisor, a row with zero, and a row with a blank divisor. Confirm that the normal row still produces the expected arithmetic result and that the other rows show the chosen fallback. Also check any formula that uses the result cell. A blank-looking result from the IF formula is text, not a numeric zero, so a later calculation may treat it differently. If downstream formulas require numbers, returning 0 may be more suitable, or those formulas may need their own checks.
  2. Fill the corrected formula down the column. Select the cell with the working formula and drag its fill handle down, or copy and paste it into the remaining result cells. Excel normally adjusts relative references by row, so the next row will use its own numerator and divisor. Check a few filled rows to ensure the references moved as intended and did not accidentally point to a fixed or unrelated cell. If the divisor is in a fixed cell that should not move, use an absolute reference such as $B$2 in the formula.
  3. Decide whether to repair one formula or a whole column. If the error appears in a calculated column, update the formula pattern for the entire intended range, not just the first visible error. If the workbook uses an Excel table, entering the corrected formula in a table column often fills the calculated column automatically. Afterward, inspect the top, middle, and bottom of the range and any totals, charts, or summary formulas that depend on it.

Common Mistakes

Wrapping everything in IFERROR without checking the cause. This can make a worksheet look clean while hiding unrelated issues. If a formula has a broken reference or unexpected text input, “Not available” may appear instead of revealing the actual error. When you are diagnosing a workbook, first inspect the divisor and the original formula. Use a targeted zero check unless you have a reason to handle multiple error types with the same fallback.

Returning zero automatically. A zero may look like a valid result, even though no division took place. In a rate or percentage report, readers might interpret it as a measured zero rather than an unavailable calculation. Choose a fallback that makes the status clear. If zero is required for later arithmetic, consider adding a separate status or note so users can distinguish a true zero from a calculation that could not be performed.

Checking the wrong cell or only one row. The divisor may be a formula in another column, and the problem may occur only on particular rows. Confirm that the IF test refers to the same denominator used by the division. Then test and fill the formula across the complete data range. A formula that checks B2 but divides by C2, for example, does not protect the actual divisor.

Changing the underlying data just to suppress the error. Replacing zero with 1, or adding a tiny amount to every divisor, changes the calculation and may distort results. Correct a source value only when it is genuinely wrong. If zero is valid data, keep it and handle the undefined calculation explicitly in the result formula.

Tips

Use wording that matches the workbook’s purpose. “No denominator” can help a colleague understand why a rate is missing; a blank can be preferable in a polished presentation; and numeric zero may work for a calculation where zero is explicitly the required substitute. Be consistent across a report so users do not have to guess what different blank, zero, and message results mean.

When the divisor is a calculated value, checking its final result is usually more reliable than trying to predict every reason it might be zero. If the denominator depends on several cells, calculate it in a separate helper cell when that makes the workbook easier to audit. A visible denominator can help you spot missing inputs and understand why a particular row has no result.

If you want to show a message only for a zero divisor but still notice other errors, use an IF test rather than a blanket error handler. This preserves other errors for investigation. Conversely, a user-facing dashboard may need a consistent display; in that case, document what the fallback means and keep a way to inspect the underlying data.

Excel formulas use commas between arguments in the examples here. Some regional settings use semicolons instead, so Excel may require semicolons when you enter a formula. If Excel rejects a formula that otherwise looks correct, check the list separator setting used by your installation. The function name and logic remain the same.

Troubleshooting

The error remains after adding IF. Verify that the condition checks the exact cell or expression used as the divisor. Check that the formula was entered in the intended result cell and that the formula bar shows the edited version. If the divisor is a calculation, inspect its displayed value and its own inputs. A different row or a second division operation elsewhere in the formula may still be unguarded.

The formula returns a blank when you expected a number. Check whether the divisor is actually zero or empty. If it contains a formula that returns an empty string, the comparison may behave differently from a truly empty cell depending on the exact expression; inspect the divisor’s formula and decide whether an explicit blank test is needed. Also check that you did not select a different row when copying the formula.

You see a different error, such as #VALUE!. A zero check only prevents division by zero; it does not fix text where a number is expected, invalid references, or other formula problems. Inspect the numerator, divisor, and every part of the calculation. If you choose IFERROR, remember that it can replace these other errors with the same fallback, which may make the underlying issue less visible.

The formula is rejected when you press Enter. Confirm that the function name, parentheses, and quotation marks are correct. The formulas here use straight double quotation marks and comma separators. If your Excel regional settings require semicolons, replace the argument commas with semicolons. Excel for Mac and Excel for the web use the same formula logic, though their ribbon layouts may differ.

The result looks blank but affects totals unexpectedly. The empty text returned by "" is not the same as a numeric zero. Review the formulas that use the result and decide whether they should ignore text, treat unavailable rows as zero, or report a separate missing-value status. Make that choice based on the meaning of the data, not just on which option makes a total look convenient.

Frequently Asked Questions

What causes #DIV/0! in Excel?
Excel shows this error when a formula divides by zero or by a blank cell. It can also happen when the denominator is a formula whose result is zero. Find the divisor in the formula and inspect its value and source.

Is IF or IFERROR better for avoiding the error?
Use IF when you want to handle a zero divisor specifically and leave other errors visible. Use IFERROR when you deliberately want the same fallback for any error in the calculation. IFERROR is shorter, but it can hide problems unrelated to division.

Can I show a dash instead of a blank?
Yes. With the numerator in A2, the divisor in B2, and the result in C2, use:

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

For A2 equal to 12 and B2 equal to 0, the result is a dash; for B2 equal to 3, it is 4. The dash is text, not a numeric value, so consider how later calculations will use the result.

Will these formulas work in Excel for Mac or Excel for the web?
Yes. IF and IFERROR are standard Excel functions and the formulas work in current versions of Excel for Mac and Excel for the web. The interface and some keyboard shortcuts differ, but you can enter the formulas directly into the cell or formula bar.

Should I return zero or a blank for a zero divisor?
Choose according to what the result means. Use zero only when it is a valid and unambiguous substitute for the unavailable calculation. Use a blank or a clear text label when a zero would suggest that a real measurement was made. If the result feeds other formulas, check how that choice affects them.

Can a blank cell cause this error?
Yes. Excel treats an empty denominator as zero for division, so an expression that divides by a blank cell can produce #DIV/0!. A zero check handles a genuinely blank divisor in the common pattern shown above; also inspect formula-generated blanks if your workbook uses them.

Related

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.