How to Use IFERROR to Hide #DIV/0! Errors in Excel Formulas
Question details
The user needs a method to hide or suppress #DIV/0! errors in Excel formulas when referenced input cells are empty or zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating ingredient costs or other metrics where some required input cells (like divisors) might be temporarily blank before data entry is complete.
- Observed behavior
- The formula returns an unsightly #DIV/0! error that clutters the spreadsheet. The desired state is to display a clean blank space or a zero instead.
Identify the formula returning the error and decide whether a completely blank cell or a numerical zero works best for your spreadsheet's layout and subsequent calculations.
Wrap the Existing Calculation in an IFERROR Function
Use the IFERROR function to intercept the #DIV/0! error and specify a custom alternative result.
The IFERROR function evaluates a mathematical expression and allows you to define a fallback value if that expression results in any standard Excel error (such as #DIV/0!, #N/A, or #VALUE!). This is highly recommended for templates where user data has not yet been populated.
Click on the cell containing the formula that is currently displaying the #DIV/0! error (for example, cell G28).
Click into the formula bar at the top of the worksheet to edit your existing calculation (e.g., =F28*C28/D28).
Type IFERROR( immediately after the equals sign. At the very end of your existing formula, type a comma, and enter your fallback value. Use "" (empty quotation marks) to leave the cell blank, or type 0. Add a closing parenthesis so your final formula looks like this: =IFERROR(F28*C28/D28, "").
Press Enter to save the changes. Then, click the small square fill handle at the bottom right corner of the active cell and drag it downward to copy this updated logic to the rest of the rows in your table.
Effortlessly Fix Formula Errors with WPS Spreadsheet
WPS Office provides a comprehensive Spreadsheet application that fully supports standard data manipulation and error handling formulas like IFERROR. It is perfect for cleaning up large datasets efficiently.
- 1. Open your file in WPS Spreadsheet: Launch the WPS Office application and open the spreadsheet containing the #DIV/0! errors.
- 2. Apply the IFERROR function: Double-click the target cell and update the formula using the formula bar to include IFERROR, such as =IFERROR(A1/B1, "").
- 3. Drag to fill the column: Click and drag the green fill handle at the bottom right of the cell downwards to apply the error-hiding rule to all similar calculations.
Frequently Asked Questions
Why does a #DIV/0! error occur in the first place?
A #DIV/0! error happens when a formula tries to divide a number by zero or by a cell that is entirely empty. Because division by zero is mathematically impossible, the software outputs this error flag.
Can I use ISERROR instead of IFERROR?
Yes, but IFERROR is much more efficient. ISERROR only returns a TRUE or FALSE outcome, which forces you to write a longer IF statement (e.g., =IF(ISERROR(A1/B1), "", A1/B1)). IFERROR combines this condition and the alternate result into one compact function.
Will the IFERROR function hide other types of formula errors?
Yes, IFERROR is a catch-all function. In addition to #DIV/0!, it will intercept and replace #N/A, #VALUE!, #REF!, #NUM!, #NAME?, and #NULL! errors.
