Formula Auditing 101

The easiest method for tracing the relationship among cells is offered by the tools on the Formula Auditing toolbar. To display the Formula Auditing toolbar, choose Tools → Formula Auditing → Show Formula Auditing Toolbar or choose Formula Auditing on the shortcut menu of one of the displayed toolbars. When you first display the Formula Auditing toolbar, Excel automatically makes it a floating toolbar, which you can dock as you see fit.

This versatile toolbar contains the following tools (from left to right) that you can put to good use in your never-ending struggle for truth, justice, and perfection in your Excel workbooks:

• Trace Precedents: When you click this button, Excel draws arrows to the cells (the so-called direct precedents) that are referred to in the formula inside the selected cell. When you click this button again, Excel adds tracer arrows that show the cells (the so-called indirect precedents) that are referred to in the formulas in the direct precedents.
• Remove Precedent Arrows: Clicking this button gets rid of the arrows that were drawn when you clicked the Trace Precedents button.
• Trace Dependents: When you click this button, Excel draws arrows from the selected cell to the cells (the so-called direct dependents) that use, or depend on, the results of the formula in the selected cell. When you click this button again, Excel adds tracer arrows identifying the cells (the so-called indirect dependents) that refer to formulas found in the direct dependents.
• Remove Dependent Arrows: Clicking this button gets rid of the arrows that were drawn when you clicked the Trace Dependents button.
• Remove All Arrows: Click this button to remove all the arrows drawn, no matter what button or pull-down command you used to put them there.
• Trace Error: When you click this button, Excel attempts to locate the cell that contains the original formula that has an error. If Excel can find this cell, it selects it and then draws arrows to the cells feeding it (the direct precedents) and the cells infected with its error value (the direct dependents). Note that you can use this button only on a cell that contains an error value.
• New Comment: Clicking this button opens a comment box attached to the current cell where you can add a text note.
• Circle Invalid Data: Clicking this button draws red circles around all the data entries in the worksheet that don't currently contain valid data.
• Clear Validation Circles: Clicking this button removes all circles drawn by clicking the Circle Invalid Data button. (To remove individual circles, select the cell and then enter the data that's required by the data validation assigned to the cell.)
• Show Watch Window: Clicking this button opens the Watch Window dialog box, which displays the workbook, sheet, cell location, range name, current value, and formula in any cells that you add to the watch list. To add a cell to the watch list, click the cell in the worksheet, click the Add Watch button in the Watch Window dialog box, and then click Add in the Add Watch dialog box that appears.
• Evaluate Formula: Clicking this button opens the Evaluate Formula dialog box, where you can have Excel evaluate each part of the formula in the current cell. This can be quite useful in formulas that nest many functions within them.

Clicking the Trace Precedents and the Trace Dependents buttons on the Formula Auditing toolbar (or choosing both Trace Precedents and Trace Dependents on the Tools → Formula Auditing cascading menus) lets you see the relationship between a formula and the cells that directly and indirectly feed it, as well as those cells that directly and indirectly depend upon its calculation. Excel establishes this relationship by drawing arrows from the precedent cells to the active cell and from the active cell to its dependent cells.

If these cells are on the same worksheet, Excel draws solid red or blue arrows (on a color monitor) extending from the precedent cells to the active cell and from the active cell to the dependent cells. If the cells are not located locally on the same worksheet (they may be on another sheet in the same workbook or even on a sheet in a different workbook), Excel draws a black dotted arrow. This arrow comes from or goes to an icon picturing a miniature worksheet that sits to one side, with the direction of the arrowheads indicating whether the cells on the other sheet feed the active formula or are fed by it.