Excel for Mac: Value Preview

This week in our series about Microsoft Excel for Mac, we show how to work around the missing feature, Evaluate Formula, by using a new feature and some old-school techniques.

This is a follow-up to our previous blog about writing and editing formulae, and how those tasks are a little different on Mac than on Windows.  Some formulae are very simple.  Debugging them is a simple matter of looking at the formula and knowing whether it’s right or wrong.  For example, you may have a formula calculating the SUM of a few values. You can often just look at the values, calculate it in your head, and know whether you’ve got it right or wrong.

On the other hand, you may have much more complex formulae, where checking is not so easy to do in your head.  In that case, there are a few tricks and tools you can use.  However, there is one less tool on Mac than there is on Windows, because the ‘Evaluate Formula’ dialog isn’t available on a Mac (as of late 2023). 

Value Preview

In early 2023, Microsoft added a handy feature called ‘Value Preview ToolTips’ to Excel on Windows, Mac and the web.  These ToolTips are a great way for you to check the values being calculated by your formula and the parts making up the formula.  It’s also very easy to use.  Simply highlight part of your formula, and a ToolTip will appear showing you the current value of whatever you’ve selected.  It works with references, functions, parameters within a function and the entire formula.  In some cases, you might select something that doesn’t calculate and then Excel won’t show a value.

To make sure you select parts of the formula that can be calculated, you can use the function ToolTips to your advantage. As shown in the image below, you can click the function arguments to highlight the related part of the formula. In the IF formula, clicking on logical_test highlights that part of the formula     

There’s a shortcut key you can press to disable or enable the Value Preview ToolTips.  Just press CTRL + OPT + P.  On a Mac, this only works when you are not editing a cell (on Windows, it may be turned on / off anytime with CTRL + ALT + P).

F9 and ESC: The Old School Way

A handy trick for debugging formulae that is much less necessary now that we have the Value Preview ToolTips is to press F9 when you’ve selected part of the formula that you want to evaluate.  Excel will calculate the selection and replace what you selected with the current value.  Just remember to press ESC after doing this, otherwise you might accidentally save a hard-coded value where you didn’t mean to.

As you can see in the image below, we highlight part of the formula, then press F9 which changes the cell reference G21 to its value (11).  Then, we press ESC to avoid saving it as a hard-coded value.

Formula Builder

As we described in the previous post, you can use the ‘Formula Builder’ to help debug.  When you’ve entered enough information for Excel to start calculating, it will show you the current values above each field in the pane.

Please come back for future posts in this series, as we cover much more about Excel for Mac.