Power Query Problems

How to Combine Multiple Completed Excel Forms into One Master Spreadsheet

Nimra MalikNimra Malik Nov 11, 2026 871 views

Question details

The user needs a practical way to consolidate identical Excel forms filled out by different people into a single centralized dataset.

How to Combine Multiple Completed Excel Forms into One Master Spreadsheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Organize your files

Place all the completed Excel forms you wish to combine into a single, dedicated folder on your computer.

2
Import data from folder

Open a new Excel workbook. Navigate to the 'Data' tab on the ribbon, click 'Get Data', hover over 'From File', and select 'From Folder'.

3
Select target folder

Browse to the folder containing your forms, select it, and click 'Open'. A preview window will appear listing all the files.

4
Combine the files

At the bottom of the preview window, click the 'Combine' dropdown button and select 'Combine & Transform Data'.

5
Select the data range

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.

Use Power Query to Consolidate Excel Files from a Folder
Refreshable Data: When new forms are added to the folder, you don't need to repeat this process. Simply open your master spreadsheet, go to the 'Data' tab, and click 'Refresh All' to update the dataset instantly.
Efficient Data Merging

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. 1. Open WPS Spreadsheet: Launch WPS Office, open a new blank spreadsheet, and navigate to the 'Data' tab on the top ribbon.
  2. 2. Access the Merge Tool: Click the 'Merge' button and select 'Merge multiple workbooks into a file' from the dropdown menu.
  3. 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. 4. Configure merge settings: Select the specific merge rule you need, such as combining sheets that share the same name across all files.
  5. 5. Generate master spreadsheet: Click 'Start Merging'. WPS Spreadsheet will automatically extract and consolidate all selected files into a brand new master workbook.
Built-in 'Merge Workbooks' tool requiring zero coding or database knowledge.Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Free, lightweight, and incredibly intuitive for everyday data consolidation tasks.Supports merging multiple sheets from different files into one continuous master sheet.
microsoft office alternative - wps office

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.