Contents show
There is very little difference between Excel for Mac and Excel for Windows for most users. A workbook created in Excel for Windows will usually work fine when opened in Excel for Mac and vice-versa.
However, there are differences, and the more advanced Excel for Mac users may encounter issues as discussed below.
Very few large businesses use Macs. Most Mac business users are relatively small enterprises, with Macs very common in the creative sector. Similarly, the number of MacOS installations is much smaller than for Windows for home users.
Understandably, Microsoft will always develop new Excel features for the Windows platform first. And then, sometimes but not always, add the new features to Excel for Mac. But there is often a lag of many months or years.
Power users (especially those writing complex macros) should stick with Excel for Windows.
For professional Excel development, it is hard to recommend Excel for Mac. There is just too much missing functionality and/or niggling problems to deal with. However, if you’re not a power user and only need to write the occasional macro, there’s no reason not to use Excel for Mac.
So, if you’re an advanced user, are there any reasons why you’d want to use Excel for Mac rather than Excel for Windows?
Perhaps if you spend a lot of time working with different colour schemes, colour selection is similar to Excel for Windows but with additional features such as CMYK, sliders, colour wheels, sliders, palettes and pencils. And an eyedropper for colour matching.
NEED HELP WITH AN EXCEL PROJECT?
The Excel Experts can help you or your business complete an unfinished project, integrate web applications, automate tasks, and much more.
Issues with Excel for Mac
Data Connectivity
Excel for Mac has a limited range of data connections (for connecting to external data) compared to Excel for Windows.
With Excel for Mac, you are essentially limited to ODBC SQL, HTML, Text and Database (Microsoft Query).
However, with Excel for Windows, you can connect to the web, tables/ranges, Access, ODBC SQL, PDF, JSON, XML and many others.
Quick Access Toolbar
Unlike Excel for Windows, the Quick Access Toolbar (QAT) in Excel for Mac can’t be imported or exported.
In addition, installing a new Excel version will delete any customisations applied to the QAT.
Form Controls
Worksheet form controls look pretty ugly on a Mac (even worse than in Excel for Windows).
Power Pivot
Power Pivot is a feature included with Excel for Windows that, according to Microsoft, enables the user to:
“…import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts.”
Unfortunately, this feature is not available in Excel for Mac.
Visual Basic for Applications (VBA)
VBA is Microsoft’s programming language for Excel.
The version included with Excel for Mac is similar to that in Excel for Windows but has several significant drawbacks.
These drawbacks make professional VBA development with Excel for Mac much more difficult.
See the following section for more detail.
Visual Basic for Applications (VBA)
VBA Help and Support
When Googling for help with VBA, the overwhelming majority of search results is for Excel for Windows.
There is very little content for Excel VBA for Mac. Ron de Bruin’s Mac website (https://www.macexcel.com/) is an honourable exception.
Learn Excel VBA!
Visual Basic Editor
The Visual Basic Editor (VBE) functionality in Excel for Mac is very poor compared to Excel for Windows.
In particular, there is no support for user forms (see below). It’s also not possible to import/export components (user forms and modules).
User Forms
With Excel for Mac, you can’t build user forms or edit user form code – the VBE just doesn’t have that functionality.
The only way is to build user forms in Excel for Windows and check that they look OK on a Mac.
You could build user forms with VBA code, but it’s an incredibly tedious process.
User forms created in Excel for Windows are smaller when viewed in Excel for Mac.
But Jon Peltier has some nifty re-sizing code (https://peltiertech.com/userforms-for-mac-and-windows/).
From Jon’s website:
“There is a difference in screen resolution between the two platforms. On the Mac, it’s 96 dpi, where each dot represents a pixel. In Windows, screen resolution is 72 dpi (dots per inch) and each dot represents a ‘point’, while pixels are still 96 per inch. Confusing the issue is that VBA in Windows uses points as the measurement unit for designing UserForms, while VBA on the Mac uses pixels. The result is that without applying any correction, UserForms that come out just right in Windows are only 75% as large on the Mac, making them difficult to read.”
However, even after using the re-sizing code, you’ll need to carefully check the resulting forms on a Mac carefully in case any text, for instance, doesn’t now fit in a text box.
And the column widths in multi-column list boxes don’t re-size, although this can be fixed with code.
Editor: Ron de Bruin’s website has some handy code that uses conditional compiler constants to test whether the operating system is Windows or MacOS – this is especially useful if you need to develop an application that is going to be used by both Mac and Windows users.
Mac user forms using frames containing list boxes don’t always work properly.
I’ve had problems with frames changing to a white background and losing the frame surround. It was an issue that I wasn’t able to reproduce consistently.
User forms created in Windows may look different on a Mac, e.g. a coloured user form button will appear grey on a Mac (an alternative is to use a text box with a single line border, combined with an ‘xxx_Enter()’ event, where ‘xxx’ is the text box name, triggered when the user clicks the text box).
Shapes
There are occasional problems with shape sizes being changed in Excel for Mac.
e.g. I had a worksheet with a group of equally spaced, same size buttons with text created in Excel for Windows.
When the workbook was opened in Excel for Mac, some buttons had different sizes and spacing.
I couldn’t track down the root cause but suspect it may be something to do with the different screen resolutions and/or text sizes for Windows/Mac machines.
Best practice is to (1) avoid wrapping text and (2) gradually increase the size of the shapes in Windows until the Mac sizes are all consistent.
Due to screen resolution differences, text for user forms and shapes in Windows that is carefully aligned with tabs may not align correctly on a Mac.
Event Programming
Event programming with Excel for Mac can be flaky.
I had an example where a Worksheet_Change() event did not fire on a Mac (it worked fine on Windows).
However, when Application.EnableEvents=True was added to the Workbook_Activate() and Workbook_Open() events, the Worksheet_Change() event fired OK.
This is strange because if events were disabled, the Workbook_Activate() and Workbook_Open() events shouldn’t have fired either!
Microsoft Excel 101 – Excel Basics Beginner Course
Files and Folders
Interacting with files and folders using VBA on a Mac is much more difficult.
The following notes are taken from Ron de Bruin’s website:
“In Windows we can use GetOpenFilename to select files and do what we want with the path results. You can use filefilter to only display the files you want and use MultiSelect to select more than one file. It is also possible with ChDrive and ChDir to set the folder that is selected when GetOpenFilename opens. But on a Mac, filefilter does not work and it is not possible to select more than one file. Also ChDir does not work as in Windows to set the folder that will open with GetOpenFilename.“
There are similar issues with other functions such as GetSaveAsFileName and SaveAs.
Miscellaneous Issues
ActiveX controls are not allowed in Excel for Mac (they are only compatible with the Windows OS).
Any code that requires a Windows API will not work on Excel for Mac.
Extended ASCII characters in VBA are usually different in the MacOS, e.g. Chr(149) for a bullet point in Windows, which results in an ‘ï’ (letter ‘i’ with an umlaut) in a Mac.
For a Mac, you can use Chr(165) for a bullet point. This can be an issue when designing message boxes.
Some macro keyboard shortcuts seem to be reserved, e.g. Ctrl+h does not seem to work as a macro shortcut on a Mac, although it works fine in Windows.
VBA animation is only possible with Excel for Windows as functions Sleep() and Wait() are not available on a Mac.
In Excel for Windows, the right-click context menus can be edited with VBA. This is not possible with Excel for Mac.
Excel for Mac 2008 can’t run macros at all!
The VBA functionality was completely removed.
After a considerable outcry, it was reinstated in the next version (2011).
All the macro code will disappear if a spreadsheet is opened in the 2008 version and then saved.
An illustrative guide to Excel from Jamie Keet.
Developing for Excel for Mac
If you need to develop a professional application for Excel for Mac, you’ll probably want to develop Excel for Windows first. Then test it thoroughly on a Mac and fix any issues that arise.
If you’ve only got a Mac PC or laptop, you could install Excel for Windows using Boot Camp or Parallels.
However, if you’re developing for clients where the workbook will be used by both Mac and Windows users, you’re still going to need Excel for Mac for testing purposes.
Useful tip: don’t use vbCrLf in your code.
This results in one carriage return in Windows but two with a Mac. Better to use Chr(10), which gives one carriage return in Windows and Mac.
NEED HELP WITH AN EXCEL PROJECT?
The Excel Experts can help you or your business complete an unfinished project, integrate web applications, automate tasks, and much more.
