Excel Formula to Remove the #DIV/0! Error

In Excel, the quickest way to replace a #DIV/0! error is to wrap the division in IFERROR, such as =IFERROR(A2/B2,""), which displays a blank instead of an error. If you want to handle only a zero divisor and leave other formula errors visible, test the divisor directly with IF.

What causes #DIV/0! in Excel?

Excel returns #DIV/0! when a formula tries to divide a number by zero. A blank cell used as the divisor is also treated as zero in ordinary division, so a formula that refers to a denominator cell that has not been filled in can produce the same error. For example, a sales-per-unit calculation may fail when the units cell is empty or contains 0.

The error is useful information: the calculation does not have a valid numerical result yet. A zero divisor may mean that data is missing, that a measured quantity genuinely equals zero, or that the calculation is not meaningful for that row. It is worth deciding which situation applies before replacing the error. In a report, a blank may look tidy, but it can also conceal an incomplete input that needs attention.

Direct division is the most obvious cause, but a formula can show the error when its calculation includes a division whose denominator is zero. The underlying issue is still the divisor, even if the formula is longer than a simple numerator divided by denominator.

Use IFERROR for a quick replacement

Suppose the numerator is in A2 and the divisor is in B2. With those cell locations, use this formula to return a blank whenever the calculation produces an error:

=IFERROR(A2/B2,"")

If A2 contains 120 and B2 contains 0, the formula returns an empty-looking result instead of #DIV/0!. If B2 contains 5, it returns 24. The second argument, "", is an empty text string: the cell appears blank, but it is not truly empty. For example, functions that count cells or test whether a cell is blank may treat a formula returning "" differently from a cell that has never contained anything.

To show a message instead, keep the same layout—numerator in A2 and divisor in B2—and use:

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

This can be more informative in a working sheet because it tells someone that the result needs a review. You can also use a numeric replacement. With the same A2 and B2 layout, this version returns 0 when the division produces an error:

=IFERROR(A2/B2,0)

Choose the replacement based on what the result means, not just on how the cell should look. A zero is a real value and may affect totals and averages. A blank or a message may better communicate that no valid result is available.

Handle only a zero divisor with IF

IFERROR is convenient, but it catches more than #DIV/0!. It also replaces other errors in the calculation, such as #VALUE! or #REF!. If you want unexpected errors to remain visible, check the divisor before doing the division. With the numerator in A2 and the divisor in B2, this returns a blank when B2 is zero:

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

If A2 is 120 and B2 is 0, the result is blank; if B2 is 5, the result is 24. An actually empty B2 cell is treated as zero in this comparison, so the formula also avoids dividing by a blank input. If A2 contains an error, however, that error is not deliberately suppressed by the zero check. That distinction can make this approach preferable in calculation models where errors should be investigated rather than hidden.

You can return a label instead of a blank. With A2 as the numerator and B2 as the divisor, use:

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

This is useful when a reader might otherwise mistake a blank for a missing report value. Keep in mind that the formula tests whether the divisor is zero; it does not verify that the numerator or other inputs are valid. A text entry in a cell that is supposed to contain a number can still cause a different error during the division.

Choose the right replacement value

The replacement changes how people interpret the worksheet and can affect later calculations. Before filling a formula down a column, decide what a zero denominator means for the data:

Blank: Use this when no meaningful result exists yet and an empty-looking report is appropriate. Remember that a formula-generated blank is still a formula result, not a truly empty cell.
Zero: Use this only when treating the missing or invalid calculation as zero is correct for the analysis. It can lower an average or change a total.
Text such as “N/A”: Use a label when readers should know that the result is unavailable or not applicable. Text may not be included in numeric calculations in the same way as numbers.
A visible message: Use a prompt such as “Check divisor” in a worksheet used for data entry or review. It makes the issue easier to spot, though it may make a finished report less compact.

For a rate, percentage, or per-unit measure, zero may not be a sensible substitute. If 0 units were sold, for instance, revenue per unit is undefined; returning 0 could imply that a valid calculation was made and the rate itself was zero. A message or blank may communicate the situation more accurately. Consider how downstream formulas and charts use the output before choosing.

