Formula Errors

Prevent Excel #DIV/0! Errors with IFERROR

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 869 views

Question details

Replace Excel #DIV/0! errors with a blank, zero, or another value when a formula divides by zero or a blank cell.

Prevent Excel #DIV/0! Errors with IFERROR
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet divides one value by another, but the divisor can be zero or blank, such as when billing was recorded in a previous month.
Observed behavior
Excel displays #DIV/0! instead of a calculation result when the divisor is zero or blank.
Before you start

Identify the formula cell and the cell used as its divisor. In the examples below, M2 is the numerator and N2 is the divisor; replace these references with the cells in your worksheet.

Solution 1Recommended

Use IFERROR to replace the error with a blank or zero

Wrap the calculation in IFERROR when you want to display a fallback value instead of an error.

1
Select the formula cell

Click the cell showing #DIV/0!, then select its formula in the formula bar.

2
Wrap the calculation

Enter =IFERROR((M2/N2)-1,"") and press Enter. This returns a blank when the calculation produces an error.

3
Choose another fallback

To display zero instead, enter =IFERROR((M2/N2)-1,0). Replace the final argument with another value if needed.

Use IFERROR to replace the error with a blank or zero
IFERROR catches other errors too: IFERROR replaces any error from the calculation, not just #DIV/0!. Use the IF method if you want other errors to remain visible.
WPS Spreadsheets

Handle divide-by-zero errors in WPS Spreadsheets

Use IFERROR in WPS Spreadsheets to replace a divide-by-zero error with a blank or another value while working with your spreadsheet.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select the result cell: Click the cell containing the division formula and select its contents in the formula bar.
  3. 3. Add IFERROR: Enter =IFERROR((M2/N2)-1,"") using your own cell references, then press Enter.
  4. 4. Save the workbook: Choose File > Save to keep the updated formula.
Replace #DIV/0! results with a blank or zero using IFERROROpen and edit Microsoft Excel XLSX and XLS filesUse a familiar formula bar to update calculations
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show #DIV/0! when a cell is blank?

A blank cell used as a divisor is treated as zero in the calculation. Check the divisor cell or wrap the formula in IFERROR to return a different value.

How do I show zero instead of #DIV/0!?

In the formula bar, wrap the calculation with IFERROR and use 0 as the fallback, such as =IFERROR((M2/N2)-1,0).

Can I hide only divide-by-zero errors without masking other errors?

Test the divisor first with IF, for example =IF(OR(N2=0,N2=""),"",(M2/N2)-1). This handles a zero or blank divisor while allowing other calculation errors to appear.

Will IFERROR change the values in my source cells?

No. IFERROR changes the result displayed in the formula cell; it does not change the values in cells such as M2 or N2.