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

This tutorial will demonstrate 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-hand corner of a cell. If you click in the cell that contains an error, a drop down list is enabled from which you can select the option you require.

 

error checking background check menu

 

If the background error checking options are not switched on in Excel, then this triangle will not show up, but the cell will still contain an error value.

 

error checking no triangle

 

In order to ensure you see the triangle, which makes error checking easier, make sure background checking is switched on in Excel options.

1. In the Ribbon, select File. Then select Options > Formulas.

 

error checking background check options

 

2. Make sure the check-mark “Enable background error checking” is checked and then click OK. (Usually, this is already checked by default in Excel.)

How to Use Error Checking

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

 

error checking menu

 

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

 

error checking show error

 

OR

Click on the small green triangle in the left-hand side of the cell that contains the error, and select Show Calculation Steps.

 

error checking calc steps

 

3. Click Evaluate to evaluate the error.

 

error checking evaluate

 

4. 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

 

5. Click Close, and then (1) click Next to move to the next error. Then (2) click Trace to trace the error.

 

error checking next error

 

Excel will highlight the error with tracing arrows allowing you to see where the error is originating from. 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

 

6. Click (1) Previous to move to the previous error. The cell pointer will in this case (2) move to E15. The formula in E15 is shown (3) 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

 

7. Click Edit in Formula bar.

 

error checking edit in formula bar

 

8. Amend the formula as appropriate, and then click Resume.

 

error checking edit formula

 

If all the errors in the worksheet are fixed, Error Checking will stop.

 

error checking finished

 

This example showed #VALUE errors, but Error Checking will find all # 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, it will show up on the screen with details about the error.

 

error checking gs intro