I’ve been a Mac / Windows user for some time. Two years ago I made the switch from Windows 7 (and Windows apps) to All Mac apps running OS X (Yosemite and El Capitan). My experience has been excellent with the exception for Office 2016 for Mac. The beta experience was very frustrating do the instability. The RTM version has been a disappointment, especially when you compare it to the Windows version.
Despite the recent culture change at Microsoft, the reality with regard to the Office for Mac team, is that it’s understaffed and half-heartedly supported by Microsoft executives.
My recommendation: if you are an Office Power User, you need to 1. Replace Office with a competing product or 2. Sell your Mac and buy a Windows PC w/ Office365. The 3rd option is virtualization, but that defeats the whole purpose and benefits of using a Mac.
List of reasons to Revaluate if Office for Mac is Right for You:
Default file location setting
The Windows version of Excel 2016 enables you to set a default location for saving your files. This setting is not available in the Mac version.
Autosaved Versions of a workbook
The Windows version of Excel 2016 automatically saves ‘draft’ copies of your workbooks as you work, enabling you to retrieve an older version of a workbook even if you didn’t save your changes. This feature is not available in the Mac version.
Customizing the Quick Access Toolbar
The Mac version of Excel 2016 includes a Quick Access Toolbar, but it cannot be customized. The Windows version enables you to add any Excel command to the Quick Access Toolbar.
Page Break Preview view
The Windows version of Excel 2016 includes the Normal, Page Layout and Page Break Preview views. The Mac version only includes Normal and Page Layout.
The Windows version of Excel 2016 shows a ‘smart tag’ below selected cells that enables you to quickly create totals, charts, tables and sparklines. The Mac version does not include this feature.
The Mini Toolbar
The Windows version of Excel 2016 shows a small toolbar when text is selected, allowing you to easily change font styles, sizes and colors. The Mac version does not include this feature.
The Windows version of Office 2016 includes a multi-item clipboard that allows you to copy and paste several different things at the same time. This feature is not included in the Mac version, but there are OS X add-ins available that provide this functionality.
Redo drop-down menu
Pictures in comments
The Windows version of Excel 2016 enables pictures to be placed within cell comments. This is not possible with the Mac version of Excel.
Default template location setting
The Windows version of Excel 2016 enables you to set a default location for saving templates. This setting is not available in the Mac version.
The Windows version of Excel 2016 offers an ‘add-in store’ that enables you to add new features to Excel, including new chart types and task panes. The add-in store is not available in the Mac version.
The Windows version of Excel 2016 enables you to customize your workbook’s visual style using a Color Set, Font Set and Effects Set, but the Mac version only allows the Color Set and Font Set to be changed.
Custom Color and Font sets
The Windows version of Excel 2016 allows you to fully customize themes by creating custom Color Sets and Font Sets. The Mac version does not allow new Color Sets and Font Sets to be created, so themes can only be created from the preset options.
The Windows version of Excel 2016 contains the Live Preview feature. This is used in many different places throughout the Excel application to show a preview of the effect an option will have when you hover the mouse cursor over it. For example, hovering the mouse cursor over a Theme will preview how the Theme would look if it was applied. The Mac version does not contain this feature.
Fill Effects in cells
The Windows version of Excel 2016 allows you to apply a gradient fill to cells. This is not possible in the Mac version.
View Side by Side
The Windows version of Excel 2016 contains a View Side by Side option that allows you to easily compare two workbooks, even when many workbooks are open. This feature is not available in the Mac version, although workbooks can still be compared by using the Arrange feature.
The View Side by Side feature from Excel 2016 for Windows contains a Synchronous Scrolling option that allows you to scroll through two workbooks at the same time. This feature is not available in the Mac version of Excel 2016.
The Windows version of Excel 2016 contains a new Forecast Sheets feature that is able to automatically detect seasonal changes and forecast future values based upon a confidence factor. This feature is not available in the Mac version of Excel 2016.
Find & Replace Formats
In the Windows version of Excel 2016, the Find & Replace dialog offers the ability to find cells based on their format (for example, the background color) and to replace the format of cells that are found. This option is not available in the Mac version.
Pictures in Headers and Footers
The Windows version of Excel 2016 allows you to add pictures within Headers and Footers. The Mac version does not allow this.
Dark Grey Office theme
The Windows version of Office 2016 includes the Colorful, White and Dark Grey themes. The Mac version includes the Colorful and Classic themes.
Full-sized print preview
The Windows version of Office 2016 allows you to see a large print preview of the workbook, including the ability to zoom in and out. The Mac version displays a small print preview that cannot be zoomed.
Ribbon Display Options
The Windows version of Office 2016 contains a Ribbon Display Options menu that allows the Ribbon to be completely hidden, freeing up more screen space than is possible by simply minimizing the Ribbon. This feature is not available in the Mac version.
The Windows version of Excel 2016 allows you to select a range of cells to be used as data labels within a chart. This is not possible in the Mac version.
In the Windows version of Excel 2016, it is possible to apply a chart filter to quickly select the data that should be displayed in a chart. Chart filters do not exist in the Mac version, but you can still change the data that is displayed within a chart by using the Select Data Source dialog.
The Windows version of Excel 2016 allows you to choose how errors should be displayed when printing a workbook. This setting does not exist in the Mac version, so errors will always be printed exactly as they are shown.
In Excel 2016 for Windows, holding down the <Alt> key displays ‘key tips’ for every item on the Ribbon, enabling every command to be accessed using keyboard shortcuts. Key tips are not available in the Mac version.
XPS document format
Excel 2016 for Windows is able to save workbooks in the XPS document format. XPS is a Microsoft alternative to the PDF format. The Mac version cannot save to XPS format.
Excel 2016 for Windows automatically opens files that were downloaded from the internet in protected view. Protected view disables editing and other features to protect you from viruses. The Mac version does not include protected view.
External data source drivers
The Windows version of Excel 2016 is able to connect to any ODBC data source. The Mac version can only connect to SQL Server data sources by default, but 3rd party drivers are available that enable other data sources to be used.
The Watch Window is a feature of Excel 2016 for Windows that enables you to monitor the values of cells anywhere in the workbook. This can be useful in complex workbooks with many interconnected worksheets.
The Watch Window is not available in the Mac version of Excel 2016.
Excel 2016 for Windows allows the ribbon to be fully customized. This enables you to change the commands shown on the default ribbon tabs and to create entirely new ribbon tabs.
The ribbon cannot be customized in Excel 2016 for Mac.
Commands not in the Ribbon
Excel 2016 for Windows contains many ‘hidden’ features that are not shown on the ribbon. These include the Speak Cells features that enable Excel to audibly read the values from cells, as well as many other special features.
Excel 2016 for Mac does not have any additional features that are not shown on the ribbon or Menu Bar.
Allow Users to Edit Ranges
In the Windows version of Excel 2016, it’s possible to add multiple passwords to a workbook that each have access to different cells. This can be useful if a workbook has several users that should be given different access rights.
Excel 2016 for Mac only allows a single password to be created for each workbook.
Excel 2016 for Windows has the ability to apply a digital signature to a workbook. These are used to prove the identity of the author and certify that a document can be trusted.
ActiveX controls are alternative versions of the form controls. They are rarely used, and are intended to be used with the VBA programming language.
ActiveX controls are not available in the Mac version of Excel 2016.
Trusted files and folders
The Windows version of Excel 2016 allows you to define a list of trusted files and folders. Files that are trusted or saved to a trusted folder can be opened without any warnings, even if they contain macros.
Macro security settings
Excel 2016 for Windows allows you to choose between several settings for macro security. Macros can be completely disabled, enabled after showing a warning message, only enabled for digitally signed workbooks, or always enabled.
Excel 2016 for Mac only allows the warning message to be either switched on or switched off.
Relative references in macros
In the Windows version of Excel 2016, it’s possible to record macros using either absolute or relative references. Absolute references mean that the macro will always affect the same cells every time it runs. Relative references mean that it will affect cells relative to the position of the active cell when the macro runs. Relative macros are very useful when you need a macro to affect different cells each time the user runs it, instead of always affecting the same cells.
It isn’t possible to record a macro with relative references Excel 2016 for Mac, so all macros use absolute references.
Slicer table filtering
A timeline is a special type of slicer that is specifically used to filter data by date and time.
Timelines aren’t available in Excel 2016 for Mac, but you can replicate their functionality using slicers.
OLAP pivot tables and relationships
Standard Excel pivot tables can only summarize data from a single table, while OLAP pivot tables can draw their data from multiple tables. To make this possible, relationships must be created between the data tables, creating what is known as a data model.
OLAP features are only available in Excel 2016 for Windows, so none of these features can be used in the Mac version of Excel.
Automatic date grouping in pivot tables
When a field containing date information is added to a pivot table in Excel 2016 for Windows, it is automatically split into appropriate groups (usually Year, Quarter, Month and Day).
This does not happen in the Mac version of Excel 2016, but the same result can be achieved by using the Group feature.
Automatic insertion of GETPIVOTDATA functions setting
Excel 2016 for Windows enables you to create PivotCharts. These are charts that have the same features as pivot tables, including the ability to filter data within the chart.
PivotCharts cannot be created in Excel 2016 for Mac.
Excel 2016 for Windows has the ability to display data on a 3D map of the world. This is useful for presenting data that is tied to geographical locations.
3D Maps cannot be created in Excel 2016 for Mac.
Get & Transform
Get & Transform (previously known as PowerQuery) is now included in Excel 2016 for Windows.
Get & Transform allows you to connect to external data sources and perform many different transformations upon them, including unpivoting summarized data, appending several data sources together and setting data types.
Get & Transform is not available in Excel 2016 for Mac.
<Alt>+<;> keyboard shortcut
In the Windows version of Excel 2016, you can use the <Alt>+<;> keyboard shortcut to quickly select visible cells.
This keyboard shortcut is not available in the Mac version of Excel 2016.
Range name scope
Excel 2016 for Windows allows range names to have either worksheet or workbook scope. Range names with worksheet scope can only be referenced within a single worksheet, while workbook scope allows them to be referenced anywhere in the workbook.
Paste names in dialogs
The Paste Names feature enables quick insertion of range names.
Excel 2016 for Mac does not allow this feature to be used within dialogs. It can only be used to insert range names into cells and formulas.
In Excel 2016 for Windows, this feature can also be used to insert range names into dialogs and task panes.
‘Data entered in a table is invalid’ error checking rule
Excel 2016 for Mac contains 8 error checking rules.
Pivot table Defer Layout Update option
Excel 2016 for Windows offers a Defer Layout Update option when editing pivot tables. This allows you to fully configure a pivot table without anything appearing on the worksheet, and without any calculations being performed. This is useful when working with very large data sets that could take a long time to calculate.
The Defer Layout Update option is not available in Excel 2016 for Mac.
Fn+F4 shortcut key in Formula Builder
The Fn+F4 keyboard shortcut is used to quickly define absolute cell references. Unfortunately, this keyboard shortcut cannot be used within the Formula Builder task pane in Excel 2016 for Mac.
The F4 keyboard shortcut works without problems in the equivalent dialog in Excel 2016 for Windows (the Insert Function dialog).
‘Always create backup’ save option
When saving a workbook, Excel 2016 for Windows offers the Always create backup option. If this option is enabled, a separate backup version of the workbook is created whenever the workbook is saved.
This option is not available in the Mac version of Excel 2016.
Embedded object resizing
Excel 2016 for Mac only allows you to merge two workbooks at a time when sharing a workbook using the merge method. You can merge as many workbooks as necessary, but you must merge them one at a time.
Multi-Select button in slicers
Excel 2016 for Mac allows you to select multiple items in a slicer by holding down the Cmd key.
Excel 2016 for Windows adds an additional Multi-Select button to slicers, which allows you to select multiple items without holding down any keys.
Select all search results in pivot table filters
Note that this feature is available in standard filters; it is only missing from pivot table filters.
Pivot table filter by selection
Excel 2016 for Mac enables you to filter an ordinary range of data by selecting a range of cells and then filtering to show only records that match the selected values. However, this option is not available when working with pivot tables.
Excel 2016 for Windows allows this option to be used within pivot tables.
Features that behave differently in Excel 2016 for Windows
Cmd shortcut keys
Many features of Excel 2016 for Mac can be accessed by holding down the <Cmd> key and pressing another key (for example <Cmd>+<b> makes the selected cell bold-faced).
Windows computers do not have a Cmd key, so the Ctrl key is used instead.
Fn shortcut keys
If you are using OS X with its default settings, you need to hold down the <Fn> key to use the F keys at the top of the keyboard within Excel (for example <Fn>+<F4> to make a cell reference absolute).
Ribbon and Menu Bar
Excel 2016 for Windows does not use the Menu Bar that is used for many commands in Excel 2016 for Mac. Instead, all commands are accessed via the Ribbon.
This means that Excel 2016 for Windows has additional Ribbon options to enable access to commands that are in the Menu Bar in Excel 2016 for Mac.
File tab and ‘Backstage View’
In Excel 2016 for Windows, the Ribbon contains an additional tab called File. This tab takes you to Backstage View which contains options for saving, printing and sharing, as well as more advanced Excel options.
These options are accessed via the Menu Bar in Excel 2016 for Mac.
To add an AutoSum in the Mac version of Excel 2016, you must first click the AutoSum button and then press the <Enter> key. The Windows version allows this to be done more quickly by double-clicking the AutoSum button.
AutoSum is covered in: Lesson 2 3: Use AutoSum to quickly calculate totals.
Minimize Ribbon double-click
In the Mac version of Excel 2016, clicking the selected Ribbon tab minimizes the Ribbon. In the Windows version, the Ribbon is minimized by double-clicking any Ribbon tab.
If the Ribbon is minimized in the Mac version of Excel 2016, it can be restored by clicking any tab. The Windows version requires you to double-click a tab in order to permanently restore the Ribbon.
Task pane docking
The Mac version of Excel 2016 allows task panes to either be ‘floating’ or docked to the right side of the screen. The Windows version allows task panes to be docked to either the left or right side of the screen (as well as being floating).
Row height and column width in worksheet groups
Both the Windows and Mac versions of Excel 2016 enable worksheets to be grouped so that changes can be applied to every sheet simultaneously. The Mac version does not apply changes to row heights and column widths to grouped worksheets, while the Windows version does.
Page break indicator lines
Copying worksheets by dragging and dropping
Copying worksheets between workbooks by dragging and dropping
When copying worksheets between workbooks, you might expect the same rules to apply as when dragging and dropping worksheets within a workbook. This is true in Excel 2016 for Windows, but Excel 2016 for Mac always creates a copy of a worksheet when it is dragged to another workbook.
In Excel 2016 for Mac, the help search system is accessed via the Help menu on the Menu Bar. Excel 2016 for Windows replaces this with a Tell me what you want to do option on the Ribbon.
Find & Replace ‘look in’
The Find & Replace dialog contains a ‘look in’ menu that allows you to search in values or formulas. The ‘look in’ feature is available in Excel 2016 for Mac, but only in the Find dialog; it is not present in the Replace dialog. Despite this, the Replace dialog will behave according to the selected ‘look in’ option if you set it in the Find dialog before switching to Replace.
Formula Builder task pane
In the Windows version of Excel 2016, the Formula Builder task pane is replaced by the Insert Function dialog.
The Insert Function dialog has the same purpose and features of the Formula Builder, but appears as a floating dialog instead of a task pane.
In the Mac version of Excel, you can quickly insert a range name by clicking: > Insert > Name > Paste.
In the Windows version of Excel, this feature is accessed using the Ribbon command: Formulas > Defined Names > Use in Formula.
Excel 2016 for Windows offers a separate Name Manager dialog that enables you to view and edit all of the range names that have been defined within a workbook.
In the Mac version of Excel 2016, these features have been included in the Define Name dialog.
Error checking dialog modality
The Mac version of Excel 2016 does not allow you to interact with the workbook as long as the error checking dialog is open (in other words, it is a modal dialog).
Excel 2016 for Windows allows you to make changes to the workbook while the Error Checking dialog is on-screen. This will pause error checking, requiring you to click a Resume button when returning to the dialog.
Scenario summary report formatting
Scenario summary reports created in Excel 2016 for Windows have different formatting to summary reports created in Excel 2016 for Mac. The differences are only cosmetic, so the same values will be displayed in both versions.
Custom views and tables
Excel 2016 for Windows does not allow custom views to be created if a table is present anywhere in the workbook.
The Mac version allows custom views and tables to coexist.
The Protect Windows option does not work in Excel 2016 for Windows, but will work without problems in Excel 2016 for Mac.
Editing embedded Excel objects
In Office 2016 for Windows, it’s possible to edit an embedded Excel object within a Word document without leaving Word.
In Office 2016 for Mac, editing an embedded Excel object always launches a separate Excel window.
PivotTable Builder dialog
Excel 2016 for Mac uses the PivotTable Builder dialog to configure pivot tables.
Excel 2016 for Windows replaces this with a PivotTable Fields task pane.
The dialog and task pane have the same purpose and features.
Locked workbook notifications
When a workbook is locked for editing by another user, you are prompted to open the workbook in read-only mode.
The Windows version of Excel 2016 allows you to choose whether you want to be notified when the other user closes the workbook. Excel 2016 for Mac defaults to this option, so you will always be notified when a locked workbook becomes available for editing.
Manual pivot table sorting
Manual pivot table sorting is possible in both the Mac and Windows versions of Excel 2016.
In Excel 2016 for Windows, this feature must be specifically enabled. Manual sorting is always enabled in Excel 2016 for Mac.
Range name bug
Excel 2016 for Windows contains a bug where the Apply Names feature (shown in: Lesson 4 3: Use range names to make formulas more readable) does not always work correctly.
This bug is not present in Excel 2016 for Mac, so range names should always be applied to formulas without problems.
Filtering pivot tables by multiple values
Excel 2016 for Windows defaults to only allow a single value to be selected in a pivot table filter. To select more than one value, the Select Mutiple Items option must be enabled.
Excel 2016 for Mac always allows multiple items to be selected in pivot table filters.
Pivot table Group and Outline options
Excel 2016 for Mac allows the pivot table outline to be expanded and collapsed by using the commands: Group and Outline > Show Detail and Group and Outline > Hide Detail.
Excel 2016 for Windows uses contains an Expand/Collapse menu in place of the Group and Outline menu and uses the commands Expand and Collapse in place of Show Detail and Hide Detail.