Excel Formula auditing tools
- the simple way to evaluate just a part of a formula,
- the Formula Evaluator allows you to see how the various parts of a nested formula are evaluated and the order in which the formula is evaluated:
![Evaluate Formula in Excel for Microsoft 365 Evaluate Formula in Excel 365](/images/tips/126_365/1.png)
- 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:
- Using the mouse:
Highlight the part of the formula:
- Using the keyboard:
Press Shift and use the navigation keys.
4. Press F9.
Excel displays the calculated result of the selected part of the formula:
![Evaluated part of the formula in Excel for Microsoft 365 Evaluated part of the formula in Excel 365](/images/tips/126_365/15.png)
5. You can evaluate other parts of the formula:
![Evaluated parts of the formula in Excel for Microsoft 365 Evaluated parts of the formula in Excel 365](/images/tips/126_365/16.png)
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:
![Evaluate Formula button in Excel for Microsoft 365 Evaluate Formula button in Excel 365](/images/tips/126_365/2.png)
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:
![Tracing cell relationships example in Excel for Microsoft 365 Tracing cell relationships example in Excel 365](/images/tips/126_365/7.png)
- the cells C1:C3 contain the sum of the columns A and B,
- the cell C4 has a value of 7,
- the cell C5 contains the formula = SUM (C1:C4).
To see the cell dependencies, activate the cell, then on the Formulas tab, in the Formula Auditing group, click the Trace Precedents button:
![Trace Precedents button in Excel for Microsoft 365 Trace Precedents button in Excel 365](/images/tips/126_365/trace_precedents.png)
Excel displays the direct dependencies for the cell C5:
![Cell direct dependency in Excel for Microsoft 365 Cell direct dependency in Excel 365](/images/tips/126_365/8.png)
To see the next level of the dependencies, click the Trace Precedents button again:
![Cell indirect dependencies in Excel for Microsoft 365 Cell indirect dependencies in Excel 365](/images/tips/126_365/9.png)
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:
- If the sheet has a dependency on another sheet or workbook, you will see the arrow like this:
- 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:
![Trace Dependents button in Excel for Microsoft 365 Trace Dependents button in Excel 365](/images/tips/126_365/trace_dependents.png)
Excel displays the direct dependents for the cell A1:
![Cell direct dependent in Excel for Microsoft 365 Cell direct dependent in Excel 365](/images/tips/126_365/11.png)
To see the next level of the dependents, click the Trace Dependents button again:
![Cell indirect dependents in Excel for Microsoft 365 Cell indirect dependents in Excel 365](/images/tips/126_365/12.png)
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:
![Cell indirect dependents in another sheet Excel for Microsoft 365 Cell dependents in another sheet Excel 365](/images/tips/126_365/13.png)
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:
![Remove Arrows buttons in Excel for Microsoft 365 Remove Arrows buttons in Excel 365](/images/tips/126_365/remove_arrows.png)
See how to select special types of cells for more details.
See also this tip in French: Évaluer la formule dans Excel.