Three ways to understand part of an Excel formula

Office-Watch.comLast updated: 12 December 2023

Featured,Microsoft 365,Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Office 2007,Office 2010,Office 2013,Office 2016,Office 2019,Office 2021 / Office LTSC,Office 365

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.

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. 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