Excel Tip of the Week: Evaluate Formulas

What a mess. I actually ended up finding a simpler solution, so I was able to do away with the above formula. However, before I was able to arrive at a better solution I was stuck working with a very complex formula. This is where Evaluate Formula really came in handy.

Even if you don’t think you’ll ever write something super complex in Excel, it is likely you will have to work in a spreadsheet with someone else’s tricky formulas. When that happens you’ll be glad you have Evaluate Formula in your Excel toolkit.

What the Evaluate Formula feature does is allow you to evaluate each part of a formula individually and see how Excel is calculating a specific piece of the formula. As usual, the best way to learn this is via an example: (Note to Mac users, this feature is not available in your version of Excel.)

1. Download the example spreadsheet here
2. Navigate to cell C7
3. From the “Formulas” menu in the “Formula Auditing” section click on “Evaluate Formula” (Keyboard shortcut: Alt → M → V )
4. You will see the Evaluate Formula dialogue box pop up (shown below)
5. The box shows C5  - C6. Notice the underlined portion. This is what Excel recognized as the first thing it needs to evaluate. (In more complex formulas the first thing underlined is not always the first term.)
6. Click “Evaluate” and Excel will calculate what is underlined. In this case C5 changes to 529.
7. Continue clicking “Evaluate” until the formula is calculated.

The other option in the dialogue box is called “Step In.” If you click this button Excel will jump to the cell referenced in the current underlined portion. Further, within the dialogue box it will show both the cell reference and its value below it. You can then “Step Out” and continue evaluating.

Don’t forget to try out the advanced example in the example spreadsheet. The formula to evaluate has some stuff I haven’t covered before on this blog, but you may still be able to figure it out.