Tracing Formula Errors

Excel offers some very effective tools on its Formula Auditing toolbar that you can use to track down the cell that's causing your error woes by tracing the relationships between the formulas in the cells of your worksheet.

By tracing the relationships, you can test formulas to see which cells, called direct precedents in spreadsheet, directly feed the formulas and which cells, called dependents (nondeductible, of course), depend upon the results of the formulas. Excel even offers a way to visually backtrack the potential sources of an error value in the formula of a particular cell. To display the precedents and dependents of a formula as well as for tracing the source of errors, you can use the command buttons in the Formula Auditing group of the Ribbon's Formulas tab.

When you click the Trace Precedents and Trace Dependents buttons on the Formulas tab, Excel shows 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. The program 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 extending from each of 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.