Skip to content

Three ways to understand part of an Excel formula

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.

Evaluate Formula

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.

New – Visual Preview – 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:

Source: Microsoft plus our cropping

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. And now Excel on the web.

Understand or fix an Excel sheet with Evaluate Formula
LET() assigns names to calculations in Excel
Excel Formula Bot upgrades and goes pro
Excel’s order of calculation is not BODMAS

About this author

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.