How to Combine Multiple Excel Reports into One Period-Based Summary
Question details
Combine multiple ERP-exported spreadsheets with matching headers into a single, automated report that tracks financial metrics across various periods.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- A user needs to consolidate historical data and monthly financial exports (budgets, actuals, encumbrances) to create a repeatable report without manually copying and pasting files.
- Observed behavior
- Instead of manually consolidating individual files repeatedly, the goal is to set up an automated data flow that generates a period-based summary with calculated percentages and expenditures.
Create a dedicated folder on your computer to store all the ERP-exported Excel files, and verify that every file has identical column headers to ensure a smooth consolidation process.
Use Power Query to Import and Combine Files from a Folder
Power Query allows you to automate the consolidation of multiple files stored in a single folder, cleaning the data once and loading it into a dynamic PivotTable.
This method creates a repeatable workflow. Once set up, you only need to add new monthly reports to the source folder and refresh your data to update the summary report.
Place all five of your ERP-exported spreadsheets into a single, dedicated folder on your computer.
Open a new blank Excel workbook. Go to the 'Data' tab on the ribbon, click 'Get Data' (or 'New Query'), select 'From File', and then choose 'From Folder'. Browse to the folder you created.
Click 'Combine & Transform Data'. Select the sheet or table name that applies to all files. Power Query will merge the data and automatically add a 'Source.Name' column, which can be used to identify the reporting period.
In the Power Query Editor, click 'Close & Load To...', and select 'PivotTable Report'. You can now build measures for budget, actuals, and expenditures, and insert slicers for the month and year.

Export a Comprehensive Date-Range File from the ERP System
Sometimes the quickest solution is to avoid manual merging entirely by adjusting the parameters of your ERP export.
Combine and Analyze Data Efficiently with WPS Spreadsheet
WPS Spreadsheet provides powerful tools to consolidate multiple data ranges and create comprehensive PivotTables, enabling you to build dynamic, multi-period financial summaries with minimal effort.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, create a new spreadsheet, and open your exported ERP data files.
- 2. Use the Consolidate tool: Navigate to the Data tab and click 'Consolidate' to merge data ranges that share identical headers.
- 3. Insert a PivotTable: Select your consolidated dataset, go to the Insert tab, and choose 'PivotTable' to begin summarizing your metrics.
- 4. Add calculated fields and slicers: Use the PivotTable tools to add calculated fields for percentages, and insert slicers to filter your report by different periods.

Frequently Asked Questions
How do I ensure my merged Excel reports don't duplicate headers?
When using Power Query to combine files from a folder, the system automatically detects column headers and promotes them, ensuring that the headers from subsequent files are treated as data structure rather than appended as duplicate rows.
Can I add a new month's report to an existing summary without starting over?
Yes. If you set up your consolidation using Power Query 'From Folder', you simply need to save the new month's Excel file into that same folder. Then, open your master workbook, go to the Data tab, and click 'Refresh All'.
What if my ERP-exported files have slightly different column names?
For Power Query to append tables seamlessly, the column headers must match exactly. If they differ, you will need to open the Power Query Editor, manually rename the mismatched columns so they align perfectly, and then apply the changes.
How do I add calculated fields like 'percentage of budget' in my summary?
After loading your combined data into a PivotTable, select any cell within the PivotTable. Go to the 'PivotTable Analyze' tab, select 'Fields, Items, & Sets', and click 'Calculated Field'. You can then build a custom formula dividing your Actuals by your Budget.




