How to Use Power Query to Combine Multiple Excel Workbooks Automatically

Departmental accounting, sales consolidation, and regional reporting frequently require aggregating data from dozens of separate monthly or regional Excel workbooks into a unified reporting dataset. Historically, analysts manually copied and pasted ranges between files or wrote fragile VBA macros prone to breaking when columns shifted. With Power Query (Get & Transform Data) built into Microsoft Excel, combining dozens or hundreds of disparate workbooks from a shared folder is fully automated into a resilient, refreshable data pipeline.

How Power Query Folder Consolidation Operates

Power Query executes folder consolidation through an automated ETL (Extract, Transform, Load) query pipeline:

• Folder Ingestion: Connects to a directory path and reads metadata (filenames, file extensions, date modified) for all files matching designated criteria.
• Sample File Modeling: Designates the first file as a structural blueprint, extracting table headers, column types, and data ranges.
• Automated Iteration: Applies the exact data transformation steps across every individual workbook in the directory, stacking records vertically into a unified table.
• Dynamic Refresh: Whenever a new monthly report is dropped into the target directory, clicking Refresh All incorporates the new file data automatically without rebuilding formulas.

Connecting Power Query to a Local or Network Folder

Place all target Excel workbooks sharing similar tabular structures into a single dedicated folder:

1. Open a new blank workbook in Microsoft Excel.
2. Navigate to the Data ribbon tab.
3. Click Get Data > From File > From Folder.
4. Browse to your target folder path (e.g., C:\CompanyData\RegionalReports) and click Open.
5. Excel displays an initial file ledger showing file attributes. Click the dropdown arrow next to the yellow Combine button and select Combine & Transform Data.

Configuring the Sample File Transformation Pipeline

Excel opens the Combine Files dialogue window to model data extraction:

1. Under Sample File, leave First file selected (or select a representative template).
2. Select the target worksheet name (e.g., SalesData) or designated Excel Table object.
3. Click OK. Power Query generates an automated helper query group alongside the primary data staging canvas.

Cleaning and Transforming Consolidated Columns in Power Query Editor

In the Power Query Editor window, refine your data before loading it into Excel:

• Filter Unwanted Files: Click the filter dropdown on the Source.Name column to filter out temporary lock files (e.g., files beginning with ~$).
• Set Explicit Data Types: Ensure dates resolve as true Date formats, currencies as Decimal Numbers, and transaction IDs as Text. Click the icon in each column header to assign types.
• Remove Blank Rows and Totals: If regional files included trailing summary or total rows, filter the transaction ID column to remove null or “Total” values to avoid double-counting.

Loading Data to the Excel Data Model or Worksheet Table

Commit your automated ETL pipeline to the workbook:

1. Click Close & Load To… in the upper-left corner of the Home ribbon.
2. Choose your destination:
   • Table: Renders a physical Excel table directly on a new worksheet tab.
   • Only Create Connection & Add this data to the Data Model: Ideal for multi-million-row consolidations analyzed via Power Pivot without cluttering sheet grids.
3. Click OK.

Whenever new monthly files are saved to the folder, simply navigate to Data > Refresh All. Power Query ingests, cleans, and consolidates the new files in seconds, eliminating manual data entry across Microsoft Excel workflows.