Error Checking

When using formulas you should always check for errors.
The Formula Auditing group on the Formulas tab contains commands to help you identify and fix formula errors.
The most useful command is the Error Checking drop-down.

Error Checking - Displays the Error Checking dialog box, if your worksheet contains errors.
Trace Error - Clicking once will trace all the arrows to cells with error values. You should remove any trace arrows before using.
Circular References - A sub-menu displaying the cells that contain Circular References.

Error Checking Dialog Box

This dialog box is probably the quickest way to find any cells that contain errors on the active worksheet.
The first cell that contains an error will be selected when this dialog box is displayed.
You can use this dialog box to find and investigate any errors one by one.
This dialog box is completely modeless and can only be displayed when the active worksheet contains at least one error.
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 Excel Options, Formulas Tab.
You can use the Previous and Next buttons to move between the errors on the active worksheet.
This option is available even if you have switched off background checking.

Background Error Checking

Excel will perform background error checking for you automatically (by default).
You can change this setting from the Options, Formulas Tab.

When an error is identified, a small green triangle is displayed in the top left corner of the cell.
Selecting this cell will display a tooltip that gives you a short description as well as a Smart Tag.

Most of the time the reason you are seeing a green triangle is because Excel cannot evaluate the formula.
Two of the most common formula error values are #DIV/0! and #VALUE!

Ignoring Errors

If there are any errors in your formulas they will be indicated by the Error Checking Smart Tag.
If the error checking rules have been switched off you can use the Error Checking dialog box instead.
Both the smart tag and the dialog box give you an option to "Ignore Error".
If you ignore an particular error, the smart tag will disappear and the error will no longer appear in the Error Checking dialog box.
It is possible to reset all your "ignored errors" using a button on the Options, Formulas Tab.

Error Check Complete

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


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

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext