Excel IFERROR: Syntax, Examples & Shortcut to Add/Remove
Microsoft Excel errors can appear for many reasons, especially when using complex Excel formulas.
Perhaps a lookup function cannot find a match. Or you divide by zero by accident. These errors can confuse users and disrupt other formulas. The IFERROR function allows you to replace them with cleaner outputs.
In this Excel tutorial, you will learn both the IFERROR basics and advanced use cases, plus keyboard shortcuts that let you add or remove IFERROR across multiple cells at once, boosting your productivity.
The IFERROR function in Microsoft Excel helps you manage errors in worksheet formulas, keeping your data analysis readable.
When there is an error, the IFERROR function returns a result that you choose. When there is no error, it returns the original result of the formula. This makes your spreadsheet look cleaner and overall better formatted. You will not see #N/A, #DIV/0!, or other error messages. You can show user-friendly text such as “n/a”, “Not found.” or simply a 0.
=IFERROR(value, value_if_error)
value: The formula, cell reference, or expression that might fail.
value_if_error: The fallback you want if an error occurs. This can be a number, text, empty string, or another formula.
When Excel calculates value:
No Error: IFERROR returns the normal result.
Error: If there is #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!, IFERROR returns value_if_error.
Excursion: Key Excel Error Types
#N/A: No matching value found; appears most often in lookup based formulas.
#VALUE!: Calculation involves an inappropriate data type, such as text where a number is expected.
#REF!: A referenced cell, row, or column was deleted or moved beyond the valid worksheet area.
#DIV/0!: The formula attempts division by zero or by an empty cell.
#NUM!: Numeric computation is impossible, for example a negative square root or an out-of-range iteration.
#NAME?: Excel does not recognise the function, range, or named constant, usually because of a spelling mistake or missing definition.
#NULL!: An incorrect space operator requests the intersection of two ranges that do not intersect.
#SPILL!: The intended spill range for an array result is blocked by existing data or merged cells.
IFERROR treats every one of these codes identically, replacing the error with the specified fallback. Knowing which code you are suppressing ensures you mask only the harmless cases and fix the root cause when necessary.
Let us say cell A2 holds 10 and B2 contains 0 (a classic blank cell / zero divisor combo).
The formula =A2/B2 would normally show #DIV/0!. However, wrapping it with IFERROR provides a custom message or a numeric fallback:
=IFERROR(A2/B2, "Error in calculation")
When B2 = 5, the result is 2 (A2 / B2).
When B2 = 0, you see “Error in calculation.”
I often use divisions for calculation KPIs such as growth rates. In such cases, I prefer to have 0 as output (perhaps shown as -) instead of “Error in calculation.” This makes KPIs much easier to look at.
Division by zero leads to #DIV/0!. It often happens when the denominator is 0 or blank. In financial models, it can happen if the prior period value is 0. You can use IFERROR to show a clearer result:
=IFERROR(A1/B1, 0)
Lookup formulas such as VLOOKUP or INDEX/MATCH can return #N/A if a match is not found. Wrap them in IFERROR to provide a more friendly message:
=IFERROR(VLOOKUP("ABC123", Table1, 2, FALSE), "Not found")
If “ABC123” (the lookup value) is not in the table, IFERROR returns the custom message “Not found” (hitting the recommended keyword not found). If the lookup value is found, you see the normal lookup result.
Note: In newer Excel versions, XLOOKUP has an if_not_found argument, which can replace IFERROR for lookups. However, IFERROR is still helpful in older Excel or for more complex logic.
Functions like SUM, COUNTIF, or SUMIFS might fail if an iserror pops up in at least one cell reference. With IFERROR, you can show 0 or a dash instead of an error, ensuring conditional totals work smoothly. IFERROR protects the result:
=IFERROR(SUM(B2:B10), 0)
=IFERROR(SUMIFS(Sales, Region, "East") / SUMIFS(Leads, Region, "East"), "-")
(This example is for completeness, but it is often best to fix errors in the source data for SUMIFS. That is better than hiding them with IFERROR.)
Sometimes you want more than one lookup. If your VLOOKUP function in the following formula fails, try a second, then show 'Not found'.
=IFERROR( VLOOKUP(A1, Table1, 2, FALSE), IFERROR(VLOOKUP(A1, Table2, 2, FALSE), "Not found") )
Excel checks Table1 first. If that fails, it checks Table2. If both fail, it returns “Not found.”
=SUM(IFERROR(B1:B3/B1:B3, 0))
| A | B | C | |
|---|---|---|---|
| 1 | Alpha | 100 | 2 |
| 2 | Bravo | 200 | 5 |
| 3 | Charlie | 0 | 0 |
Here is what happens:
The formula divides each value in column B by the corresponding value in column C (for example, 100/2, 200/5, and 0/0), which gives you the array {50; 40; #DIV/0!}.
The IFERROR part swaps any errors (like the division by zero) with 0, turning the array into {50; 40; 0}.
Finally, SUM adds up these numbers for a total of 90.
Use IFERROR Intentionally
Do not hide errors without a reason. You might mask real issues.
Wrap Only the Vulnerable Par
For example, if you have
=SUMIFS(...) / SUMIFS(...), you can wrap only the division or the denominator.Choose Logical Fallbacks
For numeric calculations, 0 or "" (blank) is often good. For lookups, “Not found” or “N/A” is clearer.
Consider Performance & Validation
Many nested IFERROR formulas, especially when paired with heavy COUNTIF or VLOOKUP functions. can slow calculations. Sometimes a helper cell/column is better.
Consider Data Validation
Data validation can catch invalid inputs before formulas run. However, IFERROR still helps if you have unexpected data.
Accelerate Excel, our Microsoft Excel add-in, helps you work faster in Excel. For instance, with shortcuts to add or remove IFERROR in many cells at once.
With the Add IFERROR to Formula utility you can wrap each formula with =IFERROR(original, 'customized message') with a keyboard shortcut.
Sometimes you get a workbook where every formula is wrapped in IFERROR. This could hide real problems. With Accelerate Excel, you can remove IFERROR functions from the selected cells with a shortcut.
The add-ins Objects feature (among others) lists all ranges with formula errors and lets you navigate through them, so you can check errors that might need IFERROR handling.
Note: It also lists PivotTables, charts, hyperlinks and cells with data validation.
Speed: You can update many formulas in seconds.
Consistency: All error handling looks the same.
Convenience: A shortcut beats manually writing out formulas.
No Need for Complex VBA: Tech-savvy users could write their own code, but making it robust and fast (i.e. to handle 1000 cells at once instantly) takes time. That time is better spent on your core tasks.
Downloading and adding Accelerate Excel to your Excel is easy. Visit our download page for instructions and the download link.
Excel’s IFERROR function is key for managing formula errors and formatting spreadsheets, letting you display clear outputs instead of error codes.
With Accelerate Excel, you can add or remove IFERROR via keyboard shortcuts across multiple cells simultaneously, instead of manually writing the formula.
How do I make IFERROR return a blank cell instead of an error?
Use =IFERROR(A1/B1, "") – the empty string hides the error.
How do I fix a #DIV/0! error in Excel?
Wrap the division in IFERROR: =IFERROR(A1/B1, 0).
How do I hide #N/A errors in VLOOKUP results?
Use IFERROR(VLOOKUP(X, Range, 2, FALSE), "Not found").
What's the difference between IFERROR and IFNA Function?
IFERROR traps every Excel error; IFNA only #N/A.
What is the ISNA and ISERROR Function?
ISNA checks if a value is #N/A; ISERROR checks for any Excel error.