Power Query Problems

Combine Data from Matching Excel Sheets Across Multiple Workbooks

Huda Qurayshi Sep 28, 2026 872 views

Question details

The user needs to consolidate data from specific worksheets (Personal, Individual Business, Corporate, and Organization) located in multiple Excel workbooks into central worksheets, handling different structures for each sheet type.

How to Combine Data from Matching Excel Sheets Across Multiple Workbooks

Product: Excel
Device & OS: not provided
Scenario: Consolidating categorized data from multiple Excel files into a single master workbook based on matching worksheet names.
Observed behavior: Multiple workbooks contain identically named sheets but different structures per category, requiring a scalable combination method like Power Query.

Before you start

Before beginning, gather all the source workbooks into a single, dedicated folder on your computer, and ensure the column headers within each specific worksheet category are standardized as much as possible.

Solution 1Recommended

Use Power Query to Combine Matching Sheets

Power Query is the most efficient and robust method to import, transform, and append data from multiple files in a folder based on worksheet names.

Power Query allows you to automate the consolidation process without writing any code. Since each worksheet category (e.g., Personal vs. Corporate) has a different structure, you will need to create a separate query for each category to combine them correctly.

Organize Source Files

Place all the Excel workbooks you want to consolidate into a single, dedicated folder on your computer.

Get Data from Folder

Open a new Excel workbook. Go to the Data tab, click 'Get Data', select 'From File', and then choose 'From Folder'. Browse to your dedicated folder and click Open.

Open Power Query Editor

In the preview window that appears, click 'Transform Data' to open the Power Query Editor.

Filter by Worksheet Name

Locate the column that contains the worksheet names (often named 'Item' or 'Name' after expanding the initial file metadata). Use the filter dropdown on this column to select only one specific sheet type, such as 'Personal'.

Expand and Combine Data

Click the two-arrow Expand icon on the 'Data' or 'Content' column. This will extract and append the data from all the 'Personal' sheets across your workbooks.

Load the Consolidated Data

Review the combined data. Once you are satisfied, click 'Close & Load To...' from the Home tab and select 'Table' to output the consolidated data into a new central sheet in your master workbook.

Use Power Query to Combine Matching Sheets

Automated Updates: By saving this query, you can easily update your master sheet in the future simply by clicking 'Refresh All' on the Data tab whenever new workbooks are added to your source folder.

Solution 2

Use a VBA Macro for Custom Consolidation

If your workbook layouts are consistent within their categories and you need a highly customized, one-click solution, a VBA macro can automate the extraction.

Consolidate Workbooks Easily with WPS Office

WPS Spreadsheet offers powerful data handling tools and full VBA support, allowing you to combine matching sheets from multiple workbooks smoothly and efficiently.

1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet to act as your master workbook.
2. Access Data Tools: Navigate to the Data tab to utilize built-in data consolidation tools for simple merging tasks.
3. Run Consolidation Macros: For complex multi-file merging, use the built-in VBA editor (ALT + F11) to run your consolidation scripts exactly as you would in standard Excel.
4. Save and Share: Save your master file in the widely supported .xlsx format for easy sharing and future data appending.

Seamlessly consolidate data from multiple worksheets and workbooks.Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm, .csv).Built-in support for advanced macros and VBA scripts for automated workflows.Free, lightweight, and features a familiar tabbed interface for easy navigation.

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine sheets with different column structures using Power Query?

Yes. If structures differ, Power Query will append the data based on matching column headers. However, this may result in 'null' values for columns that exist in some sheets but not others. It is highly recommended to standardize columns before combining.

Why do I get an error when expanding the Content column in Power Query?

This usually happens if the source folder contains hidden files, temporary files (like ~$filename.xlsx), or non-Excel files. Ensure you filter out these files in Power Query by excluding names that begin with '~$' or filtering by the '.xlsx' extension before expanding the data.

How do I update my combined sheet when I add a new workbook to the folder?

If you used Power Query to set up the connection, simply navigate to the Data tab in your master workbook and click 'Refresh All'. The query will automatically detect the new file in the folder, process it, and append the new data to your central sheet.

Do I need to keep the source workbooks open to run a VBA consolidation macro?

No, you do not need to open them beforehand. A properly written VBA macro will automatically open each workbook in the background, extract the necessary data, and close the file without requiring manual intervention.

Excel & Data Problems