How to Combine Multiple Completed Excel Forms into One Master Spreadsheet
Question details
The user needs a practical way to consolidate identical Excel forms filled out by different people into a single centralized dataset.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Collecting form data from multiple users, where each user fills out a separate copy of the same Excel template, and merging them for analysis.
- Observed behavior
- The user seeks an automated or efficient method to combine the data into one master spreadsheet rather than manually copying and pasting from individual files.
Ensure all the Excel forms you want to combine have the exact same structure (identical column headers and data formats) and are saved in a single dedicated folder on your computer.
Use Power Query to Consolidate Excel Files from a Folder
Power Query provides an automated, refreshable way to import and combine multiple workbooks stored in the same folder.
Power Query is the most robust tool for this task. It establishes a connection to the folder containing your files, meaning any new forms added later can be included in the master spreadsheet with a simple refresh.
Place all the completed Excel forms you wish to combine into a single, dedicated folder on your computer.
Open a new Excel workbook. Navigate to the 'Data' tab on the ribbon, click 'Get Data', hover over 'From File', and select 'From Folder'.
Browse to the folder containing your forms, select it, and click 'Open'. A preview window will appear listing all the files.
At the bottom of the preview window, click the 'Combine' dropdown button and select 'Combine & Transform Data'.
In the Combine Files dialog box, click on the specific worksheet name or table that holds the form data. Click 'OK' to load it into the Power Query Editor, verify the data, and click 'Close & Load' to generate your master spreadsheet.

Automate Consolidation Using a VBA Macro
If your files require highly customized data manipulation during the import process, a VBA macro can loop through the folder and extract the data automatically.
Redesign the Process with Microsoft Forms
Transitioning to an online form service eliminates the need to distribute and manually combine individual Excel files in the future.
Merge Multiple Workbooks Instantly in WPS Spreadsheet
WPS Office provides a powerful, built-in 'Merge' tool that allows you to combine multiple Excel forms into a single master spreadsheet with just a few clicks, completely bypassing the need to learn Power Query or write complex VBA code.
- 1. Open WPS Spreadsheet: Launch WPS Office, open a new blank spreadsheet, and navigate to the 'Data' tab on the top ribbon.
- 2. Access the Merge Tool: Click the 'Merge' button and select 'Merge multiple workbooks into a file' from the dropdown menu.
- 3. Add your files: In the dialog box, click 'Add Files' or 'Add Folder' to select all the completed Excel forms you want to combine.
- 4. Configure merge settings: Select the specific merge rule you need, such as combining sheets that share the same name across all files.
- 5. Generate master spreadsheet: Click 'Start Merging'. WPS Spreadsheet will automatically extract and consolidate all selected files into a brand new master workbook.

Frequently Asked Questions
Can I combine Excel files if the column headers are not in the same order?
Yes. When using Power Query, the tool intelligently aligns the data based on the exact column header names rather than their physical position in the worksheet.
What happens if one of the submitted forms has extra columns?
If a file has additional columns, Power Query will include those columns in the final master spreadsheet. For the forms that did not have those extra columns, the cells will simply display as 'null' or empty.
Is it better to use Power Query or VBA for combining files?
Power Query is generally superior for standard file consolidation because it requires no coding background, is visually intuitive, and allows for easy data refreshing. VBA is only recommended if you need highly customized, complex data manipulation logic during the import process.
Why does Power Query show an error when combining files?
Errors typically occur if one of the files in the folder is currently open by another user, if a file is corrupted, or if a workbook has a completely different structure (such as missing the target worksheet name). Ensure all files are closed before refreshing your query.




