Microsoft Excel for Mac - PIVOT Tables

Question

Hello Everyone,

I am using a Microsoft Office for Mac.

I want to ask question regarding the pivot function for Microsoft Excel for Mac.

There is an option dialogue box missing from the existing interface he missing option is 'Add this data to the Data Model'.

I have been trying to add my existing worksheet with another worksheet under the same exel document. I read manuals and check the web and this suggest that I need to select/click on the option 'Add this data to the Data Model' in order to add multiple tables to create a pivot table report. I checked Mac version online manual for excel and it stated that the tab should exist in the Mac interface.

Am I missing something on the Mac version of Excel? My existing Mac excel is currently updated. 

My excel is running at Version 16.6.6 - 190114

My notebook is running MacOS Mojave (10.14.2) 

Please advice. Thank you.

Answer 1

I agree that they could have done better. However, I want to give credit where I think credit is due.

When Office 2016 first came out the code base was the same as Office 2011 for Mac. It was a distinctly different code set from Office for Windows. I hated it. I think the first general release build was simply the worst commercial software I had ever used. It crashed. It had bugs. It barely worked at all. I was telling users here in the forum to complain directly to Microsoft's Satya Nadella about how neglected the product was.

Apparently, all our complaints resulted in sweeping changes. During the 2016 product cycle Office, and Excel in particular, underwent a complete remake. They went from 32-bit to 64-bit. The entire code base was abandoned and replaced with the same code base as Office for Windows Word, Excel and PowerPoint. The Visual Basic Editor was rebuilt from scratch (and is still not quite done in 2019). On the Mac side, support for automation via JavaScript and Objective-C was added to AppleScript support. Crashes are practically non-existent. I have used Microsoft Office since 1985 and it has never been as stable as it is today,

I find the number of previously unsupported features that are now supported astonishing considering the short time span since the code base was switched over. The very latest formulas are now supported in Excel - the latest ones being the stock formulas. In Excel 2019 for Mac we now have flash fill, pivotcharts, slicers for tables & PivotTables and PivotCharts, built-in SQL Server ODBC driver, and many many more features.

I think Excel for Mac has undergone the most dramatic positive change of any software I have ever used, and it is evident that Microsoft is continuing to bring it up to par with Excel for Windows. The last remaining frontier is the power tools and accompanying data model piece, which is an enormous amount of coding to work with on a different operating system. They're doing it now! Yay!

While I lament that Office for Mac got way behind development of Office for Windows, I am delighted to see the amount of progress that has been made in a very short period of time. Hence, my advice to nudge the developers to keep at it by giving the feature a vote of support in the UserVoice forum.

Answer 2

Hi Lim,

I am cringing about the terrible advice you've been given so far. It's truly sad that these people get paid to to tell you pure BS.

When you said:

"My excel is running at Version 16.6.6 - 190114" you were running the very latest build of Excel 2016. The message you received that updates are not available was correct. You had the latest update.

You have since updated to 16.21, which is the current general release build of Excel 2019.

And that's nice, but it doesn't change anything because the Data Model is simply not supported at this time in Excel for Mac.

The official statement from Microsoft is that support for the Data Model is planned. You can give a vote of encouragement to the developer team here:

Add support for Get & Transform (formerly Power Query)

Progress is evident in the latest builds of Excel for Mac. When Office 2016 first came out, it would strip a workbook of the Data Model. That was destructive. Now, if a workbook has a data model, you can safely open and save it without losing the Data Model. You can see its existence, but you can't see or edit its properties.

If you are viewing a QueryTable based on a data model that was made in Excel for Windows, you can right-click in the table and see that new features are listed, but grayed out.

It is obvious that work is coming along on implementation.

If you want to be among the first to try these features, join Insider Fast. You'll get weekly builds with the latest features, along with the latest bugs. Microsoft hopes that if you use these features you'll send them feedback via the smile button.

Currently, Insider Fast builds still do not support the data model. I can't tell you when these features will go live, but my gut feeling is that they are coming pretty soon. I expect that a major new feature such as this will probably stay with Insider Fast for quite some time before it gets to general release.

What is Office Insider? - Office Support

Answer 3

Thanks Jim for your advice. I upgraded my license MS Office 2016 to 2019 ...  i paid a very minimal fee for the upgrade from the corporate plan that my employer has with Microsoft. 

Still disappointing to discover that the latest update still could not solve this issue.  Microsoft developers could had done better ...

Answer 4

Hi Jim,

Thanks so much for your advice. I have learned alot from your advice. 

Kind regards,

LIM