Excel 2013 Add Multiple Tables to a PivotTable with the Data Model

Excel 2013 allows you to combine multiple, related tables in a PivotTable using the Data Model. The Data Model is a cut down version of the PowerPivot plugin. This video takes you through how to create create Pivot reports using multiple data sources.

------------------------

[ChesterTugwell]

Hi Mark. You can consolidate multiple data ranges using the old Pivot Table wizard. You can add a button for this to the Quick Access Toolbar - its called the PivotTable and PivotChart wizard.

[OzduSoleilDATA]

Chester, this is fantastic! Thanks. I had no idea about this feature. Your presentation is clear and I can see plenty of use for it.

[sedelkov]

Indeed, this video saved few days for me!
Thank you Chester!

[stephenbrinklow]

Excellent presentation, thanks for sharing

[ChesterTugwell]

Hi Matt
I've put a link to limits and specifications for you in the description.

[MarkCBB]

awesome video, very helpful. is there a way to "Join" to tables that have the same data, but due to the size of the tables, I have had to split them across 3 worksheets, can I create a Master data table in the data module that make the 3 databases 1 database?

[mattlazarus]

Hi Chester, thanks for the video, it's really useful. I'm interested to know if there is a maximum number of tables you can link to a single Pivot Table? Or is it (like with other 64 bit Pivot Table limitations) limited by the amount of available memory on your computer? Thanks for any help in advance.

[ravikumar_r_c]

Hi this was excellent,  can you share this spread Sheet as well..
thanks in advance.

[WalterLiddy]

Does the 'ALL' list just automatically generate from any table in your document, or did you set up each table the same way prior to the 'active' one we're shown?

[tjadam]

I have sales data for the last five years on one spreadsheet and I currently have pivot tables and charts which helps me analyze the data. However, Excel takes surmountable amount of time to open/close/refresh because of the amount of data in the table. Would it be better to create a separate workbook for each year's worth of data and then make a connection to another workbook from where all of the pivot tables and charts reside? Is this even possible?

[deepk]

Hi Chester,

Awesome video!

Just need to clarify one thing, in all the different tables we MUST have common value so that it will get linked ?

- Deepak

[AA-hsgt]

Hi Chester. This video was the best I found so far but I'm having an issue and was wondering if you could help. For some reason I can't seem to get the "add this data to date model" to be a usable option. I'm using office 365, is this the issue? Do you know if I need to go out and buy a separate program to run this option? Please let me know. Thanks.

[stevencarson]

For those saying that the 'Add Data to the Data Model' is greyed out, it may be because you are using a csv file rather than a file saved in XLSX format - solved the issue for me anyway...

[libing]

Hi Chester. I found your video informative and i have made a similar work sheet.  1st sheet with fields EmpID, Name, second sheet with EmpID, Department and third sheet with EmpID and Salary fields .  I want to combine these sheet by EmpID, Name, Department and Salary

For that i have created pivot table and combined these tables using id as my primary key. In the pivot table i want   salary of each employee but i am getting total salary for each employee instead of their actual salary.

Please help me solve this problem.

[davidsanchezvasquez]

"All this data to the Data Model" is disabled. Anyone knows how to solve this?

[sandeepmenon]

Hi Chester. I have a problem with getting the "Add this data to Data model Option". I have followed all the procedures as seen in your video, but when i insert Pivot table that option seems to be blurred. Could you help me ?

[diala_the_bookkeeper]

ouch i have excel 2010 is there any solution😣

[mienzillaz]

it's not what i'm looking for..:( i have many databases which i would like to combine into one pivot.. all have exactly same layout, reason i have then separate is because of smart view timeouts.. and it would be really huge so i had to split it.. anyway i wish if all my tables could behave as one in pivot.. that each element in pivot will give me records from all tables with one click..