How to Merge Two Excel Workbooks Into One
Open both files, right-click a sheet tab, choose Move or Copy, pick the other workbook and tick Create a copy. Power Query merges the data into one table.
2026-10-19
How to merge two Excel workbooks into one
To merge two Excel workbooks into one, open both, right-click a sheet tab in the first, choose Move or Copy, select the second workbook in To book, tick Create a copy, and click OK. Repeat for each sheet, then save. The second file now holds every sheet from both.
That is one meaning of "merge". The other is combining the rows themselves into one table, so January's and February's data sit in a single list. That needs an extra step, and the right method depends on whether the columns in each file are in the same order.
TL;DR: Key takeaways
- Sheets into one file: Move or Copy, with both workbooks open.
- Data into one table, same column order: move the sheets in, then stack them.
- Data into one table, columns in different orders: use Power Query From Folder.
- Formulas that pointed at other sheets become links to the original file.
- For a monthly job, Power Query can be refreshed; a copy-and-stack cannot.
Put the sheets in one workbook
Microsoft's Move or Copy steps:
- Open both workbooks. The destination only appears in the list if it is open.
- In the source file, right-click the sheet tab and choose Move or Copy.
- In To book, choose the destination workbook.
- Choose where the sheet should go in Before sheet.
- Tick Create a copy to leave the original in place, then click OK.
To move several sheets at once, hold Ctrl and click each tab before right-clicking. They travel together.
Check formulas afterwards. Microsoft warns that moving a sheet to another workbook "might cause errors or produce unintended results." In practice, any formula that referred to a different sheet in the source file now points back at that file as an external link. The same problem comes up when splitting sheets into separate files, and the fix is the same: convert those formulas to values if the original file will not travel with the merged one.
Combine the data into one table
Once the sheets sit in one workbook, you may still want their rows in a single list. There are two good ways, and the deciding question is column order.
Same columns, same order: stack the sheets
The free Combine All Sheets macro stacks every sheet in the workbook into one new sheet called Combined, keeping a single header row at the top if your sheets have headers.
It copies each sheet's data below the last, so it works by position: column A goes under column A. That is fine when every file came from the same template. If one file has Region and Amount swapped, they will be stacked into the wrong columns without any warning.
Two other behaviours to know: it includes every sheet in the workbook, hidden ones too, and running it again replaces the existing Combined sheet. Combining multiple sheets into one covers it in detail.
Columns in different orders, or a monthly job: Power Query
Power Query's From Folder import combines files without opening them, and it lines columns up by name. Microsoft is explicit: "the columns do not have to be in the same order as the matching is done by column names."
- Put the workbooks in a folder of their own.
- Go to Data > Get Data > From File > From Folder and choose that folder.
- Click Combine > Combine & Load.
Keep the folder clean, because every file in it is included. The real payoff comes later: drop next month's file into the folder, click Refresh, and the combined table updates. Power Query or a macro explains when that trade is worth the setup.
Which method to use
| Situation | Best method |
|---|---|
| You just want both sets of sheets in one file | Move or Copy |
| Same template, one-off merge into one table | Move or Copy, then Combine All Sheets |
| Columns differ in order between files | Power Query From Folder |
| You will repeat this every month | Power Query From Folder |
Frequently asked questions
How do I combine two Excel files into one?
Open both, right-click a sheet tab in one, choose Move or Copy, select the other file in the To book list, tick Create a copy, and click OK. Repeat for each sheet. To merge the rows into one table as well, stack the sheets with a macro or use Power Query.
Why isn't my other workbook in the Move or Copy list?
The To book list only shows workbooks that are currently open. Open the destination file first, then right-click the sheet tab again and it will appear.
Can I merge workbooks without opening them?
Yes, with Power Query. Data > Get Data > From File > From Folder reads every file in a folder and combines them into one table without opening each workbook.
What if the columns are in a different order in each file?
Use Power Query From Folder, which matches columns by their header names. Stacking sheets by copying them, including with a macro, places column A under column A, so mismatched orders end up in the wrong columns.
Will merging workbooks break my formulas?
It can. A formula that referred to another sheet in the source file becomes an external link to that file after the move. Check them after merging, and convert them to values if the original file will not be kept alongside.
What to do next
Decide whether you want the sheets in one file or the rows in one table. For a one-off merge of files built from the same template, use Move or Copy and then run Combine All Sheets. For anything recurring, set up Power Query From Folder once and refresh it each month.