Three ways to merge Excel files into one: Move or Copy sheets between workbooks (fastest, manual), Power Query (refreshable, needs matching columns), or a VBA macro (flexible, for the brave). Which one is right depends on a question people skip: are you merging once, or every week? The answer to that changes everything, so it's covered at the end.
Method 1: Move or Copy sheets
The right answer for a one-time merge of a handful of files.
- Open the destination workbook and every source workbook.
- In a source, right-click the sheet tab → Move or Copy.
- Under To book, pick the destination. Tick Create a copy unless you want the sheet removed from the source.
- Repeat per sheet. Rename tabs as you go — Excel appends "(2)" on name clashes.
Formatting, formulas and column widths come across intact. Formulas that referenced other sheets in the source will now point at the source file as an external link — check for [Budget.xlsx] in formula bars.
Where it breaks
It's manual. Ten files, five sheets each, is fifty right-clicks. And it's a snapshot: the merged workbook has no idea when a source changes. So if the source is changing on a frequent basis, this approach does not work.
Method 2: Power Query (Get & Transform)
The right answer when the files share a layout and you'll need to re-do the merge. Power Query stacks every file in a folder into one table and remembers how, so next time it's one click.
- Put the source files in a single folder. Nothing else in it.
- Data → Get Data → From File → From Folder → choose the folder.
- Click Combine & Transform Data. Pick the sheet or table to use as the sample.
- In the editor, remove columns you don't need, then Close & Load.
- Later: Data → Refresh All re-reads every file.
Where it breaks
Every source must have the same columns in the same order; one renamed header and the refresh fails. It's one-directional — the merged table is read-only, so you can't fix a number there and have it flow back. And it produces a table, not the original sheets: formatting, notes and layout are gone. It's a data pipeline, and behaves like one. It is useful for reporting on same-structured files and not designed for collaboration.
You also have no idea what changed since the previous merge.
Method 3: VBA macro
A short macro can loop through a folder, open each file and copy sheets into one workbook — preserving formatting, unlike Power Query. Search "VBA merge workbooks in folder" and you'll find a dozen working versions. It's worth it if you're comfortable with the editor and Move or Copy is too slow. It's not worth it if you'll be the only one who can run it.
The question to ask first: once, or every week?
If you're merging once — closing a project, archiving a year — any of the three methods above is fine. Use Move or Copy and get on with your day.
But if you're doing this every week — Sales sends their sheet, Marketing sends theirs, you assemble the summary, and by Wednesday two of them have changed — then you don't have a merge problem. You have a sync problem. Every merge is a snapshot that's stale the moment a source moves, and the people who own the sources have no idea you took it.
Symphony handles that case differently. In the source workbook you xCopy the worksheet you need. In your summary workbook you xPaste it. It isn't a copy and it isn't a file link — it's the same worksheet connected to the Symphony database, now living in both workbooks. When Sales updates theirs and Submits, one Refresh in your summary pulls it in, with "who changed what and why". The source owners keep working in their own files. Nothing is re-merged, because nothing was ever separate. You can also collaborate and write back to Sales and iterate through the changes till you have alignment.
An Excel add-in, a monthly license for the whole team, Excel 2016 or later on Windows.
Which method?
Move or Copy — one-time, few files, formatting matters. Power Query — repeated, many files, identical layouts, values only. VBA — repeated, formatting matters, you're comfortable with code. Symphony — the sources are owned by other people and keep changing.
Related: how xCopy and xPaste work · why a database behind Excel makes this possible · forecast consolidation in practice
Frequently asked
What's the fastest way to merge two Excel files?
Open both, right-click the sheet tab you want to move, choose Move or Copy, pick the other workbook under 'To book', tick 'Create a copy'. Takes ten seconds for a one-time merge.
How do I combine multiple Excel files into one automatically?
Power Query: put the files in one folder, then Data → Get Data → From File → From Folder → Combine & Transform. It builds a refreshable query that stacks every file with the same layout. It breaks if a source file's columns change.
Can I merge Excel files without losing formatting?
Move or Copy keeps formatting. Power Query and VBA copy values and rebuild the table, so formatting comes from the destination. If the source formatting matters, Move or Copy is the only built-in method that preserves it.
What if the files I merge keep changing?
Then you don't have a merge problem, you have a sync problem — the merged file is stale the moment a source changes. Symphony's xCopy and xPaste put the same live worksheet into more than one workbook, so the summary updates when the sources Submit.