Forum Discussion
Combining various workbooks in a single pivot table
Hi Aditya,
You can use "Get and Transform" feature in Excel 2016 (Power Query in Excel 2013 and Excel 2010) to combine the data from each of these workbooks into one single workbook. Once this is done, all the data will be available in one table and a regular Excel pivot table can be built on it.
Here is a microsoft support article to combine multiple files into one workbook:
https://support.office.com/en-us/article/Combine-files-in-a-folder-with-Combine-Binaries-Power-Query-94b8023c-2e66-4f6b-8c78-6a00041c90e4
Hope that helps..
Thanks,
Balakrishna
Dear Balakrishna,
Thank you so much for the help it has helped me a lot and made a lot easier. Now only one more querry that whe the files become very huge they tend to work slow infact very slow, so is there any way to build an pivot table using data from various workbooks ?
Regards,
Aditya
Hi Aditya,
Sounds like you are loading the combined data from all the sheets to the workbook. Instead you can load all the data to Excel data model. You have the option to load the data to the Excel data model in the "Load To" dialogue of Power Query as "Add this data to the data model". Once loaded into Excel data model, you can open the power pivot (Excel data model) window by clicking "Manage" command from "Data Model" group in "Power Pivot" tab.
In this Power Pivot window, you have the option in "Home" tab to create a Pivot Table on top of the data in the excel data model. This Pivot table will be sitting inside your workbook like a regular pivot table, but this is lot more powerful and runs much faster.
Hope that helps..!!
Thanks :)
Balakrishna
Dear Balakrishna,
Thanks for the suggestion im definetely gonna try it out and think that will work out.
Regards,
Aditya
