Combine multiple Excel files into one
Thirty files of the same shape, one per branch or month, to bring together in one table. Copy and paste works until the day a column has moved.
In Excel, step by step
Put them together
Put all the files in one folder, with no other file.
Power Query
Data, Get Data, From File, From Folder, pick the folder, then Combine and Load.
Keep the origin
Power Query adds each file's name as a column: keep it, to know where each row comes from.
Pitfalls
- Columns in a different order, or named differently ("Customer code" and "Customer no."), are placed side by side instead of being merged.
- A title or total row in one file ends up in the middle of the data.
- A file whose numbers are stored as text turns the whole column into text.
Faster, on the whole file
Merge Excel files stacks as many files and sheets as you want, matches columns by name even when out of order, shows those that do not match, and adds each row's source.
Frequently asked questions
- And to link two files by a shared column rather than stack them?
- That is a lookup, not a merge: Look up between two files.