Table of Contents
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
- Right click on that column and choose Split Column -> By Delimiter.(Read more : Split delimiters into rows)
- 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.