Combining various workbooks in a single pivot table

[Aditya Jadhav]

Hello,

Have a big data base with me and need to combine all the data to one single sheet (this is one question how do i do that ?), and how can i combine all those workbooks in a single pivot table ?

Have just selected a few of them only to understand the method of doing things , actually have 17 different sheets for each year and want all the data in a single pivot table so need to know how do get them at one place.

Attaching the files below for the reference.

Regards,

Aditya

[#2]

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:

Combine files in a folder using Get and Transform/Power Query

Hope that helps.. 

Thanks,

Balakrishna

[#3]

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

[#4]

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

[#5]

If only all workbooks are opened, otherwise to make conenction to the file directly from data model (Power Pivot)

[#6]

Dear Balakrishna,

Thanks for the suggestion im definetely gonna try it out and think that will work out.

Regards,

Aditya

[#7]

Dear Sergei,

I have tried doing it when the workbooks are open and was able to do some but due to the heavy data that takes a lot of time and also the excel crashes too. Looking for some option to sort it out.

Regards,

Aditya