Analistable

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?

Three ways two files can be combined
Your filesYou wantMethod
Same columns (e.g. January and February sales)One longer listStack rows
Any contentOne workbook with two tabsMove or Copy
Different columns, shared ID (customers + orders)One wider tableJoin with XLOOKUP or Power Query

Case 1: stack rows from two files

  1. Open both files. In file 1, press Ctrl+End to find the last used row.
  2. In file 2, select the data rows without the header (click the first data row number, then Ctrl+Shift+End).
  3. Copy, switch to file 1 and paste into the first empty row below the data.
  4. Add a column called Source and fill it with the file name for each block, so you can tell the rows apart later.
  5. 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

  1. 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.
  2. Repeat for the second file.
  3. Choose Data → Get Data → Combine Queries → Append, select the two queries and click OK.
  4. 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

  1. Open both workbooks.
  2. In the second workbook, right-click the sheet tab and choose Move or Copy….
  3. Under To book, select the first workbook. Choose where the sheet should go.
  4. 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.

Joined result: Customers with their order totals
Customer IDNameOrder total
C-101Bakery Lune200
C-102Hart & CoNo orders
C-103Nordic Supply75

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

Lookup problems between two files
SymptomCauseFix
Every row returns the not-found valueIDs are numbers in one file and text in the otherConvert one side: =TEXT(A2, "0") or =VALUE(A2)
Some rows fail, some workTrailing spaces or non-printing charactersLook up TRIM(CLEAN(A2)), or clean the column first
Results go wrong after closing the second fileThe formula still points at the external workbookCopy the sheet into the same workbook, or paste values
#NAME? instead of a resultXLOOKUP isn't available in Excel 2019 or earlierUse 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.

Related guides