📎 This article includes 1 downloadable practice file ↓
In this article
- Syntax & Parameter Breakdown
- Where You Will Use It
- Practical Worked Examples
- Mistakes People Make & How to Avoid Them
- Frequently Asked Questions
- Which errors does IFERROR catch?
- What is the difference between IFERROR and IFNA?
- Does IFERROR slow down workbooks?
- IFERROR in practice
- Where you will use it
- Worked example
- Mistakes people make
- Continue learning
The IFERROR function in Excel is an essential spreadsheet tool designed to Catch and handle formula errors cleanly across your spreadsheet, replacing ugly #N/A, #VALUE!, and #DIV/0! alerts with custom fallbacks.
Syntax & Parameter Breakdown
=IFERROR(value, value_if_error)| Argument | Requirement & Description |
|---|---|
value |
Required. The formula, cell reference, or calculation you want to evaluate. |
value_if_error |
Required. The fallback value to return if the formula results in an error (e.g. 0, “-“, or “Not Found”). |
Where You Will Use It
Crucial for executive dashboards, client-facing reports, and financial models where unhandled errors break downstream SUMs and look unprofessional.
Practical Worked Examples
Here are common real-world formula scenarios utilizing IFERROR:
| Formula | Expected Behavior & Purpose |
|---|---|
=IFERROR(VLOOKUP(A2, Products!A:D, 4, FALSE), "New Item") |
Returns ‘New Item’ instead of #N/A if the product ID does not exist in the database. |
=IFERROR(B2 / C2, 0) |
Safely computes growth rate or ratios, returning 0 instead of #DIV/0! when denominator is zero. |
=IFERROR(XLOOKUP(A2, Staff!A:A, Staff!C:C), "-") |
Clean dash fallback for missing personnel records. |
Mistakes People Make & How to Avoid Them
Avoid wrapping entire complex formulas blindly with IFERROR during development, as it masks syntax typos (like misspelling a function name) that you need to fix.
Frequently Asked Questions
Which errors does IFERROR catch?
IFERROR intercepts all standard Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.
What is the difference between IFERROR and IFNA?
IFNA only catches #N/A errors (common in lookups), letting other errors like #REF! or #DIV/0! alert you to formula bugs.
Does IFERROR slow down workbooks?
No, Excel evaluates value_if_error only if the primary formula actually triggers an error.
📚 Master spreadsheet formulas with our Free Excel Course or explore related functions in the Formula Lab.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
IFERROR in practice
Where you will use it
- Show a friendly message instead of #DIV/0! in ratios
- Clean up report formulas before printing
- Default values for incomplete data
Worked example
Sales per visit, with a dash where it can't be calculated.
| Sales | Visits | Formula | Result |
|---|---|---|---|
| 45000 | 30 | =IFERROR(A2/B2,"-") | 1500 |
| 12000 | 0 | =IFERROR(A3/B3,"-") | - |
| 8000 | n/a | =IFERROR(A4/B4,"-") | - |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Wrapping every formula in IFERROR | It hides real mistakes; use IFNA for lookups and fix genuine errors |
| Returning 0 in averages | Return "" so averages ignore it |
📎 Practice files for this article
- 📗Practice workbook: Logic, LET & LAMBDA17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.