Excel has a simple way to understand or fix a complex formula. 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.
- Formulas with circular references may not evaluate as expected. If circular references are desired, you can enable iterative calculation.
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.
About Excel Live worksheets in Microsoft Teams
Excel Paste Special with math operations
Convert Excel formula into fixed values or text
Hiding Cell Contents or Formulas in Excel
Excel Array formulas for everyone
