Auto‑Consolidate Monthly Files with Excel Power Query (No Copy‑Paste Ever Again)

Table of Contents

😫 Every month you receive 10–20 Excel files with the same structure, and you waste hours copying rows into one “master” file—hoping nothing breaks.

🗂️ Category: Advanced Excel Functions (Business Automation & Data Preparation)

⭐ Difficulty: ★★★★☆

📝 Content

Excel’s Power Query – From Folder feature lets you automatically combine all Excel files in a folder into one clean, refreshable dataset.

Once set up, you simply drop new files into the folder and click Refresh—Excel handles the rest.

This is a core technique used in finance reporting, sales consolidation, HR headcount tracking, and operations dashboards.

✅ Benefits

• Eliminates repetitive copy‑paste work
• Reduces human errors in monthly reports
• Automatically includes new files with one click
• Creates a scalable, professional data pipeline

🛠️ Procedure

1. Place all source files in one folder

(Ensure the files share the same column structure and sheet name).

2. In Excel, go to Data → Get Data → From File → From Folder, then select the folder.

3. Click Combine & Transform Data and choose:

• The correct worksheet or table
• Any required cleanup (remove headers, change data types)

4. Load the result into Excel or the Data Model.

5. Next month: replace or add files → click Refresh.

📎 Notes

Power Query ignores file names—you can add them back as a column if needed.

Works with hundreds of files without slowing Excel formulas.

Ideal input for PivotTables, Power BI, and management dashboards.

Combine this with LET, LAMBDA, or Power BI for end‑to‑end automation.