How to Use IFERROR in Excel - Practical Guide

Learn to use IFERROR to handle formula errors gracefully and display custom messages instead. This function intercepts errors like #DIV/0!, #N/A, and #VALUE!, replacing them with user-friendly text or alternative calculations. Essential for creating professional spreadsheets that won't break when data changes.

Why This Matters

IFERROR prevents spreadsheets from displaying distracting error codes, maintaining professionalism and readability. It's critical for financial reports, dashboards, and any sheet shared with non-technical users.

Prerequisites

•Basic understanding of Excel formulas
•Knowledge of common error types (#DIV/0!, #N/A, #VALUE!)

Step-by-Step Instructions

Open a spreadsheet with a problematic formula

Launch Excel and open a file containing a formula that produces errors (e.g., =A1/B1 where B1 might be zero).

Click the cell with the error

Select the cell displaying the error code you want to handle.

Edit the formula with IFERROR

Press F2 to edit the cell, then wrap your formula: =IFERROR(original_formula, "error_message"). For example: =IFERROR(A1/B1, "N/A").

Define the error output value

Enter what should display if an error occurs—text in quotes ("N/A"), a number (0), or another formula.

Press Enter and copy down if needed

Confirm the formula with Enter, then drag the fill handle down to apply IFERROR to other rows with similar calculations.

Alternative Methods

Use IFNA for #N/A errors only

If you only need to handle #N/A errors, use =IFNA(formula, value) instead of IFERROR for more specific control.

Combine with IF and ISERROR

Older method: =IF(ISERROR(formula), "error_message", formula) works in all Excel versions but is more verbose than IFERROR.

Tips & Tricks

✓Use empty string "" as the error value to hide errors completely without displaying anything.
✓Nest multiple IFERROR functions to handle different error scenarios sequentially.
✓Test your formula by intentionally creating the error condition to ensure IFERROR responds correctly.

Pro Tips

★Use IFERROR with VLOOKUP to display "Not Found" instead of #N/A when lookup values don't exist.
★For financial dashboards, nest IFERROR with calculations: =IFERROR(SUM(range)/COUNT(range), 0) to show 0 instead of #DIV/0!.
★Combine IFERROR with data validation to create self-cleaning sheets that auto-correct missing references.

Troubleshooting

IFERROR still shows the error code

Check that your syntax is correct and the formula is in the right cell. Recalculate the sheet (Ctrl+Shift+F9) to refresh all formulas.

IFERROR returns the error value even though the formula is correct

Your original formula may be producing an error you didn't expect. Test the formula without IFERROR first to identify the real issue.

IFERROR displays the error message in all rows instead of just error rows

Ensure your formula logic is correct; IFERROR only returns the error value when the first argument actually errors, not for every calculation.

Related Excel Formulas

IFNA IF ISERROR IFERROR alternatives

Frequently Asked Questions

What errors does IFERROR catch?

IFERROR catches all Excel errors: #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, and #GETTING_DATA. It's the most comprehensive error handler in Excel.

Can I nest IFERROR functions?

Yes, you can nest multiple IFERROR functions to handle different scenarios, but keep nesting depth reasonable for readability.

Does IFERROR slow down my spreadsheet?

No, IFERROR has negligible performance impact and is actually more efficient than older IF/ISERROR combinations.

What's the difference between IFERROR and IFNA?

IFERROR catches all errors, while IFNA only catches #N/A errors. Use IFNA when you specifically need #N/A handling.

Can IFERROR display a formula result as the error value?

Yes, the second argument can be a formula: =IFERROR(A1/B1, SUM(C:C)) will display the sum of column C if division errors.