Excel 2013 offers some very effective formula-auditing tools for tracking down a 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 jargon, directly feed the formulas and which cells, called dependents (nondeductible, of course), depend on 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.
The formula-auditing tools are found in the command buttons located in the Formula Auditing group on the Formulas tab of the Ribbon. These command buttons include the following:
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.
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 Arrows: Clicking this button removes all the arrows drawn, no matter what button or pull-down command you used to put them there.
Click the drop-down button attached to this button to display a drop-down menu with three options: Remove Arrows to remove all arrows (just like clicking the Remove Arrows command button); Remove Precedent Arrows to get rid of the arrows that were drawn when you clicked the Trace Precedents button; and Remove Dependent Arrows to get rid of the arrows that were drawn when you clicked the Trace Dependents button.
Show Formulas: To display all formulas in their cells in the worksheet instead of their calculated values — just like pressing Ctrl+` (tilde).
Error Checking: When you click this button or click the Error Checking option on its drop-down menu, Excel displays the Error Checking dialog box, which describes the nature of the error in the current cell, gives you help on it, and enables you to trace its precedents.
Choose the Trace Error option from this button’s drop-down menu to attempt to locate the cell that contains the original formula that has an error.
Choose the Circular References option from this button’s drop-down menu to display a continuation menu with a list of all the cell addresses containing circular references in the active worksheet — click a cell address on this menu to select the cell with a circular reference formula in the worksheet.
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. The Evaluate Formula feature can be quite useful in formulas that nest many functions within them.
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.
Clicking the Trace Precedents and Trace Dependents buttons in the Formula Auditing group of the Formulas tab on the Ribbon 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 on 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 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.