📎 This article includes 1 downloadable practice file ↓

Every month someone drops twelve branch files in a shared folder and you copy-paste them into one sheet. Power Query — built into Excel 2016 and later as Get & Transform — does this in a few clicks, and next month you just press Refresh.

Step by step

1. Data → Get Data → From File → From Folder, and pick the folder.
2. Excel lists the files. Click Combine → Combine & Transform Data.
3. Choose the sample file and the sheet or table to use. Power Query uses it as the template for all files.
4. The Power Query editor opens with every file stacked, plus a Source.Name column showing which file each row came from — keep it.
5. Clean once: remove blank rows, set data types (dates as Date, amounts as Decimal Number), rename columns.
6. Home → Close & Load. You get one table in Excel.

Next month, drop the new files in the folder and click Data → Refresh All. Done.

Filter out the junk files

Folders collect temporary files, old versions and random PDFs. Right after the Source step, filter:

• Extension equals .xlsx (or .csv).
• Name does not begin with ~$ — those are lock files Excel creates while a workbook is open.

When headers differ between files

Power Query matches columns by name. If one branch writes “Amount” and another “Amt”, you get two half-empty columns. Fix it at the source if you can; otherwise add a Rename Columns step in the sample-file query (the “Transform Sample File” query), which is applied to every file.

Power Query vs VBA vs Python

Prefer code? See merge Excel files with Python.

Try it yourself: step by step

1. Download branch-files.zip and unzip it — you get a Monthly folder with four branch workbooks (North, South, East, West).
2. Open a new blank workbook → Data → Get Data → From File → From Folder → select the Monthly folder → Combine & Transform Data.
3. In the dialog pick the sheet named Data and click OK. Power Query stacks all four files and adds Source.Name.
4. Set types: click the Date column icon → Date; Qty and Price → Whole Number. Close & Load. You should see 60 rows.
5. Test the refresh: copy one branch file inside the folder (e.g. North_branch – Copy.xlsx), then Data → Refresh All. Rows increase — delete the copy and refresh again.
6. Make a pivot table from the loaded table with Source.Name in Rows and Qty in Values to check each file contributed.