How to Use Power Query to Automatically Combine Multiple Excel Workbooks into One Dataset

The Nightmare of Monthly Reporting

If you work in finance, HR, or operations, you are likely familiar with the “Data Consolidation Nightmare.” Every month, regional managers email you five different Excel workbooks containing their regional sales data. To generate the final company-wide report, you must open each workbook, copy the data, paste it into a master spreadsheet, ensure the columns align perfectly, and repeat this tedious, error-prone process every 30 days.

Microsoft Excel contains a massively powerful, built-in ETL (Extract, Transform, Load) tool called Power Query. Power Query can automate this entire process. You point it at a Windows folder, and it will automatically ingest, clean, and stack every Excel file inside that folder into a single, unified dataset.

Step 1: Organize Your Files

For Power Query to work its magic, the incoming files must be organized.

1. Create a dedicated folder on your computer or network drive (e.g., C:\Monthly_Reports\).
2. Place all the regional Excel files into this folder.
3. Crucial Requirement: The files must share the exact same structure. Column A must be “Date” in all files, Column B must be “Revenue,” etc. The files can have different numbers of rows, but the column headers must match.

Step 2: Connect Power Query to the Folder

1. Open a brand new, blank Excel workbook. (This will become your Master Consolidation file).
2. Navigate to the Data tab on the ribbon.
3. Click Get Data (on the far left).
4. Hover over From File, and select From Folder.
5. A dialog box will appear. Click Browse, select the C:\Monthly_Reports\ folder you created, and click Open.

Step 3: Combine and Transform the Data

Excel will analyze the folder and display a preview window listing the individual files it found (e.g., East_Sales.xlsx, West_Sales.xlsx).

1. At the bottom of the preview window, click the Combine dropdown button.
2. Select Combine & Transform Data.
3. The “Combine Files” window will appear. Excel needs to know which specific worksheet inside those files contains the data. Assuming all your managers put their data on “Sheet1”, click on Sheet1 in the left pane to highlight it.
4. Click OK.

Step 4: The Power Query Editor

The main Excel window will disappear, and the Power Query Editor will open. This is a separate, specialized interface where you can manipulate the data before it enters your final spreadsheet.

You will see that Power Query has successfully stacked all the data from all the files on top of each other. It also smartly added a new column on the far left called “Source.Name,” which identifies exactly which file each row came from.

At this stage, you can clean the data. If a manager accidentally left a blank row at the bottom of their sheet, you can click the dropdown arrow on any column and uncheck “null” to filter out blank rows. Power Query records every click you make as an automated step.

Step 5: Load the Final Data

1. In the top left corner of the Power Query Editor, click the Close & Load button.

The Editor will close, and Excel will dump the perfectly combined, cleaned dataset into a neat green table in your master workbook. You can now build your PivotTables and charts off this master table.

The True Power: Automation for Next Month

The magic of Power Query happens next month. When the new regional reports arrive, you do not need to repeat these steps.

1. Simply drop the new Excel files into the C:\Monthly_Reports\ folder.
2. Open your Master Consolidation workbook.
3. Navigate to the Data tab.
4. Click the Refresh All button.

In the background, Power Query will instantly scan the folder, ingest the new files, apply any data cleaning steps you recorded, and append the new data directly to the bottom of your master table. A process that used to take hours of copying and pasting now takes precisely one click.