Value Preview ToolTips in Excel

Microsoft has just added ToolTips to help you check your formulae directly in the Formula bar or in the cell you’re editing.  All you need to do is select part of the formula, and Excel will display a ToolTip that contains the current value of the part you highlighted.  They’re called Value Preview ToolTips, a catchy title if ever there wasn’t one.  But don’t let that put you off!

Up until now, a common technique modellers employed to obtain the current value of (part of) a formula was to select the formula (or part thereof) and press F9.  Doing so would replace (the selected part of) the formula with its current value.  You’d probably only do this to check a value, and then you press ESC to avoid keeping it as a hard-coded value in your formula. 

Alternatively, another approach was to use the ‘Evaluate Formula’ dialog (ALT + M + V or Formulas -> Evaluate Formula), which let you check the current value of (each part of) your formula.  ToolTips are intended to be easier to use and less burdensome should you just want to focus on one part of the formula.

To get this new feature to work, in any formula, simply select the part you wish to evaluate.  For example, in a ToolTip that shows the function syntax, click any of the function parameters to select it, and then take note of the ToolTip that appears which shows the current value of that parameter.  

It should be noted that:

• You may select references, functions, parameters within a function or even the entire formula
• if you’d like to turn off the ToolTips, you can press CTRL + ALT + P toggle the feature on or off, but note:
• on Excel for Mac, this only works when you are not editing a cell
• on Excel for Windows, it may be toggled on / off anytime (if a ToolTip is visible, pressing the shortcut will not immediately hide the ToolTip, but it will switch the feature off so that subsequent selections won’t show the ToolTip).

Thus, in any formula that contains range or table references, you may select a reference and see that the ToolTip shows the value or values in the referenced cells.  Further, while editing a formula that has one or more functions, you may also place your cursor inside the brackets of the function so that the syntax ToolTip appears.  Then, click one of the parameter names in the syntax to select that part of your formula.

If you select a part of the formula that can’t be evaluated, you won’t see a ToolTip.  For example, if you select only part of a reference or part of a parameter, no ToolTip will be displayed.

It’s possible to see a tooltip for part of the formula that doesn’t get calculated as part of the cell value.  For example, if your formula is =101+201, you could select 1+20 and see a tooltip that shows 21, even though that part isn’t relevant when calculating the entire formula.

Dates presently show as serial numbers in Excel, rather than how they would be displaced under current locale settings.  This may change in the future – watch this space!

Finally, you can move the ToolTip if it’s covering something you need to see, or if you just want to locate it somewhere else.

This feature is presently rolling out to Beta Channel users running:

• Windows: Version 2302 (Build 16116.20000) or later
• Mac: Version 16. 70 (Build 230116) or later.