Understand or fix an Excel sheet with Evaluate Formula

Excel has a simple way to understand or fix a complex formula. Evaluate Formula breaks down a formula into parts that make up the result in the cell.

Evaluate Formula lets you calculate parts of a formula and see interim results that make up the final value that appears in the cell.

It’s on the Formulas tab under Formula Auditing.

Evaluate Formula isn’t new, just little mentioned despite its usefulness.  We found it in Excel 2007, in the same place on the ribbon.

How Evaluate Formula works

Evaluate Formula lets you see the interim results for each part of a formula, step-by-step.

Choose a formula then Formulas | Formula Auditing | Evaluate Formula.

The underlined part of the formula is what Excel will calculate when the Evaluate button is clicked.

Now the Average() function is converted to its result (2.5).

Anything that’s been calculated/evaluated is shown in italics (hard to see).

Next it’ll figure out of 2.5 is greater than 50 (underlined above)

Of course, that’s False so all that’s left is to show one of the IF results, obviously 999

Let’s run that formula again but with different values. Now the Average() is greater than 50

Now Evaluate Formula jumps to the True option and offers to work out the SUM()

Now you can see why the cell result is 10 – the SUM of the cells.

Step In / Step Out

If the formula links to another cell formula, Evaluate will let you ‘Step in’ to that second formula.

Here’s a simple example. The H6 refers to another cell with a formula, choose ‘Step In’ to dig deeper.

Now we can see the formula of the called cell (H6), which happens to be the formula we used above.

Now we can evaluate the second formula, to see how it works.

Step Out – takes you back to the original cell formula.

Step In can dig many levels into a series of formulas. Notice the scroll bar at right, ready to accommodate many more formulas.

But there are some limitations:

• The reference has to be in the same workbook.
• If the same reference appears more than once in a formula, you can only ‘Step in’ to the first reference.

Evaluated parts could be highlighted so customers don’t have to squint to see the italics.

Watch out for

There are some situations where Evaluate Formula either doesn’t work or might give unexpected results.

According to Microsoft, look out for

• Some parts of formulas that use the IF and CHOOSE functions are not evaluated, and #N/A is displayed in the Evaluation box.
• If a reference is blank, a zero value (0) is displayed in the Evaluation box.

In addition, there are functions that can give different results each time and that will confuse Evaluate Formula.

• The following functions are recalculated each time the worksheet changes, and can cause the Evaluate Formula tool to give results different from what appears in the cell: RAND, OFFSET, CELL, INDIRECT, NOW, TODAY, RANDBETWEEN, INFO, and SUMIF (in some scenarios).

We’d add to that list, use of Stock or Currency data types which can be refreshed automatically.

And finally, circular references are always ‘fun’ in Excel.

Evaluate Formula could be better

This feature hasn’t received any attention from Microsoft for some years and could do with some love.

The underlining and especially italics are quite hard to read. At the very least, we should have the same color-coded formulas that are on the formula bar.

Even better, make the Evaluate steps all appear on screen, instead of disappearing at each evaluation. That would make it a lot easier to understand the formula.

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address