Pivot Table from Multiple Ranges Mac Excel 2016

Question

I am wanting to create a Pivot Table from multiple data ranges. I have searched and I cannot find a way to do this on Mac Excel 2016. in 2011, you could hit comman+alt+P and it would pull up the pivot table wizard. In the 2016 version this command does not open anything. Any suggestion?

Answer 1

btw, I found a topic on the Excel for Mac request page 

"Consolidate Multiple Data Sources in a Pivot Table"

so I added a comment and upvoted the suggestion...

Answer 2

Hi

Thank you for filling me on on these details. Very clear and helpful, indeed.

In Excel 2011 the PivotTable Wizard can be displayed using this VBA code:

Sub ShowPTWiz()

Application.Dialogs(xlDialogPivotTableWizard).Show

End Sub

This code runs fine in Excel 2016 for Windows, The code fails in Excel 2016 for Mac, which means the functionality has been removed without explanation or is broken. It's one of hundreds of removed, broken, or incomplete features in Excel 2016 for Mac. Microsoft is playing a game where they are testing to see how many features they can cut before losing sales. You play the game by reacting to cut features and beg for them to be restored. You do your begging at this site:

Excel for Mac

The idea is to restore only those features where a large number of people ask for the feature to be restored. There is no factor in restoration other than number of votes. The importance or utility of a feature is not a factor included in these metrics. The more votes, the more likely a cut feature is to be restored, so vote vote vote.

Of course, before anyone votes, someone has to make the suggestion that the PivotTable Wizard be restored. So far, no one has done that, but if someone does, hopefully they will reply to this question with a link to the suggestion.

Answer 3

Hi Jim

"Consolidate multiple ranges" isn't just for SQL databases; it is also a really cool way to normalize tabular data as described in this short video clip:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Simple in Office for Windows, not so simple in Office for Mac.

This article (see link below) shows how Excel 2011 handles the requirement - you can't get to it through the menu, you have to go through the quick key command Cmd-Opt-P.

https://support.office.com/en-us/article/Consolidate-multiple-data-sources-in-a-PivotTable-8F476919-40B3-4133-9870-26F4D9F21AD6?ui=en-US&rs=en-US&ad=US

However, this quick key combination is not implemented in Excel 2016, so there does not appear to be a way of accessing the functionality (or the functionality has just been removed).

I can't find any documentation on the Microsoft site about this and the three tech support people I have spoken to so far have been unable to enlighten me.

Hoping you can help...

Answer 4

I have tried that. There is an area to select a table or range however I don't see a way to select multiple ranges from multiple worksheets. If all data was on the same worksheet I could just pick the one range. I am trying to create a pivot table from multiple ranges.

Answer 5

Have you tried the pivot table button on the insert tab?