Ever needed to create a pivot table from multiple workbooks in Excel, but unsure how to get it done? Combining data efficiently across workbooks can be overwhelming if you’re unfamiliar with the right tools, but with Power Query, it’s a whole lot easier than you think. In this guide, I’ll walk you through the steps to gather and combine data from several Excel workbooks, then use that to create pivot tables. We’ll even look at how to manage data from multiple worksheets inside the same workbook.
Here’s the best part—if someone updates the information in any workbook, it automatically syncs with your pivot table. Ready to streamline your workflow and boost your data analysis game? Here’s how to do it step-by-step.
Step 1: Making Sure Your Data is Ready
Before we jump into Power Query, you need to make sure the data in each of your workbooks is formatted correctly. Each set of data needs to be in a Table for Power Query to combine everything effectively.
Creating a Table From Your Data
- Open your workbook with the data.
- Select any cell within your data and press Ctrl + T.
- In the dialog, make sure the checkbox for “Table has headers” is checked, then click OK.
Consistent Table Names Matter
One crucial point here—you need consistent table names across your workbooks. Excel automatically names new tables with a generic “Table1”, which is fine. The key is to make sure every table across all workbooks has the same name. If you stick with “Table1”, use it everywhere.
Once you’ve prepped each workbook with tables, save the changes.
Step 2: Using Power Query to Combine Data From Multiple Workbooks
Now that your data is in tables, it’s time to use Power Query to pull all that information into a single workbook.
- In your main workbook (where you want the combined data), click on Data in the menu, then select Get Data > From File > From Folder.
- Navigate to the folder containing all your workbooks, and select that folder. Click Open.
- In the dialog that appears, you’ll see a list of your Excel files. Click Combine and Transform Data.
Power Query will ask you which table to combine. Since we made sure all tables are named the same (“Table1”), select that, and it will pull in data from across all the workbooks.
RELATED TOPIC: How to Import Multiple CSV Files Into Separate Worksheets in Excel
Cleaning Up the Data
At this point, Power Query shows a preview of your combined data with all fields displayed. This is where you can do some quick cleanup if needed. If any column formatting is off or any unnecessary fields are included, right-click on them to remove or adjust as necessary.
Once you’re happy with how the data looks, click Close & Load. Your merged data gets loaded into a new worksheet within your file, ready to be used for analysis!
Step 3: Creating the Pivot Table
With the combined data now in one place, let’s move on to creating the pivot table:
- Select any cell in the new table of combined data.
- Go to the Insert tab and click PivotTable.
- Choose whether you want the pivot table in a new worksheet or in the existing one (usually, it’s better to create it in a new worksheet for clarity).
- Click OK, and Excel generates the pivot table framework for you.
Adding Your Data to the Pivot Table
From the available fields list, drag the fields you want to analyze into the Values, Rows, and Columns sections. For example, if you want to look at Quantity Sold by Location:
- Drag Quantity Sold to the Values area.
- Drag Location to the Rows area.
Now your pivot table is set up! It dynamically calculates and displays the relevant totals. The beauty here is Power Query keeps everything connected—if anyone updates the original source data, you can refresh these values with a click.
Step 4: Handling Multiple Worksheets in a Workbook
If each workbook contains multiple worksheets—let’s say you have both Sales Data and Inventory Data—and you need to create separate pivot tables for each, don’t worry. The process doesn’t change much.
RELATED TOPIC: How to Combine Multiple Columns to a Vertical List in Excel
Naming Your Tables Appropriately
For clarity, rename the tables based on their contents (it will save you a huge headache later).
- Select any cell in your table on each worksheet and press Ctrl + T.
- In Table Design, give your table a name that’s descriptive of the data, like “SalesData” or “Inventory.” FYI: Excel doesn’t allow spaces in table names (so use “SalesData”, not “Sales Data”).
Do this for every relevant worksheet, ensuring that the names are consistent across all workbooks (e.g., every “SalesData” table has to be spelled the same on all workbooks).
Importing Data From Multiple Worksheets
The import process is almost identical:
- Get Data > From Folder again, as we did earlier.
- Select Combine but, this time, be sure to pick the right table for each specific task (for instance, pick SalesData first if that’s the content you want for the first pivot table).
- If needed, create separate queries for InventoryData and other sets of data.
Step 5: Updating the Pivot Table Automatically
One awesome benefit of using Power Query for combining workbooks is that your pivot table updates as soon as the original individual workbook is changed.
Here’s how it works:
- Let’s say someone updates the Sales Data in the workbook for Asia, increasing the quantity sold.
- When they save their changes, go back to your combined data workbook, click Data, and hit Refresh All.
- Your combined data updates, and if you refresh your pivot table after this, it reflects the changes immediately.
This ensures your analysis remains up-to-date and accurate without manually pulling the data in every time something changes.
RELATED TOPIC: How to Perform Calculations in a Pivot Table in Excel
Conclusion
Creating a pivot table from multiple workbooks in Excel is painless when using Power Query. By prepping your data correctly, Power Query lets you easily combine everything into a single dataset for analysis. Even better—your pivot tables can be updated automatically when data in the original workbooks changes.
Not only does this save time, but it ensures everyone is always working with real-time info. Share these steps with your team so the whole workflow becomes more streamlined.
If this guide helped you elevate your Excel skills, don’t forget to like the video and subscribe for more tips!
Jump over to my website for written instructions to follow along. Thanks for reading!
Get Started with Microsoft 365
Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.
This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.