Excel - How to Change Pivot Table Data Source - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need the Pivot Table Wizard in Excel from Office Home and Student 2016 for Mac. The Cmd-Opt-P that works in Excel 2011 for Mac won't work in the 2016 version. I need to add ranges. The Change Data Source doesn't work: it says because I have dat "from multiple consolidation ranges" and it says to use "Command + Option + P" which doesn't work. Is there another way of bringing up the Pivot Table Wizard?
Solved by G. Q. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 09/03/2018 - 07:12
Hello :)
Excelchat Expert 09/03/2018 - 07:12
Are you able to share your file?
Excelchat Expert 09/03/2018 - 07:19
Are you there?
User 09/03/2018 - 07:20
Excelchat Expert 09/03/2018 - 07:21
Okay, great :)
User 09/03/2018 - 07:21
file is too big
Excelchat Expert 09/03/2018 - 07:21
Ah, I see.
Excelchat Expert 09/03/2018 - 07:22
What are you trying to change on the pivot table?
User 09/03/2018 - 07:23
I'm trying to add an additional range for the source data (the latest month for the yearly table).
Excelchat Expert 09/03/2018 - 07:25
I see. you should be able to do that in the Pivot table options bar.
Excelchat Expert 09/03/2018 - 07:25
Can you add the file to googleSheets and share the link?
User 09/03/2018 - 07:27
It's got years of data in it. Did MicroSoft delete the Pivot Table Wizard from this version of Excel? Or can you bring it up some other way?
Excelchat Expert 09/03/2018 - 07:27
No, it is odd that you cannot bring it up. However, I manually create pivot tables versus using the wizard.
User 09/03/2018 - 07:29
So, how do you add data from a different tab in the worksheet? Every mobth?
Excelchat Expert 09/03/2018 - 07:29
If you click on the pivot table, go to pivot table options and then select change data range.
Excelchat Expert 09/03/2018 - 07:30
You would need to create a new dataset and reference that different tab in the new data range and then extend your pivot table to that range.
Excelchat Expert 09/03/2018 - 07:30
So see my example in the sheet to the right.
Excelchat Expert 09/03/2018 - 07:32
Do you see how my solution would work?
User 09/03/2018 - 07:33
I can't see any formulas in the example.
Excelchat Expert 09/03/2018 - 07:34
Please see D2:D6.
User 09/03/2018 - 07:35
ok I see that.
Excelchat Expert 09/03/2018 - 07:35
Okay, great. Once you do this, you just extend your pivot table range to include the new column of data and then add it to the pivot table in the field options.
User 09/03/2018 - 07:40
OK, so I see this is a little more work but makes up for the missing Pivot Table Wizard.
Excelchat Expert 09/03/2018 - 07:41
Good, I'm glad it will solve your issue :)
Excelchat Expert 09/03/2018 - 07:41
Sorry, it requires a bit more work.
User 09/03/2018 - 07:41
Thank you.
Excelchat Expert 09/03/2018 - 07:41
Is there anything else I can help you with?
User 09/03/2018 - 07:43
It looks like they took out the capability of customizing the ribbon in this version. I thought the one-time purchase would at least be the same as the 365 version, at least at purchase time.
Excelchat Expert 09/03/2018 - 07:45
So, just so you know, we are not affiliated with Microsoft. We provide third-party excel solutions. We are experts at Excel formulas and solutions.
Excelchat Expert 09/03/2018 - 07:45
That is odd though. I do have to say.
User 09/03/2018 - 07:46
Thanks for being there with solutions. I may go back to excel 2011.
Excelchat Expert 09/03/2018 - 07:46
It is my pleasure :)
User 09/03/2018 - 07:47
You're welcome. Signing off.
Excelchat Expert 09/03/2018 - 07:48
Please be sure to rate your service after ending the session :)

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc