We’ve all written a formula in Excel and gotten an unexpected result.  Maybe it was an error message or maybe it just wasn’t the number you were expecting.  Excel’s Evaluate Formula tool will walk you through calculating the formula, showing the result of each step, so you can easily find and correct problems.

To use it, click on a cell with a formula in it. Go to the Formulas tab on the ribbon. Then click on the Evaluate Formula button in the Formula Auditing section. A window will open displaying your formula.  Part of the formula will be underlined. That underlining shows which part of the formula is being evaluated in this step.  Click the Evaluate button at the bottom of the window and the underlined portion will change to the result for that step.  In other words, if the underlined portion refers to cell A3, it would change from showing “A3” to showing whatever is in A3.  Or if the underlined portion was “100 / 5” it would change to “20”.  Each time you click the Evaluate button it will advance one more step through your formula. Since you can see the result of each step along the way, it is much easier to locate and correct mistakes in your formula.

If the step being evaluated points to a cell with another formula in it, you can click the Step In button to evaluate that formula, and then Step Out to return to the original formula.



This is an update of a post originally published in January 2011