Analistable

How to combine two sheets in Excel

By the Analistable team · Updated · 5 min read

To put two sheets' rows into one list, enter =VSTACK(Sheet1!A1:D50, Sheet2!A2:D50) on a new sheet (Microsoft 365 or Excel 2024) — the first range includes the header, the second doesn't. In older versions, copy the second sheet's data rows under the first. If the sheets hold different columns about the same records, match them with XLOOKUP on a shared ID instead.

Part of our guide: How to combine Excel sheets into one

Stack or match?

Choose the method from the sheets' layout
Sheet 1Sheet 2Combine by
Orders for JanuaryOrders for February (same columns)Stacking rows
Customers (ID, name, city)Orders (ID, date, amount)Matching on ID

Stack two sheets with VSTACK

On a new sheet, in A1:

=VSTACK(Sheet1!A1:D50, Sheet2!A2:D50)

The first range brings the header row; the second starts at row 2 so its header isn't repeated. The result spills into as many rows as needed and updates when either sheet changes.

Empty cells in the source come through as 0 in a dynamic array. If some cells are blank, wrap the sources: =VSTACK(Sheet1!A1:D50, Sheet2!A2:D50)&"" turns everything into text (blanks stay blank), or trim the ranges to the exact data. To drop completely empty rows, see dynamic arrays for combining data.

Stack two sheets without formulas

  1. On Sheet2, select the data rows without the header (click row 2's number, then Ctrl+Shift+Down).
  2. Copy (Ctrl+C).
  3. On Sheet1, select the first empty cell in column A below the data (Ctrl+Down from A1, then one row down).
  4. Paste (Ctrl+V). Check the columns line up — paste matches by position.

Match two sheets side by side

When Sheet1 lists customers and Sheet2 lists orders, bring the order total next to each customer with:

=SUMIFS(Sheet2!D:D, Sheet2!A:A, A2)

or a single value with =XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, ""). SUMIFS adds every matching order; XLOOKUP returns the first one. The full set of join options is in joining spreadsheets.

Customers with totals pulled from the Orders sheet
Customer IDNameTotal orders (SUMIFS)
C-101Bakery Lune200
C-102Hart & Co0
C-103Nordic Supply75

More than two sheets?

The same VSTACK works with a 3D reference: =VSTACK(Jan:Dec!A2:D500) stacks every sheet from Jan to Dec. For a refreshable table, use Power Query Append. Both are covered in combining Excel sheets into one.

Check the result

  • The combined row count should equal Sheet1's rows plus Sheet2's rows (minus the second header).
  • Sort by each column and look at the top and bottom: text in a number column means the columns didn't line up.
  • If both sheets can contain the same record, check for duplicates on the key column with =COUNTIF(A:A, A2)>1.

Excel 2019, 2016 and Mac: no VSTACK

VSTACK and HSTACK are only in Microsoft 365 and Excel 2024. In Excel 2019 or 2016 on Windows, and in Excel 2019 for Mac, you have two practical choices: copy and paste (fine for a one-off) or Power Query (repeatable). Power Query on Windows has been built in since Excel 2016; on a Mac it arrived later and the editor has fewer options in older builds, so check that Data → Get Data opens the editor before relying on it.

  1. Click inside the Sheet1 data and press Ctrl+T to make it a table. Do the same on Sheet2.
  2. With a cell in the first table selected, choose Data → From Table/Range, then Close & Load To → Only Create Connection. Repeat for the second table.
  3. Choose Data → Get Data → Combine Queries → Append, pick the two queries and click OK.
  4. Click Close & Load. The stacked result lands on a new sheet and updates with Data → Refresh All.

Append lines up columns by header name, so it tolerates a different column order on Sheet2 — something copy and paste and VSTACK do not. The details are in Power Query Append Queries.

Combining two sheets in Google Sheets

Google Sheets stacks ranges with curly braces: ={Sheet1!A1:D50; Sheet2!A2:D50} — the semicolon means "put the next range underneath". Google Sheets also has VSTACK, which does the same job. If the second sheet is in a different file, pull it in first with IMPORTRANGE; merging Google spreadsheets covers that case.

Worked example: two months of orders

Sheet1 (January) has a header plus 3 orders and Sheet2 (February) has a header plus 2 orders. =VSTACK(Sheet1!A1:C4, Sheet2!A2:C3) returns 6 rows: one header and 5 orders.

Stacked result
OrderDateAmount
10012026-01-05120
10022026-01-1280
10032026-01-2845
10042026-02-03200
10052026-02-1955

January totals 245 and February 255, so =SUM(C2:C6) on the combined sheet should show 500. Checking that the combined total equals the sum of the source totals is the quickest proof that no rows were dropped or doubled.

Troubleshooting

Common problems when combining two sheets
SymptomCauseFix
#SPILL! instead of resultsSomething is already in the cells the result needsClear the area below and to the right of the formula
Dates show as 45678The result column has General formatFormat the column as a date; the value is fine
Amounts land in the Date columnSheet2 has its columns in a different orderReorder Sheet2, use CHOOSECOLS on it, or use Power Query Append
New rows on Sheet2 are missingThe range stops at row 50Use tables (Ctrl+T) and refer to them by name
XLOOKUP returns blank for an ID you can seeSpaces or numbers stored as text in one sheetClean the key with TRIM, or convert with VALUE

Doing it every month

If a new pair of sheets arrives each month, set the workbook up once: keep each source as a named table, point VSTACK or the Append query at the table names, and paste each month's data into the tables. The combined sheet then refreshes without editing formulas. If the sheets come as separate files rather than tabs, combining two Excel files into one is the better starting point.

Frequently asked questions

How do I combine two Excel sheets into one?
Use =VSTACK(Sheet1!A1:D50, Sheet2!A2:D50) in Microsoft 365, or copy the second sheet's data rows under the first. Use XLOOKUP if you want to match rows instead of stacking them.
Why does VSTACK show 0 in empty cells?
Dynamic arrays return 0 for references to empty cells. Append &"" to the formula or use IF(ISBLANK(...),"",...) to show blanks.
Can I combine two sheets and remove duplicates?
Yes: =UNIQUE(VSTACK(Sheet1!A2:D50, Sheet2!A2:D50)) stacks both and keeps one copy of each identical row.

Related guides