IFERROR Excel Shortcut: How to Error-Wrap Your Formulas

Excel often contains many interconnected formulas that can produce errors if a cell contains an invalid value. These errors can crash the model and make it unusable. The IFERROR function provides an easy way to handle potential errors and prevent them from breaking your spreadsheets.

 

Try Macabacus for 10x Productivity

Join the 80,000+ finance & banking professionals using Macabacus to get more done in Microsoft Office.

Start your Free Trial
 

What is IFERROR?

IFERROR is an Excel function that allows you to capture potential errors in a formula and replace the error with a value you specify. The syntax is: =IFERROR(value,value_if_error)

Where:

  • Value is the formula that may produce an error
  • Value_if_error is the value to return if an error occurs

IFERROR checks if the first argument returns an error. If it does, it will return the second argument. If not, it will return the first argument.

Where to Use IFERROR in Excel Models

IFERROR is useful whenever errors need to be contained to preserve model stability. Some common examples include:

  • Division Wrapping divisors in IFERROR prevents #DIV/0! errors. =IFERROR(100/B12,0)
  • VLOOKUP Lookup functions may fail if data changes. IFERROR displays a message. =IFERROR(VLOOKUP(A3,$D$2:$E$100,2,FALSE),”No match”)
  • External links Links to other workbooks may break if locations change. =IFERROR(‘[\Sales.xlsx]Sheet1’!A1,”Sales unavailable”)
  • Data validation If inputs are restricted via Data Validation, errors can appear if invalid entries occur. =IFERROR(A1,””)
  • Interim calculations Prevent errors in interim formulas from spreading through the model. =IFERROR(B4*B5,0)
  • Error checking Purposefully generate errors to test downstream impacts. =IFERROR(1/0,”Divide by zero error”)

Best Practices When Using IFERROR

Follow these tips when using IFERROR to help you create clean models and debug for accuracy.

  • Be descriptive Use clear messages that indicate the error reason and any action needed.
  • Use judiciously Don’t overuse IFERROR just to hide errors. Fix the root cause when feasible.
  • Nest multiple IFERRORs You can nest IFERRORs to handle different potential errors. =IFERROR(IFERROR(A1/B1,0), “Error”)
  • Place at start – Put IFERROR at the start of a formula containing many operations. =IFERROR((A1+A2+A3…),0)
  • Use on final outputs No need to use on intermediate calculations, only final error-sensitive outputs.
  • Keep formulas simple Complex nested formulas are harder to debug. Break formulas into steps.
  • Test thoroughlyRigorously test all error scenarios to ensure proper handling.

Using Macabacus to Easily Add IFERROR Protection

Adding IFERROR protection across large models with thousands of formulas can take a very long time. Creating a VBA script is an option, but you’ll then need to manage that script and your own VBA library. Fortunately, Macabacus, a popular Excel add-in for financial modeling, can make this process run a lot smoother.

With Macabacus, you can select a range of cells and instantly wrap every formula with IFERROR. Just select the target cells, open the Macabacus Formula Audit ribbon tab, choose Wrap Formulas with IFERROR, and specify the value to return on error.

Here’s a quick video demonstrating that:

Macabacus will scan all selected cells, identify those containing formulas, and wrap them with IFERROR. This makes it fast and easy to protect your model’s key outputs. Macabacus also includes model debugging tools to visualize errors, perform mass error checking, break formula circularities, and more. Combined with IFERROR wrapping, Macabacus provides a robust solution to error-proof models.

Try Macabacus today, free for 30 days and experience the ease of wrapping your formulas with IFERROR.

Discover more topics

Build an operating model
In this tutorial, we will walk through how to build a general industry business operating model.
Read more
Build an M&A model
In this section, we demonstrate how to model a merger of two public companies in Excel.
Read more
Build an LBO model
In this tutorial, we will walk you through building an LBO model in Excel.
Read more
Asset and Stock Deals
The first step in purchase price allocation, or PPA, is to determine the purchase price.
Read more