Power Query Problems

How to Combine Data From Multiple Excel Workbooks Into a Master Spreadsheet

Maira Mehtab Sep 20, 2026 873 views

Question details

The user needs to consolidate specified cells, sheets, and columns from a variable number of similarly formatted Excel workbooks into a single master workbook on a monthly basis.

Product: Excel
Device & OS: not provided
Scenario: An organization receives multiple monthly reports in separate Excel files and needs an automated way to merge them into one central database.
Observed behavior: The user requires an efficient method to collect and merge data from multiple consistent files into one master spreadsheet without manual copying and pasting.

Before you start

Ensure all the workbooks you want to combine are saved in a single, dedicated folder and share a consistent data structure, such as identical column headers and sheet names.

Solution 1Recommended

Use Power Query to Combine Workbooks from a Folder

Power Query is the most efficient and automated way to import and consolidate data from multiple Excel files located in a specific folder.

By setting up a folder query, Excel will automatically scan the folder for files and merge their contents based on the parameters you define. This method is highly scalable and perfect for recurring monthly tasks.

Organize your files

Create a new folder on your computer and move all the Excel workbooks you wish to combine into this folder.

Initiate Get Data

Open a new or existing master workbook in Excel. Navigate to the 'Data' tab on the ribbon, click 'Get Data', then choose 'From File' followed by 'From Folder'.

Locate the folder

Browse your computer to select the folder you created in step one, then click 'Open'. A dialog box will appear listing all the files contained in that folder.

Combine and Transform

Click the 'Combine' dropdown button at the bottom of the dialog box and select 'Combine & Transform Data'. This will open the Power Query Editor.

Configure sheets and columns

In the Combine Files dialog, select the sample file and choose the specific worksheet or table you want to extract from each workbook. Click 'OK' to proceed to the editor where you can filter specific columns if needed.

Load to master spreadsheet

Once your data is configured, click 'Close & Load' in the top-left corner of the Power Query Editor. The combined data will now be loaded into your master workbook.

Automated Monthly Refresh: Next month, simply drop the new Excel workbooks into the same folder, open your master spreadsheet, and click 'Refresh All' on the Data tab to instantly update your master list.

Efficient Data Management

Easily Combine Multiple Workbooks with WPS Spreadsheet

WPS Office offers intuitive built-in tools to merge multiple worksheets and workbooks seamlessly, allowing you to consolidate data without needing complex Power Query setups.

1. Open WPS Spreadsheet: Launch WPS Office, open a new blank spreadsheet, and navigate to the 'Data' tab located on the top ribbon.
2. Select the Consolidate tool: Click on the 'Consolidate' button. This feature allows you to summarize and merge data from separate ranges or files.
3. Add your source data: In the Consolidate dialog box, click the 'Reference' icon to browse and select the data ranges from the multiple workbooks you want to combine, clicking 'Add' for each one.
4. Configure and Merge: Check the boxes for 'Top row' and 'Left column' if you want to use labels, then click 'OK' to instantly generate your combined master spreadsheet.

Built-in Data Consolidation tools for quick mergingFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Lightweight interface that runs smoothly on older devicesFree to use for daily spreadsheet tasks and data analysis

Frequently Asked Questions

Do all workbooks need to have the exact same formatting to combine them?

Yes, for Power Query to combine files seamlessly, all workbooks should ideally have identical column headers, data types, and sheet names. Variations might result in null values or column mismatches in your master spreadsheet.

What happens if I remove a file from the source folder?

If you delete or move a file out of the source folder and click 'Refresh' in your master spreadsheet, Power Query will update the table and automatically remove the data that belonged to the deleted workbook.

Can I combine workbooks with different sheet names?

It is possible, but it requires more advanced Power Query transformations using custom functions or expanding table objects rather than relying on the standard Combine Files wizard. Standardizing your sheet names beforehand is highly recommended.

Is there a limit to how many workbooks I can merge at once?

Power Query can handle combining hundreds of files from a single folder. However, performance and processing time will depend on your computer's available RAM and the overall file sizes.

Excel & Data Problems