How to bulk insert IFERROR to existing Excel formulas!

We have already explored the IFERROR function in Excel. IFERROR (and IFNA) provide great ways to catch errors in Excel formulas and functions. Now, let’s take it to the next level: Bulk insert IFERROR and IFNA to existing functions. That means, you have many functions already and just want to wrap IFERROR or IFNA around them. Here is how to do that!

Method 1: Insert IFERROR by typing, copy & paste

Just quickly mentioning it here before you start the VBA battle: In some cases, it might be the fastest to examine your Excel calculations and then adjust 2-3 formulas. Next, copy and paste them. This might still be the fastest way.

Method 2: Use a VBA macro to insert IFERROR

How to insert VBA macros

Before we start, just a little reminder of how to insert VBA macros.

Open the VBA editor. The fastest way to do this would be to press Alt + F11 on the keyboard.
Alternatively, go to the Developer ribbon (if you don’t see the developer ribbon, you must activate it first) and click on Editor.
Right-click on the Excel file VBA name (or any other item below, for example the “Microsoft Excel Objects” folder icon as in the screenshot).
Go to Insert and click on “Module”.

Now it should look similar to number 4 and you see the white code area in which you can paste the VBA source codes from below.
Press Start to run the macro.

(For more help with VBA macros please refer to this article).

VBA Macro to bulk insert IFERROR functions to existing formulas

Just copy and paste the following lines of code (as described above) into a new VBA module and press start.

Sub insertIFERRORtoCells()

    On Error Resume Next

    Dim result As String

    result = InputBox("What text should be displayed in case of an error?", "Insert IFERROR function: Error Text")
    
    If StrPtr(result) <> 0 Then
        For Each cell In Selection.Cells
            On Error Resume Next
            If cell.HasFormula Then
                cell.Formula = "=IFERROR(" & Right(cell.Formula, Len(cell.Formula) - 1) & "," & result & ")"
            End If
        Next
    End If

End Sub

Disadvantages of the VBA method

The VBA macro above solves its purpose. Nevertheless, it comes with some disadvantages:

All formulas must start with the = sign. Formulas starting with =- or =+ might lead to errors.
If your existing formula already has an IFERROR function, it won’t be replaced but an additional IFERROR function is added around.
Of course, it comes with all the other disadvantages of VBA macros (for example, it’s just locally available, difficult to maintain, etc.)

So, please test this VBA macro carefully and better save a backup of your file before you use it!

Method 3: Insert IFERROR with just one click using Professor Excel Tools

Our Excel add-in has more than 120 features to simplify and speed up your work with Excel. One of them is to bulk-insert IFERROR (and IFNA) functions.

Just select all formulas you want to wrap into an IFERROR function, go to IFERROR on the Professor Excel ribbon and type the value or cell reference in case of an error. You can also just leave it blank: