Excel 2010 offers some effective formula-auditing tools for tracking 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 jargon, directly feed the formulas, and which cells, called dependents, 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 Formula Auditing group on the Formulas tab of the Ribbon. These tools 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 (or the Remove Arrows option on its drop-down menu) removes all the arrows drawn, no matter what button or command you used to put them there.
Show Formulas: To display all formulas in their cells in the worksheet instead of their calculated values (just like pressing Ctrl+`).
Error Checking: When you click this button or 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. Click the Trace Error option on this button's drop-down menu to attempt to locate the cell that contains the original formula that has an error. Click the Circular References option on this button's drop-down menu to display a menu with a list of all the cell addresses containing circular references in the active 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 pane, 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 pane, and then click Add in the Add Watch dialog box that appears.