A pivot table is one of the best ways to summarize data. You can create a summary report within seconds from thousands of rows of data.
It’s a perfect tool.
But while working with data in the real world not everything is perfect. And today, I want to share with you a common problem which we all face.
Before I start, I want to say thanks to Raman Girdhar for his question. He has been following up with me for quite long time.
Sometimes we receive or capture our data in different workbooks and in that case creating a pivot table need some extra efforts for combining those multiple workbooks into one.
Only after that, we can create a pivot table.
So in this post, I’d like to show you a 3 steps process to create a pivot table by using data from multiple workbooks.
So let’s get started.
Important: For this, we need to use power query.
Make sure you have power query in your Excel version. For Excel 2016, it’s there on the Data tab and for other versions (2010 and 2013) you need to install the add-on.
Here I have four different workbooks with sales data for different zones. You can download these files from here to follow along.
And, make sure you have all these files in a single folder.
First of all, we need to combine all the files into one table with power query.
Now, we need to make little changes in our data to make it ready for a pivot table.
At this point, we have a new worksheet in our workbook with the combine data from all the four files.
Now, it’s time to create a pivot table from it.
Congratulations! you have successfully created a new pivot table from different files.
I’m sure you are thinking about how to refresh your pivot table after updating source files.
To refresh your pivot table you need to update the query so that the data from the source file can be updated in the source table which you created with power query. And after that, you can update your pivot table.
But, the simple and best way is to use refresh button from the data tab. It will refersh both (Pivot Table + Query).
Here I have listed some points which you need to remember while using power query for a pivot table.
Because of power query creating a pivot table with multiple files is like a cup of cake. You just need to follow simple instructions.
And the best thing is it’s a one time set up, there is nothing to do again and again other than refresh the query. It can save you a ton of time and a lot of efforts.
I hope you are happy with this tip and found it useful in your work.
Now tell me one thing. Have you ever tried to create a pivot table from different workbooks? Do you have any other method to create to this? Please share with me in the comment section, I would love to hear from you.
And, please don’t forget to share this tip with your friends. They need this thing too.