Excel for Mac: Writing and Editing Formulae

This week in our series about Microsoft Excel for Mac, show a few differences between the experience of writing formulae on a Mac versus a PC.

Thankfully, your formulae will give you precisely the same result on a Mac as they do on Windows.  You don’t need to worry about this.  However, there are some minor differences in how Excel helps you to write and edit your formulae.

[It should be noted that there are a few special functions that only work on Windows.]

Function ToolTips

When writing a formula, you can begin typing the name of the function you’d like to use, and Excel will show you a list of functions that match.  You can use your arrow keys to select a function from the list, and if you’re using Windows, you’ll see a helpful ToolTip to show you a short description of the function.  Unfortunately, the ToolTip won’t appear on a Mac.

As you can see in the examples below, a ToolTip helps you understand the difference between the COUNT and COUNTA functions.  To determine this on a Mac, you can use the Formula Builder, which we’ll show next. 

Formula Builder

Excel for Mac has a ‘Formula Builder’ pane to help you create formulae.  This is different than Windows, which has a modal dialog for the same purpose.

It’s a two-step process for a new formula or a single step if you’re editing a formula.  To open it, just click the Fx button next to the Formula bar or press SHIFT + F3.  At first, it shows the function list.  You can start typing in the search field and then select a function from the list to see the function’s description and syntax, including the descriptions for each parameter.

Once you’ve found the function you want, press the ‘Insert Function’ button.  The function signature will appear in the pane, showing you the parameters with text fields where you can enter the appropriate references, functions, and / or values.  When you’re finished, press the Done button and it will insert the formula into the active cell. 

You can use the ‘Formula Builder’ to write and edit, but also to help debug your formulae.  When you’ve entered enough information for Excel to start calculating, it will show you the current values above each field in the pane.   Notice in the example shown above, the ‘Logical_test’ parameter has a value of TRUE.  This is the current calculation for “A1=“Month””.  It also shows the result of the entire formula, if it can be calculated. Again, in the example above, you can see “Result: 12” near the “Done” button.  This is the current value being calculated by the formula that’s being edited. 

Positioning the Formula Builder

It doesn’t have to be a pane – or a pain.  The ‘Formula Builder’ on a Mac will open as a side pane, but you can tear it away from the side and use it as a dialog if you like.  This gives you the flexibility to position it anywhere you like.  Move it back to the side if you want to dock it later.

[It should be noted that the ENCODEURL(), FILTERXML() and WEBSERVICE() functions rely on operating system capability on Windows, so they won’t work on a Mac.  You’ll see an #NAME? error and the Formula bar will show the function names with a ‘_xlfn.’ prefix.     

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