Leading the way in Microsoft Office Development

Step 1 - What is the Error Checking dialog box ?

This dialog box is probably the quickest way to identify any cells that contain errors on the active worksheet.

 Error Checking - If your worksheet contains errors then the Error Checking dialog box is displayed.

You can also display the Error Checking dialog box by selecting (Tools > Error Checking).

This can help you to work out why a formula is returning an error and help you to identify the cause.

When Excel cannot properly evaluate a formula it will display one of the default error values (#DIV/0!, #VALUE! etc).

There is a Trace Error button on the auditing toolbar that can be used to detect any formulas that returns an error.

Clicking once will trace all the arrows to cells with error values. You should remove any trace arrows before using.

Step 2 - Using the (Tools > Error Checking) dialog box

This dialog box is completely modeless and will only be displayed if the active worksheet contains an error.

The first cell that contains an error will be selected and the dialog box will be displayed.

The actual contents of the cell are displayed in the top left corner and a short description of the error is given below, in this case it is division by zero.

Help on this error - Displays a help topic relating to the specific type of error.

Show calculation steps - Displays the Evaluate Formula dialog box.

Ignore error - This will remove the green indicator from the top left hand corner of the cell containing the current error.

Edit in formula bar - Allows you to edit the formula in the formula bar. Press Resume to continue after you have made the changes.

Options - Displays the (Tools > Options)(Error Checking tab).

You can use the Previous and Next buttons to move between the errors on the active worksheet.

Step 3 - Error Checking Options

Enable background error checking - Select to have Microsoft Excel that checks cells to for errors at idle. If a cell is found to have an error, the cell with be flagged with a green indicator in the upper left corner of the cell.

Error indicator color - Automatic. Sets the colour Excel uses to indicate errors. If you click Automatic, the colour is set to the default colour of green.

Reset ignored errors - This will display a green indicator in the top left corner of any cells that contain errors. Errors can be ignored but this option will display them all again for the active worksheet.

Evaluates to error value - Select to have Microsoft Excel treat cells containing formulas that result in an error as an error and display a warning.

Text date with 2 digit years - Select to have Microsoft Excel treat formulas that contain text formatted cells with years represented as 2 digits as an error and display a warning when checking for errors.

Number stored as text - Select to have Microsoft Excel treat numbers formatted as text or preceded by an apostrophe as an error and display a warning.

Inconsistent formula in region - Select to have Microsoft Excel treat a formula in a region of your worksheet that differs from the other formulas in the same region as an error and display a warning.

Formula omits cells in region - Select to have Microsoft Excel treat formulas that omit certain cells in a region as an error and display a warning.

Unlocked cells containing formulas - Select to have Microsoft Excel treat an unlocked cell containing a formula as an error and display a warning when checking for errors.

Formulas referring to empty cells - Select to have Microsoft Excel treat formulas that refer to empty cells as an error and display a warning.

Step 4 - Getting Help

A message box will be displayed once the whole worksheet has been checked.

Step 5 - Things to Remember

You can choose not to ignore any errors by using the Options dialog box and clicking "Reset Ignored Errors".

If you have chosen to ignore the error by accident, pressing F2 and Enter will display the error indicator again