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.
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 Excel 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!

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