An IFERROR Excel formula replaces a calculation error with a result you choose, such as a warning, blank-looking cell, or zero. It is useful when an error is expected, but it can also conceal a broken reference or incorrect source data. This guide shows when to use IFERROR, when to use IFNA instead, and how to keep office reports trustworthy.
How Does an IFERROR Excel Formula Work?
The syntax is =IFERROR(value,value_if_error). Excel calculates the first argument. If that calculation succeeds, it displays the normal result; if it produces a recognized error, Excel displays the second argument instead. For example, =IFERROR(12/3,"Check input") returns 4, while =IFERROR(12/0,"Check input") returns Check input. You are not fixing the underlying division by zero; you are deciding how the report presents it.
According to Microsoft’s IFERROR documentation, the function handles #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. This broad coverage explains both its convenience and its risk. A missing customer code and a deleted worksheet reference can produce the same friendly fallback even though they need different responses.
Example 1: Show a Clear Warning for a Missing Lookup
Imagine a purchasing report with product IDs in A2:A5 and approved prices in B2:B5. Put P-101, P-102, P-103, and P-104 in A2:A5, with corresponding prices 12, 18.50, 7.25, and 24 in B2:B5. An order sheet has the requested product code in D2. For a workbook that uses VLOOKUP, enter =IFERROR(VLOOKUP(D2,$A$2:$B$5,2,FALSE),"Check product code") in E2.
With D2 set to P-102, E2 returns 18.50. With D2 set to P-999, it returns Check product code. That is more useful than an unexplained blank because the purchaser knows to investigate. However, IFERROR also hides unrelated errors in the VLOOKUP expression. During setup, test the lookup without IFERROR and confirm the range and column number before applying the message.
If your team uses a supported Excel edition, our XLOOKUP office examples show a more direct lookup pattern with an optional not-found argument. A missing lookup should never silently become an approved price of zero.
Example 2: Calculate a Rate Without Inventing Zero Performance
Suppose B2 contains completed requests and C2 contains total requests. The completion rate is =B2/C2. When B2 is 18 and C2 is 24, the result is 0.75, which displays as 75% after percentage formatting. If C2 is zero, Excel returns #DIV/0!. You might use =IFERROR(B2/C2,"Not available") so the dashboard clearly says the rate cannot yet be calculated.
A common shortcut is =IFERROR(B2/C2,0). That returns zero when C2 is zero, but 0% suggests a measured failure rather than a missing denominator. It may also change averages or summary statistics if other formulas treat the replacement as numeric data. For performance reporting, use a meaningful text label or a separate status field until a valid denominator exists.
If zero is genuinely the agreed business rule, document that rule beside the metric. A mathematically tidy spreadsheet is not automatically a truthful management report.
Example 3: Choose Between Zero, Blank, and a Message
The replacement value is a reporting decision, not merely a formatting choice. Compare these three formulas applied to a calculation that currently errors:
| Formula | Displayed fallback | Appropriate use |
|---|---|---|
=IFERROR(B2/C2,0) | 0 | Only when zero is the correct defined value |
=IFERROR(B2/C2,"") | Blank-looking cell | Presentation where missing values are separately tracked |
=IFERROR(B2/C2,"Review input") | Review input | Operational sheets requiring human follow-up |
A formula returning "" is still a formula cell, not an actually empty cell. Averages generally ignore that text result, while an inserted zero can affect the average. Text messages can also interfere with calculations if another formula expects a numeric value. Keep the calculation column numeric when possible, and place explanatory status text in an adjacent column.
When Is IFNA Safer Than IFERROR?
Use IFNA when the only expected problem is that a lookup cannot find a match. The formula =IFNA(VLOOKUP(D2,$A$2:$B$5,2,FALSE),"Not found") replaces #N/A, but still exposes errors such as #REF! or #NAME?. That makes it easier to notice a damaged workbook. Microsoft’s IFNA reference confirms this narrower behavior.
For a new XLOOKUP formula, the optional fourth argument already provides a not-found response: =XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,"Not found"). That avoids wrapping the entire lookup in IFERROR. Microsoft notes that XLOOKUP is not available in Excel 2016 or Excel 2019, so choose a formula that colleagues can open and recalculate in their actual software version.
When Does IFERROR Hide a Serious Spreadsheet Problem?
Suppose a report originally references a price on another sheet, but a colleague deletes the referenced column. Without error handling, the calculation displays #REF!, drawing attention to the broken reference. If the formula is wrapped in =IFERROR(original_formula,0), the report instead shows zero. A manager may mistake that number for a valid price, margin, or cost and make a decision from it.
The same problem arises with #NAME? from a misspelled function or #VALUE! caused by unexpected text in numeric fields. A broad fallback can turn an obvious error into a plausible but incorrect result. The safer sequence is to inspect the original formula, identify the expected error condition, correct unexpected errors, and only then add targeted handling.
For recurring reports, record who owns the source table and when it was last updated. The goal is to make an exception visible to the person who can fix it, not simply to remove an unpleasant symbol from the page.
A Practical Two-Column Error Review Pattern
Consider a weekly office tracker where column B holds completed items and column C holds assigned items. In D2, enter =IF(C2=0,"",B2/C2) and format the result as a percentage. In E2, enter =IF(C2=0,"Missing total",IF(B2>C2,"Check counts","OK")). Copy both formulas down the active rows. This separates the numeric rate from the explanation.
For example, a row with B2=6 and C2=8 shows 75% and OK. A row with B3=0 and C3=0 shows a blank-looking rate and Missing total. A row with B4=12 and C4=10 shows 120% and Check counts. The last case is especially important: IFERROR would not flag 120%, because it is a valid calculation even if the input data is questionable.
If you later summarize the tracker with counts, our COUNTIF and COUNTIFS guide explains how to count flagged rows without confusing error labels with numeric results. This pattern is a simple illustration, not a substitute for your organization’s approved metric definitions.
How to Audit Existing IFERROR Formulas Before Sharing a Workbook
Start with a copy of the workbook so you can inspect formulas without disrupting colleagues. Use Excel’s Find command to search formulas for IFERROR(, then review the highest-impact columns first: totals, prices, rates, deadlines, and any figures copied into presentations. For each formula, temporarily examine its first argument without the wrapper in a safe review copy.
- Identify the specific error you expect, such as an absent lookup key or zero denominator.
- Confirm source ranges, column types, and any fixed references.
- Test one known valid input and one intentionally missing input.
- Decide whether the fallback should be text, blank-looking, or numeric.
- Keep unexpected errors visible and assign an owner for corrections.
- Check dependent totals and averages after changing the formula.
Do not mass-replace every error with zero. A small audit column that counts rows requiring review is often more valuable than a visually perfect report. The conditional formatting guide also shows how to make status exceptions more visible without changing the underlying data. For a wider workbook check, use the messy-data PivotTable walkthrough to understand why source quality must be addressed before summaries are trusted.
Common IFERROR Questions
Does IFERROR fix a formula error?
No. It changes the result shown when the first argument errors. The original reference, lookup mismatch, or invalid input may still require correction.
Should I use IFERROR around every Excel formula?
No. Use it only where an expected error has a meaningful fallback. Otherwise, visible error codes can be essential diagnostic information.
Why does IFERROR return zero when I expected a blank?
Check the replacement argument and whether the original calculation returns a genuine zero rather than an error. IFERROR does not run its fallback when the first argument successfully evaluates to zero.
Can IFERROR prevent a wrong but valid lookup result?
No. If a lookup finds the wrong row because of duplicate identifiers or an unsuitable match setting, Excel may return an ordinary value. Review keys, source ranges, and matching rules independently.
Bottom Line: Make Exceptions Visible, Not Just Attractive
Use an IFERROR Excel formula to communicate a known, expected exception—not to suppress every warning in a workbook. Prefer IFNA or XLOOKUP’s not-found argument for missing lookup matches, and keep important calculation failures visible until someone resolves them. Before distributing a report, compare a valid row, a missing-data row, and a suspicious-but-numeric row. Those three checks help ensure that cleaner spreadsheet output still tells the truth.