Data Import & Export

How to Combine Multiple Excel Reports into One Period-Based Summary

Aamir Naveed AkramAamir Naveed Akram Nov 10, 2026 870 views

Question details

Combine multiple ERP-exported spreadsheets with matching headers into a single, automated report that tracks financial metrics across various periods.

How to Combine Multiple Excel Reports into One Period-Based Summary
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.
Before you start

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.

Solution 1Recommended

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.

1
Store source files in one folder

Place all five of your ERP-exported spreadsheets into a single, dedicated folder on your computer.

2
Get data from the folder

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.

3
Transform and combine data

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.

4
Load to a PivotTable

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.

Use Power Query to Import and Combine Files from a Folder
Time-saving automation: Using Power Query eliminates the need to manually append new records. Just drop next month's export into the same folder and click 'Refresh All'.

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. 1. Open your data in WPS Spreadsheet: Launch WPS Office, create a new spreadsheet, and open your exported ERP data files.
  2. 2. Use the Consolidate tool: Navigate to the Data tab and click 'Consolidate' to merge data ranges that share identical headers.
  3. 3. Insert a PivotTable: Select your consolidated dataset, go to the Insert tab, and choose 'PivotTable' to begin summarizing your metrics.
  4. 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.
Easily consolidate data from multiple sheets or files using built-in data toolsFully compatible with Microsoft Excel (.xlsx) formats, formulas, and PivotTablesAdvanced PivotTable capabilities for customized period-based reporting and slicersFree, lightweight, and user-friendly interface tailored for complex financial analysis
microsoft office alternative - wps office

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.