How is a Formula Being Calculated?
Strategy: Select the cell with the formula. From the menu, select Tools – Formula Auditing – Evaluate Formula, as shown in Fig. 484. (Click any image for a larger view.)
Note: In Excel 2007, the Evaluate Formula tool is found in the Formula Auditing grouping on the Formulas tab.
The Evaluate Formula dialog shows the formula. The first item to be calculated is underlined, as shown in Fig. 485. Click Evaluate to calculate the underlined portion of the formula.
With each click of Evaluate, Excel will calculate the underlined portion and show the results in italics. It will underline the next step in the calculation. Fig. 486 is after the second Evaluate. Excel just revealed that C4 is 1200 (in italics). It is about to calculate the first division in parentheses.
Additional Information: Any time that the next term to be calculated is a cell reference, you can choose the Step In button to evaluate the formula in that cell. In Fig. 487, choosing Step In will evaluate D14.
As shown in Fig. 488, it is possible to Step In several levels. After seeing the formula for D14, you can choose to Step In to see the formula for D15.
Use Step Out to close the most recent detail level and go back one level, as shown in Fig. 489.
Summary: This feature is a great tool. It basically gives you a great appreciation of just how much work Excel does every time you enter a formula, because it allows you to watch the calculation happen in slow motion.
Commands Discussed: Tools – Formula Auditing – Evaluate Formula