How to Create a Pivot Table using Multiple Files in Excel

Last Updated: June 22, 2023
puneet-gogia-excel-champs

- Written by Puneet

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. 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 a long time.

Sometimes we receive or capture our data in different workbooks and in that case creating a pivot table needs some extra effort 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 aw three steps process to create a pivot table by using data from multiple workbooks.

Steps to Create a Pivot Table Using Data from Multiple Workbooks

Important: For this, we need to use the power query, so make sure you have the 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.

Check this out: Power Query Tutorial 

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.

Step 1 – Combine Files using Power Query

First of all, we need to combine all the files into one file with power query.

  • Go to Data Tab -> Get Transformation -> New Query -> From File -> From Folder.
  • Now, from the folder selection window, click on “Browse” and select the folder where you have all the files.
  • Click OK.
  • From here, you’ll get a “Combine Files” window.
  • And from that window, select the worksheet in with you data in all the workbooks. (Quick Tip: Make sure to have the same worksheet name in all the workbooks).
  • Once you click OK and it will edit all the data from the workbooks into power query editor.

Step 2 – Prepare Data for the Pivot Table

Now, we need to make little changes in our data to make it ready for a pivot table.

  • If you look at the data we have got an extra column here with the name of the source file.
  • Right click on this column and select Split Column -> By Delimiter.
  • From delimiter window, select custom, add “-” as a delimiter, and select “Left-most delimiter”.
  • Click OK.
  • After that, delete the second column.
  • Now, rename the first column.
  • One more thing, right-click on the first column again and go to Transform -> Capitalize Each Word.
  • And, now your data is ready. So, click on close and load.

Step 3 – Insert the 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.

  • Select the table and go to Insert Tab and click on the Pivot Table button.
  • Here you’ll get an insert pivot table window.
  • Click OK on insert pivot table window and you’ll get new pivot table in your workbook.

Congratulations! you have successfully created a new pivot table from different files.

Refresh Pivot Table

I’m sure you are thinking about how to refresh your pivot table after updating source files.

It’s simple. 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).

Important Points

Here I have listed some points which you need to remember while using power query for a pivot table.

  • Files should be in a single folder.
  • Data should be in the same format in all the files. Otherwise, you need to make it usable after combining it.
  • The name of the worksheets should be the same in all the files.
  • Make sure you don’t have an error in any file. Or you can use the “Skip error files” option.
  • Don’t rename any workbook.

Must Read Next

  1. How To Create a Pivot table Using VBA
  2. How to Create a Pivot Table from Multiple Worksheets
  3. 30 Pivot Table Tips & Tricks
  4. How to Add Custom Field and Item in a Pivot Table
  5. How to Refresh All Pivot Tables at Once

25 thoughts on “How to Create a Pivot Table using Multiple Files in Excel”

  1. Hi Puneet,

    Kindly suggest if we can multiple worksheets i.e. only one heading will be common in all worksheets, rest all headings will be different.
    I need to compile all types of data from different worksheets into single pivot table. It should work like multiple vlookups w.r.t first workbook.

    Kindly suggest.

    Thanks,
    Udit

    Reply
  2. Hi!
    Thank you for explaining this!

    Is that possible to refresh the data if I delete a workbook and insert the same type of workbook with the same name on the folder where the query is? The problem is because I get a monthly exported excel files from another system which gets updated once a month. All the files looks the same, but they are from different teams in the department, in you case like the different zones. The only thing which would be different are the files time which I have to export. 🙂

    Thank you for your time!
    Best,
    Yasmin

    Reply
  3. Great Article. Can you add another file later on? For example say you have files for Fiscal Year (FY) 16/17, FY 17/18 and FY 18/19 and later on you wanted to add FY 19/20. How would you do this?

    Reply
  4. Thanks for your instructions. I am new to Queries and have been experimenting with combining data from multiple workbooks (Office 365). One thing I just found out is that once you establish a link to a folder, any files in new subfolders will be included. This can be advantageous for organizing source files. Also, do you have any tips for using slicers?

    Reply
  5. Can we create different charts by using single pivot? Now I’m using individual pivot for different chart so it’s consuming more size. Please suggest if any

    Reply
  6. Hi Puneet, Thank you so much for posting this on my request.
    I have a query. please refer to your step where you have asked about the “Combine Files” window. I am getting this window, however, I am not getting the “combine” button at the bottom. I am only getting rest of the three buttons – “Load”, “Edit” and “Cancel”. I am using MS Excel 2013. Any alternate for 2013 version ?

    Reply
  7. Hey Puneet,
    I am using pivot table as a part of my task. Now I was able to calculate percentages, multiplication, subtraction, addition etc., but unable to apply “if condition with 2 and more conditions” along with conditional formatting. Can you please assist on this.
    Regards,
    Sail.

    Reply

Leave a Comment