Apply the formula to a column

For a table of calculations, enter the chosen formula in the first result row and copy it down. For example, if numerators are in A2:A20, divisors are in B2:B20, and results belong in C2:C20, enter this in C2 to leave a blank for a zero divisor:

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

Because the cell references are relative, copying the formula down changes the row references: the next row uses A3 and B3, then A4 and B4, and so on. You can drag the fill handle, the small square at the lower-right corner of the selected cell, down the result range. In a structured Excel table, entering a formula in a calculated column will generally fill the formula through that column.

On Windows, select the formula cell and use Ctrl+C, select the destination cells, then press Ctrl+V. On Mac, the corresponding shortcuts are Command+C and Command+V. These are ordinary copy-and-paste shortcuts; the formula references adjust because they are relative. If you need to preserve a reference to one fixed cell while filling down, use dollar signs to make that reference absolute, such as $B$2. That is different from the row-by-row example above, where each row needs its own divisor.

Common mistakes and trade-offs

A frequent choice is wrapping every formula in IFERROR without checking what other errors might mean. If a reference was accidentally deleted or a number was entered as text, the formula can return the same blank as it does for division by zero. That makes a clean-looking sheet, but it can make a real formula problem harder to find. Use the targeted IF test when you want other errors to remain apparent.

Another mistake is returning 0 simply because it looks neat in a chart or total. A zero is not the same as “not calculated.” If a denominator is zero because the underlying activity did not occur, the result may be undefined rather than zero. Decide whether the replacement has a valid business or analytical meaning before using it in summaries.

Also, replacing an error does not repair the source data. If the divisor should never be zero, investigate why it is zero or blank. If zero is a legitimate value, then a blank or message may be an appropriate presentation choice, but the workbook should still make the rule clear to other users. A note in a column heading or a short instruction near the inputs can prevent confusion.

Finally, do not expect a formula that returns "" to behave exactly like a cleared cell. It looks blank, but other formulas can still see that the cell contains a formula. If you use such cells in counts, filters, or downstream calculations, check whether the result you want depends on counting truly empty cells, counting numeric values, or testing for an empty string.

Formula choice at a glance

For the examples below, the numerator is in A2 and the divisor is in B2. Choose the formula based on the behavior you need:

=IFERROR(A2/B2,"") replaces any error in the division with a blank.
=IF(B2=0,"",A2/B2) replaces a zero-divisor case with a blank while allowing other errors to appear.
=IF(B2=0,"Check divisor",A2/B2) gives a visible prompt when the divisor is zero.

These formulas do not change Excel’s underlying rule about division. They change what the cell displays when the calculation cannot produce a valid result. For transparent workbooks, use the narrowest condition that matches the problem and choose an output that readers can interpret correctly.

Frequently Asked Questions

How do I replace #DIV/0! with 0 in Excel?

If the numerator is in A2 and divisor in B2, use =IFERROR(A2/B2,0). This returns 0 when the division produces an error, but it also replaces other errors. To replace only a zero divisor, use =IF(B2=0,0,A2/B2) with the same cell layout.

Does IFERROR hide errors other than #DIV/0!?

Yes. IFERROR returns its fallback for any error in the expression it evaluates, not only division by zero. If you want to handle only a zero divisor, test that divisor with IF and allow other formula errors to remain visible.

Why does a blank divisor cause #DIV/0!?

In ordinary arithmetic, Excel treats a genuinely empty cell used as a divisor like zero. The division is therefore invalid. A formula that returns an empty string is not identical to a genuinely empty cell, so if your inputs use formulas that display blanks, consider using IFERROR or test the actual input conditions in your formula.

Will a formula returning an empty string count as a blank cell?

It looks blank, but it contains a formula whose result is empty text. That can behave differently from a cell that has been cleared, particularly in blank checks and counting. If that distinction matters, test the result in the context of the formulas or reports that use it.

Should I use IFERROR or IF to remove the error?

Use IFERROR for a quick fallback when any calculation error should be replaced. Use IF to check specifically for a zero divisor when other errors may reveal problems worth fixing. In either case, choose blank, zero, or a message according to what the result should mean.

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.