How to Use the Error Checking Command in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 21, 2023

This tutorial demonstrates how to use error checking in Excel and Google Sheets.

error checking intro

Background Error Checking

Errors in Excel formulas usually show up as a small green triangle in the top-left corner of a cell. If you click in the cell that contains an error, a list of possible actions drops down.

error checking background check menu

If the background error checking options are not switched on in Excel, no triangle shows up, even though the cell contains an error.

error checking no triangle

To ensure the error triangle is visible, thus making error checking easier, switch on background checking in Excel Options.

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

error checking background check options

  1. Tick Enable background error checking, and then click OK. (Usually, this is already checked by default.)

How to Use Error Checking

  1. With the file that contains the errors open in Excel, in the Ribbon, go to Formula > Formula Auditing > Error Checking.

error checking menu

  1. In the Error Checking dialog box, click Show Calculation Steps.

error checking show error

  1. Click Evaluate.

error checking evaluate

  1. Keep clicking evaluate until you get the message: The next evaluation will result in an error.

error checking result

The error is shown in the formula in the dialog box.

error checking error found

  1. Click Close and then Next to move to the next error. Click Trace Error to get more information on the source of the error.

error checking next error

Excel highlights the error with tracing arrows, allowing you to see where the error originates. In this case, the #VALUE in the error is coming from the #VALUE in the previous error but the formula in the formula bar looks fine.

error checking trace check

  1. Click Previous to move to the previous error. The cursor, in this case, moves to E15. The formula in E15 is shown in the formula bar. Here, you can see that the formula is multiplying a value (e.g., in D15) with text (e.g., in A3).

error checking trace previous

  1. Click Edit in Formula Bar.

error checking edit in formula bar

  1. Adjust the formula as appropriate, and then click Resume.

error checking edit formula

Error checking stops once all the errors in the worksheet have been addressed.

error checking finished

Note: This example showed #VALUE errors, but error checking finds all “#formula errors. There is a different option for circular references.

Error Checking in Google Sheets

Error checking is automatic in Google Sheets. As soon as you have a cell that contains an error, details about that error show up on the screen.

error checking gs intro

AI Formula Generator

Try for Free

See all How-To Articles