How to Ignore All Errors in Excel & Google Sheets

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

 

 

When working in Excel, you can encounter many different types of errors, and they’re all indicated by a little green triangle in the upper left corner of a cell. This means that a cell contains an error. There are several ways to ignore errors in Excel.

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 that you have the SUM function in cell A3, which sums values from A1 and A2, but you misspelled it as SM.

 

 

Therefore, cell A3 contains the #NAME? error and the green triangle, instead of the result. To ignore the error and remove the green triangle, (1) click on the yellow icon with the exclamation mark and (2) choose Ignore Error.

 

 

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

 

 

Note: This doesn’t mean that the error is solved, however, it still exists, but Excel is not marking the cell as an error.

Turn Off Error Checking Options

Instead of manually ignoring errors, you can disable error checking options for the whole workbook. To do this, follow the next steps.

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

 

 

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

 

 

As a result, you won’t get 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, display a text you want, or keep the cell blank. To do this, you have to nest your formula into the IFERROR function. In cell A3, enter the formula:

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

 

 

The second argument of the IFERROR function (Formula Error) will be displayed in the cell if the formula in the first argument (SM(A1:A2)) has an error. If not, the result of the formula is displayed.

In Google Sheets, if a formula contains an error, a small red triangle appears in the right upper 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.