Excel has three different ways to see a partial formula result to help you locate and fix a problem or understand how a formula works. Two methods have been in Excel for some time and there’s a new tooltip feature coming soon.
The full Evaluate Formula has been around since at least Excel 2007. It lets you see the result of each part of a formula instead of just the cell result.
It also lets you ‘drill down’ or as Microsoft says ‘step in’ to other linked cells and see their calculations.
Convert formula to number with F9
Select part of a formula the press F9 to convert into a value.
For example, here’s a cell formula selected.
Press F9 and the selection is replaced with the current value instead.
F9 ‘convert to value’ also works for a selected part of a longer formula.
I often use this trick for simple calculations like the one shown. Instead of switching to a calculator (or using my head) enter the calc into the Excel formula bar then press F9 to drop in the number directly.
If you just want a ‘peek’ into an interim result, use F9 then Undo (Ctrl + Z) to go back to the formula.
But there’s a better way coming to Excel 365 ….
New – part formula results in a tooltip
A new Excel Windows/Mac feature is showing the partial solution as a tooltip (you can see it in the above examples for pressing F9. A nice idea because it saves hopping into the full Evaluate Formula box or using the F9 trick to see the value then Undo (Ctrl + Z) to reverse the change and get the formula back.
Select part of the formula then hover your mouse pointer over the selection.
It even works for a single cell to confirm its current value.
Select functions, parameters or references within a formula.
Here is Microsoft’s example:
Beware the selection trap
Be careful, the tooltip shows the results for whatever is selected, even if that’s just part of a number or cell reference. Check out these two part selection 22.“5/4” and B“2+10” both show a nonsensical result that’s out of context.
Formula bar tooltips are currently in Excel 365 for Windows and Mac, Insiders beta releases and will reach public releases later in 2023.