In Excel, use IFERROR to return a chosen result when a formula produces an error: it has the form =IFERROR(value, value_if_error). For example, if A2 contains 1200 and B2 contains 0, =IFERROR(A2/B2,0) returns 0 instead of displaying #DIV/0!.
What IFERROR does
IFERROR evaluates an expression or formula. If that calculation succeeds, Excel returns its ordinary result; if it produces an error, Excel returns the fallback you specify. The function is available in Microsoft Excel for Microsoft 365 on Windows, and it is also supported in current Mac and web versions.
It can catch common formula errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. It can also handle errors produced by newer Excel calculations. It does not repair the underlying cause: it replaces the displayed error with the fallback result.
That distinction matters. A clean-looking cell may still represent missing data, a bad reference, or an invalid calculation. Use IFERROR when a fallback is meaningful, not simply to make every error disappear.
How the syntax and evaluation work
The first argument, called value, is the calculation Excel should attempt. The second argument, value_if_error, is what Excel returns if the first argument evaluates to an error. The fallback can be a number, text, a blank-looking empty string, or another expression.
Excel evaluates the first argument before deciding which result to return. If it is not an error, the fallback is ignored. If it is an error, Excel returns the fallback instead. The function therefore gives you one place to specify error handling around an existing calculation.
In an English-language Excel installation, formulas commonly use commas between arguments, as in the examples here. Depending on regional settings, Excel may require semicolons instead. If a formula with commas is rejected, check the list separator configured for your installation.
Example: handle division by zero
Suppose A2 contains a sales amount and B2 contains the number of units. To calculate the amount per unit, divide the first cell by the second. When B2 is zero or empty, ordinary division returns an error; IFERROR lets you choose a response. With the sales amount in A2 and unit count in B2, enter this in C2:
=IFERROR(A2/B2,0)
If A2 is 1200 and B2 is 30, the result is 40 because 1200 divided by 30 equals 40. If B2 is 0, the division produces #DIV/0!, so C2 displays 0 instead. That zero may be suitable for a report that treats an unavailable rate as zero, but it can also be misleading: readers may interpret it as a genuine rate of zero.
If an error means the rate cannot be calculated, a text message can be clearer. With the same values in A2 and B2, enter this in C2:
=IFERROR(A2/B2,"Check inputs")
Now a zero divisor produces the words “Check inputs,” while valid data still produces the calculated rate. Another option is a blank-looking result. With the same cell layout, enter this in C2:
=IFERROR(A2/B2,"")
The empty string is text, not a truly empty cell. Functions, charts, or other formulas may treat it differently from a blank cell, so choose it only when that behavior suits the workbook.
Use IFERROR with lookup formulas
A lookup can return #N/A when its search value is not present. For example, assume E2 contains an employee ID, A2:A20 contains IDs, and B2:B20 contains employee names. This formula returns the matching name or a message if the lookup calculation errors:
=IFERROR(VLOOKUP(E2,$A$2:$B$20,2,FALSE),"Not found")
FALSE asks VLOOKUP for an exact match, and the dollar signs keep the table range fixed if you copy the formula down. If E2 matches the ID in A7, Excel returns the corresponding name from B7. If there is no match, the fallback text appears. Because IFERROR catches more than missing-match errors, it could also mask another problem in the lookup formula, such as a broken reference.
If you want to replace only a not-found result and allow other errors to remain visible, use IFNA. With the same IDs in E2 and A2:A20 and names in B2:B20, enter this in the result cell:
=IFNA(VLOOKUP(E2,$A$2:$B$20,2,FALSE),"Not found")
IFNA handles #N/A specifically. This narrower behavior is often useful for lookups because a damaged reference or other unexpected error remains visible for investigation.
Choose a fallback that preserves meaning
The fallback is part of the meaning of your spreadsheet, not just a formatting choice. A zero, a message, and an empty string communicate different things to the person reading the result and can affect calculations downstream.
- Use zero only when zero is a valid and honest substitute for the error in that context. It can change averages and totals if the fallback is included in later calculations.
- Use a short message when someone needs to investigate or supply missing information. Text makes the issue visible, although it may prevent arithmetic that expects a number.
- Use an empty string when you want the cell to look blank and downstream use of text is acceptable. Remember that it is not the same as a genuinely empty cell.
- Return another calculation when there is a defensible alternate method. Make sure the alternate result does not conceal a data-quality issue.
For a shared workbook, explain what the fallback means in a nearby heading, note, or instruction. For instance, a blank could mean “not applicable,” “not entered,” or “calculation failed”; those are not interchangeable.
IFERROR compared with IF and error-checking functions
IFERROR is concise, but it does not let you choose which errors to handle. IFNA is narrower and catches only #N/A. Use it when a missing lookup is expected but other formula problems should remain apparent.
An IF formula can test a condition before carrying out a calculation. For example, if A2 holds a numerator and B2 holds a denominator, this formula checks whether B2 is zero before dividing:
=IF(B2=0,"Check inputs",A2/B2)
With A2 equal to 1200 and B2 equal to 0, it returns “Check inputs.” With B2 equal to 30, it returns 40. This approach makes the specific condition explicit. It does not automatically handle unrelated errors in the division, so those can still appear.
Error-checking functions such as ISERROR and ISNA can also be used when a formula needs to branch based on whether a value is an error. That can be useful for more detailed logic, but it usually takes more work than wrapping a calculation in IFERROR. Pick the simplest approach that handles the actual situation without hiding errors you need to notice.
Common problems and how to diagnose them
- The formula returns the fallback every time. Check the expression inside IFERROR. It may consistently be producing an error, for example because a divisor is zero or a lookup value is absent.
- The fallback hides a broken formula. Temporarily remove IFERROR or inspect the inner expression in a separate cell. This can reveal an invalid reference, misspelled function, or incompatible data type.
- A zero result looks like a valid answer. Consider replacing zero with a message, or use a condition that handles only the expected error. Confirm whether downstream totals or averages include the substituted value.
- The formula is rejected when entered. Check parentheses, quotation marks around text, and the argument separator required by your regional settings. The examples use straight double quotes and commas.
- A result appears blank but is not empty. If the fallback is an empty string, the cell still contains a formula. That distinction can matter for counts, filters, or formulas that test for blank cells.
To investigate an error in Excel for Microsoft 365 on Windows, select the formula cell and inspect the formula bar. The Formulas tab also includes Error Checking tools. On Mac, the ribbon includes formula-auditing and error-checking tools as well, although their arrangement can vary by Excel version. Checking the original formula before adding a fallback is usually the fastest way to determine whether the error is expected.
Frequently Asked Questions
What is the basic IFERROR formula in Excel?
Use =IFERROR(value, value_if_error) as the function pattern. Replace the first argument with the calculation and the second with the result you want when that calculation errors.
Does IFERROR catch #N/A?
Yes. IFERROR catches #N/A as well as other formula errors. If you want to handle only #N/A, use IFNA instead.
Can IFERROR return a blank cell?
It can return an empty string using two quotation marks as its fallback. The cell looks blank, but it still contains a formula and is not truly empty.
Why is IFERROR showing zero?
That usually means the calculation is producing an error and zero is the fallback you specified. Inspect the calculation itself to find the cause, and decide whether zero communicates the right result.
Does IFERROR fix the error?
No. It changes what Excel displays when the calculation errors; it does not correct the formula, source data, or reference that caused the error.
Related
- How to Split Cells in Half in Excel: 3 Practical Methods
- Split Cells in Excel: Shortcut Key and Text to Columns
- How to Split an Excel Page into Separate Panes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find the Minimum in Excel with the MIN FunctionTo find the smallest number in Excel, use the MIN function. With the numbers you want to check…
- Calculate Hours in Excel: Formulas and Time FormatsTo calculate elapsed hours in Excel, put the start time in A2 and end time in B2, then…
- Excel COUNTIF for Numbers: Exact and Range MatchesYes. In Excel, use COUNTIF to count cells that match a number or a numeric condition: for example,…
- Excel HLOOKUP: Return a Column NumberHLOOKUP does not return the number of the column where it finds a match; it returns a value…
- Copy Formula in Excel Without Dragging: 6 WaysTo copy a formula in Excel without dragging, copy and paste it, use Ctrl+D to fill down, or…
- Excel Data Analysis Books: How to Choose the Right OneGood Excel data-analysis books include Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston for business…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.