Wednesday, February 16, 2005

Formula Auditing in Excel

Excel has some great visual auditing tools. To find them right click a blank part of the toolbar area and turn the Formula Auditing toolbar on.
The first button does error checking. It will check errors in the sheet. When it encounters them it will offer a number of options to help resolve the error.
The next five are the main auditing tools. Two are for tracing formula precedents and two for tracing dependants and the fifth is clear all arrows. The remainder of the buttons I will discuss another time.

The trace arrows are very useful for seeing all the precendents or dependants of a cell. Basically precedents are all cells that add to a cell somehow and dependents are all cells that use a cell in their formulas some how. To use it click on a cell that has a formula relying on other cells and then click the Trace Precedents button. Blue arrows will emanate from the cell leading to the cells or ranges that it uses in its calculation. Each successive click will take you back another generation from all the cells that were located each time. Clicking the Remove Precedent Arrows button removes one generation of arrows for each click.
Beware - this can be very slow on old computers if there are a lot of numbers because of the high graphics requirements to draw all the arrows.
Trace Dependants and Remove Trace Dependents work exactly the same way and locate all cells dependent on the cell selected. The Remove All Arrows button does just that.

The picture below shows an example of this in action.


The blue lines generated are more than just pretty lines. They are also clickable. Double clicking them will send you along the line to the next cell. This is useful for cells that along way apart (off the screen). If your formula references another sheet then an arrow will head off left and up to a little black and white box representing an off sheet link. Double click the arrow leading to this box and you will open a box containing all the other sheet links that this arrow represented. Double clicking on any of these links will take you to that cell or range.


Posted by: Jethro | 2:00 AM |