How to merge Excel files manually
Excel can combine files, but none of the built-in routes is quick for a one-off job, and each has a trap that catches people out.
Copy and paste
For two or three small files, opening each one and pasting its rows under the last is the obvious approach. It works only if every file has its columns in exactly the same order. If April’s export lists Order, Customer, Amount, City and May’s lists Customer, Order, City, Amount, pasting stacks customer names under order numbers, and nothing warns you. It is also easy to paste a second header row into the middle of the data or miss the last few rows of a long sheet.
Power Query: combine files from a folder
- Put all the files in one folder, with nothing else in it.
- In a blank workbook, go to Data › Get Data › From File › From Folder and pick the folder.
- In the window that lists the files, click Combine › Combine & Transform Data.
- Choose a sample file and the sheet or table to use, then click OK.
- Check the result in the Power Query Editor and click Close & Load.
This is powerful and can be refreshed when new files arrive, but it assumes the files share the same structure, because the steps are built from the sample file. It matches columns by header name case-sensitively, so “Email” in one file and “email” in another become two separate columns, each half empty. A header with a trailing space does the same. Many people only notice when the merged table is twice as wide as expected.
Append a few tables
If the data is already loaded as queries, Data › Get Data › Combine Queries › Append stacks two tables, or three or more if you choose that option. The same case-sensitive matching applies, and every file must be loaded as its own query first.
VBA
A macro can loop through a folder and copy each sheet into a master sheet. It is fine for a repeated task but needs a macro-enabled workbook, and most simple scripts copy by position, so they break the moment a column moves.
When this tool helps
Monthly or branch exports. Your billing system gives you one CSV per month, or each branch sends its own sales file. Someone added a column in May, or the export changed its column order after an update. Matching by header name lines everything up anyway, and the Source file column tells you which month or branch each row belongs to.
Survey responses from several forms. You ran the same survey through two forms, and one labels the question “email” while the other says “Email Address “. Columns whose names differ only in capitals or spacing are matched; ones with genuinely different names are kept side by side and listed in the summary, so you can see what needs renaming.
Team copies of one template. Five people each filled in their own copy of a tracker. Merging the copies gives you the full list in seconds, and the source column shows who entered each row.
Sheets inside one workbook. A workbook with a tab per quarter or per region can be flattened into one table by ticking each sheet, which makes it ready for a PivotTable or a filter.
What the tool does differently
- Columns are matched by name, not position. A column can be third in one file and fifth in another. Capitals and extra spaces in headers are ignored, which avoids the most common Power Query surprise.
- Nothing is dropped silently. A column found in only some files is kept and named in the summary, with a count of the files it appeared in. For the sample files the summary reads: “Merged 2 files into 5 rows and 5 columns, matching columns by name. This column is only in some files and left empty for the others: Salesperson (in 1 file).”
- Repeated headers stay apart. If one file has two Amount columns, they remain two columns rather than being squashed into one.
- Formats can be mixed. An .xls from an old system, a CSV from a web app and an .ods from LibreOffice can go into the same merge.
- Headings keep their original spelling. Each column takes its heading from the first file that contains it, so your preferred wording comes from the file you add first.
Tips for a clean merge
Add the file with the best headings first, since it sets the output format and the column names. If your files have no header row, or the headers are unreliable, switch to “By position”: column A goes under column A, and the headings come from the first file. Check the column count in the summary; if it is higher than you expected, two files probably use different words for the same thing, and you can rename one heading and merge again.
Merged exports often overlap, for example when two date ranges share a few days or a customer appears in two branch files. Run Remove duplicates on the result afterwards, comparing on an ID or email column, to finish with one clean list.