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)
Short version below; a full worked example with screenshots follows the three methods.
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.
A worked example: three department files into one summary
Say Sales, Marketing and Operations each keep a spend file with the same five columns — Date, Vendor, Category, Amount, Approved by — and Finance wants one table every Monday. Here's the Power Query route end to end, with what you'll see on screen.
1. Put the three files in one folder
Create a folder — say Spend-2026 — and drop Sales.xlsx, Marketing.xlsx and Operations.xlsx in it. Nothing else. Power Query reads every file in the folder, so a stray budget draft will be stacked in with the rest.
2. Data → Get Data → From File → From Folder
Point it at the folder and click Open. Excel lists the three files with their sizes and dates. Click Combine & Transform Data — not Load, which would give you a table of file names rather than their contents.
3. Pick the sample
Power Query asks which sheet or table to use as the pattern. Choose the sheet name that exists in every file — Sheet1, or the table if you formatted the data as one. If the three files use different sheet names, this is where it fails; rename them to match first.
4. Tidy in the editor, then Close & Load
The editor opens with a Source.Name column added on the left — the file each row came from, which becomes your department column. Remove any columns you don't want, check the Amount column is typed as a number, then Close & Load. You get one table of every row from all three files, plus a query you can refresh.
5. Next Monday: Data → Refresh All
Departments overwrite their files in the folder; you click Refresh All; the table rebuilds. For a stable, same-shaped set of files, this is the right tool and it's built in.
Where the example breaks in real life: Marketing adds a Campaign column in week three and the refresh errors. Operations sends a corrected file after you've already presented the summary. Someone asks why Sales' September total changed between Monday and Wednesday, and the answer — that Sales overwrote their file — isn't recorded anywhere. Power Query rebuilt the table faithfully. It just can't tell you what moved.
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.