How to Combine Multiple Excel Worksheets with Power Query for Pivot Analysis
Question details
The user needs to merge multiple worksheets containing similar manufacturing data into a consolidated table using Power Query to run a summary pivot analysis.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating similarly structured manufacturing data across several site-specific worksheets to summarize planned amounts by site, vendor, raw material, and finished product.
- Observed behavior
- The user requires specific steps to effectively merge and rearrange the tables using Power Query so that the final output can seamlessly connect to a PivotTable for expected analytical results.
Ensure that all the worksheets you intend to combine have identically named column headers (including case sensitivity) and matching data types, and convert your data ranges into official Excel Tables (Ctrl+T) to streamline the appending process.
Consolidate Tables Using the Power Query Append Feature
Convert each worksheet's data into an Excel Table, load them into Power Query as connections, and use the Append feature to combine them into one unified dataset for PivotTable analysis.
The most reliable method to combine multiple worksheets within the same workbook is by turning them into Excel Tables and appending them in Power Query.
By doing this, any future rows added to the individual site tables will automatically flow into your consolidated report upon a simple refresh.
Navigate to the first site's worksheet, select the data range, and press Ctrl+T to convert it into a Table. Go to the 'Table Design' tab and give it a clear, identifiable name (e.g., 'SiteA_Data'). Repeat this process for all worksheets you want to combine.
Click anywhere inside your first newly created Table. Go to the 'Data' tab and select 'From Table/Range'. Once the Power Query Editor opens, go to 'Close & Load' > 'Close & Load To...', select 'Only Create Connection', and click 'OK'. Repeat this step for the remaining site tables.
On the 'Data' tab, click 'Get Data' > 'Combine Queries' > 'Append'. In the dialog box, choose 'Three or more tables', then add all your site tables from the 'Available tables' list to the 'Tables to append' list, and click 'OK'.
In the Power Query Editor, verify that all columns have aligned correctly. You can remove unnecessary columns, change data types, or rearrange columns by dragging the headers. This step ensures your final dataset is perfectly structured for the summary.
Click the 'Close & Load' drop-down arrow and choose 'Close & Load To...'. Select 'PivotTable Report' and choose 'New worksheet'. You can now drag fields like Site, Vendor, Raw Material, and Planned Amounts into your PivotTable to build the consolidated summary.
Combine Worksheets by Importing from a Folder
If your site data is distributed across multiple individual Excel workbooks rather than tabs in a single workbook, the 'From Folder' import method is faster.
Easily Merge Multiple Worksheets and Create PivotTables in WPS Spreadsheet
WPS Spreadsheet features an intuitive, built-in 'Merge Worksheets' tool that allows you to consolidate massive amounts of data across different sheets without writing M code or navigating complex query editors. Once your data is merged, you can instantly generate powerful PivotTables to summarize your metrics.
- 1. Open the Merge Tool: Launch WPS Spreadsheet, navigate to the 'Data' tab on the top ribbon, and click on 'Consolidate' or 'Merge Worksheets'.
- 2. Select Worksheets to Combine: In the dialog box, add the ranges from the different worksheets you want to merge. Ensure 'Top row' and 'Left column' are checked under 'Use labels in' if your data has headers.
- 3. Complete the Consolidation: Click 'OK'. WPS Spreadsheet will automatically map and combine the data into a brand-new summary worksheet.
- 4. Insert a PivotTable: Highlight your newly combined data table, navigate to the 'Insert' tab, click 'PivotTable', and drag your desired fields to create a multi-dimensional summary.
Frequently Asked Questions
Why are my columns not aligning correctly when combining tables in Power Query?
This usually happens when the column headers across your worksheets do not match exactly. Power Query is entirely case-sensitive, meaning 'Vendor' and 'vendor' are treated as two different columns. Ensure spelling, spacing, and capitalization are identical before appending.
Can I combine data from different Excel workbooks instead of just worksheets?
Yes. Instead of getting data from tables within the same workbook, use the 'Get Data' > 'From File' > 'From Folder' feature. This allows Power Query to read and append data from every workbook stored within that specific Windows folder.
Does the Power Query PivotTable update automatically if the original worksheet data changes?
The PivotTable does not update instantly in real time. However, you can right-click anywhere inside the PivotTable and select 'Refresh' (or go to Data > Refresh All). This triggers Power Query to fetch the latest data from the source worksheets and push it to your PivotTable.
What should I do if my raw data is not formatted as an Excel Table?
While Power Query can extract data from standard named ranges or raw worksheet grids, converting your data to an official Excel Table by pressing Ctrl+T is highly recommended. It guarantees that any new rows or columns added later are automatically encompassed by the query.
