First, make the files match
Merging only works cleanly when the files have the same structure. Open two or three of them and compare. The columns should have the same names, and the data should sit on a sheet with the same name in every file.
Differences that look minor cause real problems. A column called Cust ID in one file and Customer ID in another becomes two separate columns in the result, half empty each. Fix the names first, or plan to fix them in Power Query.
Combine a folder with Power Query
Power Query is built into current versions of Excel, and this is the method to reach for when there are more than a handful of files.
- Put all the files in one folder, with nothing else in it. Power Query also reads subfolders, so keep those out too.
- In a new workbook, choose Data > Get Data > From File > From Folder, then pick the folder.
- In the preview, choose Combine & Transform Data.
- In the Combine Files box, pick the sheet or table to read. Power Query treats every file the way it treats this example file.
- In the editor, check the column types. Set IDs and ZIP codes to Text, so they keep their leading zeros.
- Choose Close & Load.
Power Query adds a column naming the file each row came from. Keep it, because it’s the fastest way to trace a strange row back to its source.
Next month, drop the new file in the folder and choose Data > Refresh All. You don’t repeat any of the steps above.
Only a few files? Copy the sheets
For two or three workbooks, you can right-click a sheet tab, choose Move or Copy, pick the destination workbook and tick Create a copy. Then paste the rows together by hand under one heading row.
It is simple and leaves an easy trail for a one-off job. It gets tedious quickly, and there is no refresh when the data changes.
Stack CSV files from the command line
When every file is a CSV with the same columns, your computer can join them without any spreadsheet. On Windows, open Command Prompt in the folder and run:
copy /b *.csv combined.txtOn a Mac or Linux machine, open Terminal in the folder and run:
cat *.csv > combined.txtThe output is named .txt so the command doesn’t try to include its own output. Rename it to .csv when it’s finished. Two things to watch for: each file’s heading row now appears in the middle of the data, and a file that doesn’t end with a line break will run its last row into the next file’s first.
Our file cleaner removes those repeated heading rows and tells you how many it found, which is a quick way to confirm that every file was picked up.
Check the merged result
- Row count. Add up the rows in each source, without headings, and compare with the result.
- Money totals. Compare a total per file with the source file’s own total.
- Half-empty columns. A column filled for only some files usually means a heading didn’t match.
- Duplicates. Files often overlap, such as monthly exports that include the last days of the previous month. See our guide to removing duplicates before you delete anything.
- Size. One worksheet holds 1,048,576 rows at most. If you are close to that, a spreadsheet has stopped being the right container, and a database is the better home.