Merge many Excel/CSV files into one sheet: Power Query steps and 3 gotchas

Every month someone sends you twelve branch reports, or a folder of CSV exports, and you spend an hour copy-pasting them into one sheet. Excel already has a built-in way to do this: Power Query. Here is the short version, plus three things that usually break it.

Power Query: combine every file in a folder (Excel 2016+ / Microsoft 365)

1. Put all files you want to merge in one folder (nothing else in it).
2. In a new workbook: Data > Get Data > From File > From Folder, pick the folder.
3. In the file list, click Combine > Combine & Transform Data.
4. Choose the sample file and sheet, then OK. You get one table with a Source.Name column telling you which file each row came from.
5. Close & Load. Next month, drop the new files in the folder and hit Refresh All.

3 gotchas

1. Columns missing from the sample file disappear. The auto-generated Expanded Table Column step only remembers the column names of the sample file you picked in step 4. If later files have an extra column, it is silently dropped unless you edit that step (or replace the hard-coded list with Table.ColumnNames of all tables).

2. Title rows become headers. If a report starts with a line like September 2026 sales report, that line gets promoted to the header. Remove Top Rows fixes it, but only when every file has the same number of title lines.

3. Total rows get merged too. Each file's Total / Subtotal row is appended like any other row, so your grand total is inflated. Filter them out before you sum.

If your files are messy

Power Query is the right tool once you are comfortable with it. For people who just want a double-click, we made a small Windows program, ExcelMerge ("엑셀합치기"): drop .xlsx / .xlsm / .csv files into a folder and run it. It

• matches columns by name, even if the order differs, and appends columns that only exist in some files,
• finds the real header row under title lines,
• moves total/subtotal rows to a separate sheet,
• adds source file and source sheet columns,
• keeps leading zeros and turns "1,250,000" into a number.

It runs offline, needs no Python install, and was built with help from AI tools and tested on fake data, so please try Power Query first and treat the program as a convenience, not a guarantee. The UI and sheet names are in Korean.

Get it here (about $3): https://rainlover32.gumroad.com/l/hxmdp