How to combine two Excel files into one
By the Analistable team · Updated · 5 min read
If both files have the same columns, paste the second file's data rows (without its header) under the first file's last row. If you want both sheets in one workbook, right-click the sheet tab → Move or Copy → choose the other workbook → tick Create a copy. If the files have different columns about the same records, join them side by side with XLOOKUP on a shared ID.
Part of our guide: How to merge Excel files: every method compared
Which kind of combine do you need?
| Your files | You want | Method |
|---|---|---|
| Same columns (e.g. January and February sales) | One longer list | Stack rows |
| Any content | One workbook with two tabs | Move or Copy |
| Different columns, shared ID (customers + orders) | One wider table | Join with XLOOKUP or Power Query |
Case 1: stack rows from two files
- Open both files. In file 1, press Ctrl+End to find the last used row.
- In file 2, select the data rows without the header (click the first data row number, then Ctrl+Shift+End).
- Copy, switch to file 1 and paste into the first empty row below the data.
- Add a column called Source and fill it with the file name for each block, so you can tell the rows apart later.
- Check the column order matches before pasting — copy-paste matches by position, not by header name.
If the columns are in a different order, reorder them first, or let a tool match them by header: the Analistable combiner does this automatically.
Case 1 with a formula: VSTACK in Excel 365
If you'd rather keep both files' data intact and build the combined list from them, first copy both sheets into one workbook (Case 2 below), then on a new sheet enter:
=LET(
all, VSTACK(Jan!A1:E200, Feb!A2:E200),
FILTER(all, CHOOSECOLS(all, 1) <> "")
)The first range includes January's header row; the second starts at row 2 to skip February's header. FILTER removes the empty rows at the bottom of each generous range. VSTACK, CHOOSECOLS and LET need Excel for Microsoft 365 or Excel 2024 (LET alone is also in Excel 2021); in Excel 2019 or earlier, use copy-paste or Power Query.
Case 1 with Power Query: Append, matched by header
- In a new workbook choose Data → Get Data → From File → From Workbook, pick the first file, select its sheet and click Transform Data. Choose Close & Load To… → Only Create Connection.
- Repeat for the second file.
- Choose Data → Get Data → Combine Queries → Append, select the two queries and click OK.
- In the editor, check the types, then Close & Load.
Append matches columns by header name, so a different column order doesn't matter, and a column that exists in only one file is kept with blanks for the other. When either file is updated, Refresh All rebuilds the combined table.
Case 2: put both sheets in one workbook
- Open both workbooks.
- In the second workbook, right-click the sheet tab and choose Move or Copy….
- Under To book, select the first workbook. Choose where the sheet should go.
- Tick Create a copy to leave the original file unchanged, then click OK.
Formulas that referred to other sheets in the original file may now point back to that file. Check Data → Edit Links after copying.
Case 3: join side by side on a shared ID
When file 1 lists customers and file 2 lists their orders, combining means bringing columns across, matched on Customer ID. In file 1, next to the first customer:
=XLOOKUP(A2, [Orders.xlsx]Sheet1!$B:$B, [Orders.xlsx]Sheet1!$C:$C, "No orders")
XLOOKUP returns the first match only. If a customer can have several orders and you need all of them, use Power Query's Merge Queries, which returns one row per match. See joining two tables in Excel.
| Customer ID | Name | Order total |
|---|---|---|
| C-101 | Bakery Lune | 200 |
| C-102 | Hart & Co | No orders |
| C-103 | Nordic Supply | 75 |
Checks after combining
- Row count: stacked rows should equal the sum of both files' rows.
- Duplicates: if the files overlap, use Data → Remove Duplicates on the key column. See merging two spreadsheets and removing duplicates.
- Data types: dates and IDs should be one type after combining, or sorting and lookups will misbehave.
For example, if January has 120 data rows and February has 135, the stacked sheet should have 255 data rows plus one header, so the last row is row 256. A quick =SUM(Combined[Amount]) should also equal the January total plus the February total.
When the join returns “No orders” for customers who do have orders
| Symptom | Cause | Fix |
|---|---|---|
| Every row returns the not-found value | IDs are numbers in one file and text in the other | Convert one side: =TEXT(A2, "0") or =VALUE(A2) |
| Some rows fail, some work | Trailing spaces or non-printing characters | Look up TRIM(CLEAN(A2)), or clean the column first |
| Results go wrong after closing the second file | The formula still points at the external workbook | Copy the sheet into the same workbook, or paste values |
| #NAME? instead of a result | XLOOKUP isn't available in Excel 2019 or earlier | Use INDEX/MATCH |
Doing the same in Google Sheets
Upload both Excel files to Google Drive and open them as Google Sheets. Copy one tab into the other file with right-click → Copy to → Existing spreadsheet. To stack the two tabs, use an array literal, where the semicolon places ranges one above the other:
={Jan!A1:E; Feb!A2:E}Blank rows from open-ended ranges can be removed with =FILTER({Jan!A2:E; Feb!A2:E}, {Jan!A2:A; Feb!A2:A} <> ""). XLOOKUP works in Google Sheets for Case 3 with the same syntax. More options in merging Google spreadsheets.
Frequently asked questions
- How do I merge two Excel files into one workbook?
- Open both, right-click the sheet tab in the second file, choose Move or Copy, select the first workbook under To book, tick Create a copy and click OK.
- How do I combine two Excel files with the same columns?
- Copy the data rows (without headers) from the second file and paste them below the last row of the first. Add a Source column so you know which file each row came from.
- How do I merge two Excel files based on a common column?
- Use XLOOKUP for one match per row, or Power Query Merge Queries to return every matching row.