Use data from multiple files in Excel to create a Pivot Table

Use data from multiple files in Excel to create a Pivot Table

Sometimes we need to create a pivot table from multiple workbooks and in that case, to create a pivot table you need to combine those workbooks into one.

The following article guides you in 3 steps to create a pivot table using data from multiple workbooks.

Note: you need to use the power query tool.

With Excel 2016 version, this tool is available at tab, for older versions (2010/2013) you need to install this add-on.

You need to make sure these files are in the same directory.(Read more : compare two columns in Excel)

Step 1 – Combine files using Power Query

  • Go to Data tab -> Get Transformation -> New Query -> From File -> From Folder.
  • For Excel 365 go to tab Data -> Get data -> From File -> From Folder.

  • At the dialog box that opens > Click “Browse” > select the folder containing the files to merge.

  • Click OK.
  • “Combine Files” window as below
  • Select worksheet.
  • Select “Combine & Edit”
  • For Excel 365, select “Combine & Transform data”

  • click OK.

Step 2 – Prepare Data for Pivot Table

  • As shown below you can see there is an added column named Source.name

  • In the window that appears > select custom, add “-” as a delimiter > “Left-most delimiter”.

  • Click OK.
  • Then delete the column source.name 2

  • Next, rename the first column “Zone”

  • 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.(Read more : Power Query)

Step 3 – Create Pivot Table

  • Select the table and go to Insert Tab > Pivot Table.

  • The Create PivotTable window appears.

  • Click OK to finish creating the pivottable.

Refresh Pivot Table

To refresh the pivot table, you use the refresh button from the data tab. It will update both (Pivot table + Query).

Points of note

  • The files should be in a directory.
  • The data must be in the same format in all files. If not, you need to make it usable after combining.(Read more : Excel Charts and Graphs)
  • The names of the worksheets must be the same in all files.
  • Make sure you have no errors in any of the files. Or you can use “Skip error files”
  • Do not rename the workbook.

Learn More About SEO

If you want to learn more about SEO, check out these other helpful resources!