How to merge two Excel sheets
“Merge” can mean two different things in Excel. You might want to stack two lists with the same columns, or match records by an ID and bring details together. Choose the method that fits your data.
Method 1: Put two lists with the same columns together
Use this method when both sheets have the same column headings, such as two monthly sales lists.
- Make a copy of your workbook first.
- Check that both sheets have the same columns in the same order.
- On the second sheet, select and copy the data rows, not the heading row.
- On the first sheet, click the first empty row below its data and paste.
- Check a few rows to confirm the values went under the correct headings.
For repeatable imports, use Excel's Power Query: select each data range, choose Data > From Table/Range, then use Append Queries to stack the tables. Menu names can differ slightly between Excel versions.
Method 2: Match rows using a shared ID
Use this method when one sheet has IDs and another sheet has details for those IDs. For example, Sheet1 has orders and Sheet2 has customer names.
| Sheet1: Order ID | Amount | Sheet2: Order ID | Customer |
|---|---|---|---|
| 101 | 45 | 101 | Ana |
| 102 | 70 | 102 | Ben |
| 103 | 30 | 103 | Chen |
To bring the customer name beside each order, put this formula in Sheet1 cell C2 and fill it down. The order IDs are in A2:A4 on Sheet1 and Sheet2; customer names are in column B on Sheet2.
=XLOOKUP(A2, Sheet2!$A$2:$A$4, Sheet2!$B$2:$B$4, "No match")In older Excel versions without XLOOKUP, use VLOOKUP if the ID is the first column in the lookup table:
=IFERROR(VLOOKUP(A2, Sheet2!$A$2:$B$4, 2, FALSE), "No match")These formulas return one matching result for each order. If IDs are repeated and you need every matching row combination, the Excel Lookup tool can join two files by a selected column.
Check the result
- Make sure both sheets use the same ID format. For example, text "101" and number 101 may not match as expected.
- Check for extra spaces in IDs and duplicate IDs.
- Review rows that show “No match” before sharing or using the merged data.