See all How-To Articles

How to Ignore All Errors in Excel & Google Sheets

This tutorial demonstrates how to ignore all errors in Excel and Google Sheets.

ignore formula error

When working in Excel, you can encounter a variety of errors. Errors are indicated by a little green triangle in the upper-left corner of a cell. Read on for to ignore errors and hide those triangles.

Ignore Error in a Single Cell or Range

If you have an error in a single cell or in a range of cells, you can ignore them easily. Say you are using the SUM Function in cell A3 to add up values from A1 and A2, but you misspelled it as SM.

ignore formula error

Therefore, cell A3 contains the #NAME? error and the green triangle, instead of the result.

  1. To ignore the error and remove the green triangle, click on the yellow icon with the exclamation mark.
  2. Choose Ignore Error.

ignore formula error 1

As a result, this cell is no longer marked as an error, and the green triangle has disappeared.

ignore formula error 2

Note: This doesn’t mean that the error is solved, however. It still exists, but Excel just isn’t marking the cell.

Turn Off Error Checking Options

Instead of manually ignoring errors, you can disable error checking options for the whole workbook.

  1. In the Ribbon, go to File > Options.

ignore formula error 3

  1. In the Excel Options window, go to the Formulas tab, uncheck Enable background error checking, and click OK.

ignore formula error 4

As a result, you won’t see green triangles in cells with formula errors.

Use IFERROR Function

Although previous options hide the green arrow from a cell, the error (#NAME? or similar) is still present in the cell. If you want to avoid displaying the error, you can use the IFERROR Function to display some text or to keep the cell blank. Nest your original formula into an IFERROR formula.

For this example, in cell A3, enter the formula:

=IFERROR(SM(A1:A2),"Formula Error")

ignore formula error 5

The first argument is the original formula (SM(A1:A2)), and if the formula is valid, its result appears in the cell. The second argument of the IFERROR Function (Formula Error) is what populates the cell if the formula in the first argument has an error.

The IFERROR Function in Google Sheets

In Google Sheets, if a formula contains an error, a small red triangle appears in the upper-right corner of the cell. There is no option to ignore it as in Excel, but you can use the IFERROR Function in the same manner.

ignoreerrors gs

See all How-To Articles