Ignore Divide by Zero Errors in Excel with IFERROR

To hide a divide-by-zero result in Excel, wrap the division in IFERROR, such as =IFERROR(B2/C2,""). This displays a blank instead of an error when the calculation fails. If you want to suppress only division-by-zero errors and keep other problems visible, test the denominator with IF instead. The right choice depends on whether you want a clean display or stricter error checking.

What You’ll Need

A workbook in Microsoft Excel for Microsoft 365 on Windows, with the formula that currently returns #DIV/0!.
The cell references for the numerator (the value being divided) and denominator (the value dividing it). For example, a revenue amount might be in B2 and a quantity in C2.
A decision about what should appear when the denominator is zero: a blank cell, a message, or a numeric zero. Keeping an error visible can also be appropriate when it signals missing or invalid data.

The formulas below use commas as argument separators and straight quotation marks. Excel may use semicolons instead in some regional settings; if you see a formula-entry error, use the separator expected by your installation. The same functions are available in current Excel for Mac and Excel for the web, although ribbon placement can vary slightly.

Step-by-Step

Find the source of the error. Select a cell showing #DIV/0! and inspect its formula in the formula bar. Identify the denominator: it may be a cell reference, a value calculated by another formula, or a range result. Excel returns #DIV/0! when a formula divides a number by zero or by a blank cell treated as zero. For instance, a per-unit calculation will fail if the quantity cell is empty. Check whether zero is a valid value or whether the missing denominator points to unfinished data. Understanding the cause helps you decide whether to display a blank or fix the underlying data.
Choose how broadly to handle errors. Use IFERROR when any error from the calculation should be replaced with a chosen result. Use IF to check specifically whether the denominator equals zero, leaving unrelated errors visible. The broader option is convenient for presentation, but it can conceal problems such as a mistyped cell reference. If you are building a report that other people will rely on, a specific zero check is often easier to audit.
Wrap the division with IFERROR to show a blank. Suppose revenue is in B2 and units sold are in C2, and you want revenue per unit in D2. With revenue in B2 and units in C2, enter this in D2: =IFERROR(B2/C2,""). When C2 is zero or blank, D2 appears empty. If B2 is 120 and C2 is 4, the formula returns 30. If C2 is zero, it returns a blank display instead of #DIV/0!. Replace B2 and C2 with the references in your own sheet; do not change the comma or quotation marks unless your Excel regional settings require different separators.
Enter the formula and check its result. Click the output cell, type or paste the formula, and press Enter. Confirm that a normal calculation still returns the expected number and that a zero denominator no longer displays the error. A blank result from "" is an empty text value, not a genuinely empty cell. This matters if another formula counts or tests the cell. The cell may look empty while still containing a formula.
Use IF when you want to catch only a zero denominator. For the same layout, with revenue in B2 and units in C2, put this in D2: =IF(C2=0,"",B2/C2). If C2 is zero or blank, the result is blank; if it contains 4 and B2 contains 120, the result is 30. Unlike IFERROR, this does not automatically replace every other error that might occur in the division. If B2 or C2 contains an error, that error remains visible, which can help you find a separate problem.
Choose a more informative result if a blank could confuse readers. With revenue in B2 and units in C2, this version displays a message when there are no units: =IF(C2=0,"No units",B2/C2). For a zero denominator it returns the text No units; for 120 divided by 4 it returns 30. A message makes the reason for the missing calculation clear, but the result column now contains both text and numbers. That can affect later calculations, charts, or sorting, so use a message only when it suits the purpose of the column.
Apply the formula to the rest of the column. If the data is in ordinary cells, select the formula cell and drag its fill handle (the small square at the lower-right corner) down the rows. Excel normally adjusts relative references, so a formula referring to B2 and C2 changes to B3 and C3 on the next row. Check several filled rows to make sure each formula refers to the matching row. If the data is formatted as an Excel table, entering the formula in a table column generally fills the calculated column automatically.
Keep the source data and the calculation separate. Do not replace the original zero or blank denominator merely to remove the display error unless that change reflects the real data. Put the error-handling logic in the result formula, then retain the original inputs for review. Save the workbook and, if it is used for important reporting, compare a few results with hand calculations. This confirms that the formula is hiding only the display you intended and has not changed the underlying arithmetic.

Common Mistakes

Hiding every error without realizing it. IFERROR substitutes the fallback value for any error in its first argument, not just #DIV/0!. That includes errors such as #REF! from a broken reference and #VALUE! from incompatible data. A blank output can therefore make a damaged formula look like a harmless zero-denominator case. Use the explicit denominator test when you want other errors to remain visible, or investigate the source of an error before hiding it.

Returning zero when zero means something different. A formula such as =IFERROR(B2/C2,0) displays a numeric zero when the calculation errors. That may be appropriate for a report where zero is the intended convention, but it can wrongly suggest that the calculated rate is actually zero. If the value is undefined because there are no units, a blank or clear message may be more accurate.

Expecting an empty string to behave like a truly empty cell. A formula that returns "" displays nothing, but the cell still contains a formula and returns text. Functions that count numbers may ignore it, while functions that count nonempty cells may treat it differently from a cell that has never had content. Check how downstream formulas handle the result before using blanks throughout a model.

Overwriting the denominator to make the error disappear. Changing a source value from zero to 1, or deleting a formula, may remove the error but also falsify the data. Handle the result in the calculation or correct the source only after confirming what the input should be.

Tips

Use IFERROR for concise error handling when replacing all calculation errors is acceptable. Use an IF test against the denominator when you specifically want to handle zero and preserve other errors for investigation.
Choose a fallback that matches the meaning of the calculation. A blank is unobtrusive, a message explains why a value is missing, and zero is a real numeric value that can affect totals and averages.
For formulas copied down a list, test at least one row with a nonzero denominator, one with zero, and one with a blank denominator. This catches incorrect references and confirms the behavior you want.
If you need to use a blank-looking result in later calculations, decide how those calculations should treat it. Do not assume that a formula returning "" is identical to a genuinely empty cell.
Use Excel’s formula bar to inspect the actual formula rather than relying only on what the cell displays. A blank result can hide both a deliberate fallback and an unintended formula error.

Troubleshooting

The formula itself is rejected. Check that the function name is spelled IFERROR, the parentheses are balanced, and the quotation marks around a blank or message are straight double quotes. The examples use commas between arguments. Depending on your Windows regional settings, Excel may expect semicolons; replace the argument separators if Excel reports a syntax problem. In Excel for Mac or the web, function behavior is the same, though the expected separator can also depend on locale.

The error remains after you add IFERROR. Confirm that the formula you edited is the cell that displays #DIV/0!, and that the division is inside the first argument of IFERROR. If the visible error comes from a different formula or a referenced cell, wrapping another calculation will not catch it. Also check for a second division elsewhere in the formula. In a longer calculation, each operation that can cause an error must be included within the expression being handled, or addressed at its source.

The cell looks blank but is included in a count. If the formula returns "", the cell still contains a formula. A function that counts cells containing formulas or text may treat it differently from a truly empty cell. Review the counting function and decide whether you want to count numeric results, formulas, or visibly populated cells; these are not always the same thing.

Unexpected errors still appear with the IF version. The formula =IF(C2=0,"",B2/C2) only checks whether C2 equals zero. It will not suppress an error already present in B2 or C2. Inspect those input cells and their formulas. If your goal is to hide all errors, switch to IFERROR; if the other error indicates bad data, fixing it is usually more useful than masking it.

A zero-looking denominator is not caught. The denominator may contain a very small nonzero value, or a formula may return a value that displays as zero because of number formatting. Select the cell and inspect its value or formula bar. If your business rule treats values below a threshold as zero, define that threshold deliberately rather than assuming the displayed format changes the stored value.

Frequently Asked Questions

Does IFERROR hide only #DIV/0!?
IFERROR replaces any error returned by its first argument, including reference and value errors. To handle only a zero denominator, test the denominator with IF and leave other errors visible.

How do I show a zero instead of a blank?
Use a zero as the fallback in IFERROR if every error should display as numeric zero. If only a zero denominator should produce zero, use an IF test on the denominator. Consider whether a displayed zero would be mistaken for a real calculated result.

Will a blank returned by the formula count as an empty cell?
No. "" is an empty text result, and the cell still contains a formula. Some Excel functions treat it differently from a cell that is genuinely empty, so check downstream counts and calculations.

Can I prevent the error without changing the original formula’s inputs?
Yes. Wrap the division in IFERROR or add a denominator check with IF. Both change the displayed result of the calculation, not the source values in the numerator or denominator cells.

Does this work in Excel for Mac and Excel for the web?
Yes. IFERROR and IF work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Menu placement and regional formula separators may differ, but the logic is the same.

Should I hide every divide-by-zero error in a workbook?
Not necessarily. If zero means that a rate cannot yet be calculated, showing a blank or message can make a report easier to read. During data entry or auditing, keeping the error visible can reveal missing inputs that need attention. Choose the behavior for the purpose of the sheet rather than hiding errors automatically.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.