Excel Formula auditing tools

• tracing tools to see the cell dependencies and cell dependents,
• checking error values.

Note: In Excel, formulas are evaluated in the natural order. In other words, if the formula in cell C2 depends on the result of the formula in cell A2, Excel evaluates cell A2 before calculating cell C2.

A simple way to evaluate a part of a formula

You can use a simpler way to evaluate a part of a formula in Excel:

   1.   Select the cell that contains the formula.

   2.   Double-click the cell (or activate a cell and press F2) to get into Edit mode (see more about modifying cell data).

   3.   Select the part of the formula you want to evaluate:

   4.   Press F9.

Excel displays the calculated result of the selected part of the formula:

   5.   You can evaluate other parts of the formula:

or press Esc to cancel and return your formula to its previous state.

Run the Formula Evaluator

The Evaluate Formula dialog box shows a formula being calculated one step at a time.

To run the Formula Evaluator, select the cell that contains the formula, and on the Formulas tab, in the Formula Auditing group, click Evaluate Formula:

In the Evaluate Formula dialog box:

• Click the Evaluate button repeatedly to see the intermediate results as the formula is calculated. The result of the evaluation is shown in italics:
  
• If the underlined part of the formula is a reference to another formula, click the Step In button to display the other formula in the Evaluation box:
  
• Click the Step Out button to go back to the previous cell and formula:
  
• Click the Restart button to return the evaluation:
  

Tracing cell relationships

Cell dependencies (in Microsoft Excel, cell precedents)

A formula cell dependencies are all the cells that contribute to the formula result. A direct dependency (in Microsoft Excel, direct precedents) is a cell you use directly in the formula. An indirect dependency (in Microsoft Excel, indirect precedents) is a cell that isn't used directly in the formula but is used by a cell you refer to.

For example:

To see the cell dependencies, activate the cell, then on the Formulas tab, in the Formula Auditing group, click the Trace Precedents button:

Thus, to see all dependencies, you need to click the Trace Precedents button as many times as many precedent levels are used in the spreadsheet.

Notes:

1. If the sheet has a dependency on another sheet or workbook, you will see the arrow like this:
   
2. See also how to select special types of cells.

Cell dependents

Cell dependents include all formula cells that use the cell. The formula cell can be a direct dependent or an indirect dependent.

The example is the same as for the dependencies.

To see the cell dependents, activate the cell, then on the Formulas tab, in the Formula Auditing group, click the Trace Dependents button:

Thus, to see all dependents, you need to click the Trace Dependents button as many times as many dependent levels are used in the spreadsheet.

Note: If the sheet has dependents in another sheet or even workbook, you will see the arrow like this:

Remove arrows

To remove arrows, on the Formulas tab, in the Formula Auditing group:

• Click the Remove Arrows button to remove all arrows for precedents and dependents,
• Click the Remove Arrows button, then select Remove Precedent Arrows to remove only arrows for precedents,
• Click the Remove Arrows button, then select Remove Dependent Arrows to remove only arrows for dependents:

See how to select special types of cells for more details.

See also this tip in French: Évaluer la formule dans Excel.