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

- 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.
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.
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.
Click the cell showing #DIV/0!, then select its formula in the formula bar.
Enter =IFERROR((M2/N2)-1,"") and press Enter. This returns a blank when the calculation produces an error.
To display zero instead, enter =IFERROR((M2/N2)-1,0). Replace the final argument with another value if needed.

Use IF to check the divisor before calculating
Check for a zero or blank divisor when you want to handle that condition without hiding other calculation errors.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Select the result cell: Click the cell containing the division formula and select its contents in the formula bar.
- 3. Add IFERROR: Enter =IFERROR((M2/N2)-1,"") using your own cell references, then press Enter.
- 4. Save the workbook: Choose File > Save to keep the updated formula.

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.




