Mac Excel 2011, Pivot Table from multiple workbooks

[vanclute]

Please forgive if this has been covered, I searched but there were *so* many posts on the general topic and the first bunch I looked at were all Windows-only solutions, so I decided I just needed to post anew.

I'm on Mac Excel 2011, so there is no Pivot Table Wizard. I have 4 workbooks, each with an identical table (layout that is, data is of course unique in each) that I would like to summarize in a single pivot table in a different workbook.

Is it possible to create a pivot table in one workbook from data contained in tables in 4 other workbooks using Mac Excel 2011 and preferably without requiring VB, and if so... how would I go about this?

Thanks!

 

[vanclute]

Wow that's awful. Thanks for the heads up. Fortunately I've finally learned, after 30 years of using a Mac, not to upgrade every time a new version of anything (including the OS) comes out. My last attempt was Yosemite and it went horribly wrong, thank goodness I had though to clone my boot HD before the upgrade. I'm still on Mavericks on all machines in the house (except one that is still on Lion!) and have no plans to change until something I absolutely must use, requires me to do so!

So all that being said... though I can now open the Wizard, I can't get it to allow me to select ranges from multiple workbooks. Every time I select a range from an open workbook, as soon as I hit the Add button, it strips the workbook out of the resulting reference and leaves me with only the worksheet name and range. So no way to know what book it came from.

I've read countless pages on this subject and every one of them either says to use Krill's VBA code (or some modification thereon) or some sort of 3rd party add-in, or manually merging all the data into a single table.

Is there a working set of instructions for how to actually get a single pivot table from multiple workbooks that each contain identically-formatted and named tables?

 

[vanclute]

WHEW. At long last, I got it. Had to manually type in my references as using the mouse to select the ranges was just not working properly, but in the end I got there.

Or so I thought. As I typed the above, Excel finally finished processing the tables and...

OUT OF MEMORY.

This on a machine with 32GB of RAM in it and basically nothing open but Excel and Chrome. I think Excel is now in a very bad state... probably going to have to relaunch and try again. Kinda doubtful this will ever work now.

 

[vanclute]

Well it took hours of trial & error and tons of googling, but I finally got what I wanted, more or less. I still don't really understand what's going on here, but I can see what I want in a single place now so I'm not gonna ask.

This is the tutorial that finally got me on the right track: http://www.contextures.com/xlPivot08.html

Perhaps it will help someone else one of these days too. Thanks starl for all your assistance!

 

[jonsamson]

ANy suggestions how to do this on the Mac Excel 2016 - that is what I have and I desperately need to get Pivot Tables from multiple worksheets.