Excel library

How to Combine Multiple Sheets with VSTACK in Excel

Copying and pasting rows from separate regional or monthly tabs into one master sheet every week quickly becomes tedious and error-prone. With a single dynamic array formula, you can stack rows from North, South, and West sheets into one live summary list that updates automatically when source data changes. In this tutorial, I'll show you how to combine multiple sheets with VSTACK in Excel using individual ranges, 3D references, and Excel Tables.

Quick answer
  1. Click cell A2 on your summary sheet below your column headers.
  2. To combine specific ranges from separate tabs, enter =VSTACK(North!A2:D4, South!A2:D3, West!A2:D3) and press Enter.
  3. To combine consecutive sheets with matching columns, use a 3D reference such as =VSTACK(North:West!A2:D10) and filter out empty rows.

If your source tabs grow each week, format each sheet as an Excel Table (Method 3) so VSTACK picks up new rows automatically.

What Is the VSTACK Function in Excel?

The VSTACK function appends arrays vertically, placing the second range directly underneath the first in a single dynamic spill range. Its syntax is =VSTACK(array1, [array2], ...), where each argument is a cell range, Excel Table, or 3D sheet reference.

Version note: VSTACK requires Microsoft 365 (Windows, Mac, or Web) or Excel 2024. Older desktop versions such as Excel 2021 and Excel 2019 return a #NAME? error; if you use an older version, see our guide on combining sheets with Power Query or Consolidate.

All three methods below use the same sample workbook with three regional tabs (North, South, and West), each sharing four columns in A1:D1: Order ID, Product, Qty, and Revenue.

SheetData RangeOrdersSample Rows
NorthA1:D431001 Desk Lamp (160), 1002 USB Hub (180), 1003 Webcam (210)
SouthA1:D321004 Keyboard (250), 1005 Mouse Pad (120)
WestA1:D321006 Headset (190), 1007 Monitor Stand (240)

Method 1: Combine Specific Sheets with VSTACK

Use this approach when you want to combine two to five worksheets that are not next to each other or have different row counts. Our data sits in North!A2:D4, South!A2:D3, and West!A2:D3, with matching headers copied into A1:D1 on a blank Summary sheet.

  1. Open the Summary sheet and confirm row 1 holds your four headers in A1:D1.
  2. Select cell A2, leaving enough blank rows and columns below and to the right for all seven orders.
  3. Type the formula below into the formula bar.
  4. Press Enter to spill all seven rows from the three regional tabs into A2:D8.
fxFormula
=VSTACK(North!A2:D4, South!A2:D3, West!A2:D3)
VSTACK formula in cell A2 combining rows from North, South, and West sheets into A2:D8
VSTACK stacks all seven orders from North, South, and West into one list in A2:D8

How this works: VSTACK reads North!A2:D4 first (orders 1001 to 1003), appends South!A2:D3 (orders 1004 and 1005), and finishes with West!A2:D3 (orders 1006 and 1007). Because the result is a dynamic array, editing any order on a regional tab updates Summary immediately.

Include Column Headers Automatically

You can pull the header row from the first tab instead of typing headers manually on Summary. Clear row 1, select cell A1, and include row 1 in the first range only.

fxFormula
=VSTACK(North!A1:D4, South!A2:D3, West!A2:D3)

Note: If any cell inside the destination spill area already contains text, numbers, or spaces, Excel returns a #SPILL! error. Clear the blocking cells below A2 or check our troubleshooting steps to fix an Excel SPILL error.

Method 2: Combine Multiple Sheets with a 3D Reference and VSTACK

When you have many consecutive tabs with identical column layouts—such as monthly tabs from January to December—listing every sheet name by hand is slow. A 3D reference lets you specify only the first and last tab in the sequence.

Make sure the North, South, and West tabs sit next to each other in the workbook, and place Summary outside that span so it does not reference itself.

  1. Arrange your workbook tabs so North is first, South is in the middle, and West is last.
  2. Switch to the Summary sheet and select cell A2.
  3. Type the 3D formula below, replacing sheet names with single quotes if they contain spaces (for example, 'North Sales:West Sales'!A2:D4).
  4. Press Enter to stack A2:D4 from all three tabs into A2:D10.
fxFormula
=VSTACK(North:West!A2:D4)
3D VSTACK formula in A2 returning zero rows in A7:D7 and A10:D10 where South and West have empty cells
A 3D VSTACK range turns empty trailing rows on shorter sheets into zero rows

How this works: Excel evaluates the range A2:D4 on every worksheet sitting between North and West inclusive and stacks them in tab order. Any new tab you drag between North and West later is added automatically, while shorter sheets like South and West return 0 in row 4 because Excel converts empty cells inside array ranges to zeros.

Remove Blank Rows with LET, FILTER, and CHOOSECOLS

To reserve room for future orders without showing rows of zeros, reference a larger block such as A2:D10 and filter out rows where the first column is blank.

  1. Select cell A2 on your Summary sheet.
  2. Enter the formula below using a range taller than your largest source sheet, such as A2:D10.
  3. Press Enter to return only the seven populated rows in A2:D8.
fxFormula
=LET(d, VSTACK(North:West!A2:D10), FILTER(d, CHOOSECOLS(d, 1)<>""))
LET, VSTACK, FILTER, and CHOOSECOLS formula in A2 stacking North through West into A2:D8 with blank rows removed
Wrapping 3D VSTACK in LET and FILTER removes all empty zero rows automatically

How this works: The LET function stores the stacked 3D array in a short variable named d so Excel calculates VSTACK once instead of twice. CHOOSECOLS(d, 1)<>"" checks column 1 (Order ID) for non-empty cells, and the FILTER function drops every empty row where that column was blank.

Note: CHOOSECOLS also requires Microsoft 365 or Excel 2024. Always point CHOOSECOLS to an identifier column that is never left blank on valid records.

Method 3: Combine Excel Tables with VSTACK for Dynamic Updates

If you add new orders to your regional sheets frequently, Excel Tables let VSTACK grow automatically with zero extra blank rows to filter out.

  1. Click any cell inside the data on the North tab and press Ctrl + T (Cmd + T on Mac) to create an Excel Table, keeping My table has headers checked.
  2. Go to Table Design > Table Name and name it NorthSales.
  3. Repeat the same steps on the South and West sheets, naming them SouthSales and WestSales.
  4. Switch to Summary, select cell A2, and enter the formula below.
  5. Press Enter to spill all seven rows into A2:D8.
fxFormula
=VSTACK(NorthSales, SouthSales, WestSales)
VSTACK formula in A2 combining the NorthSales, SouthSales, and WestSales Excel Tables into A2:D8
Stacking named Excel Tables expands automatically whenever you add new rows

How this works: Named Excel Tables exclude their header rows by default when referenced by name, so VSTACK stacks only the data rows from NorthSales, SouthSales, and WestSales. Whenever you type a new order directly under any table, the table expands and the combined list on Summary updates right away.

If you also want to pull the header row dynamically into row 1 of Summary, enter =VSTACK(NorthSales[#Headers], NorthSales, SouthSales, WestSales) in cell A1.

Sort the Combined List or Handle Mismatched Columns

Once your sheets are stacked, wrap VSTACK inside SORT to order the master list by any column—for example, sorting descending by column 4 (Revenue) puts order 1004 (250) at the top.

fxFormula
=SORT(VSTACK(NorthSales, SouthSales, WestSales), 4, -1)

If one sheet has fewer columns than the others, VSTACK fills the missing columns on the right with #N/A. Wrap the formula in IFNA to show blank cells instead of errors: =IFNA(VSTACK(NorthSales, SouthSales), ""). If your goal is a summarized report with totals rather than a raw row list, consider building a Pivot Table from multiple sheets instead.

FAQs

Does VSTACK work in Excel 2021 or Excel 2019?

No. VSTACK is available only in Microsoft 365, Excel for the Web, and Excel 2024. Older versions display a #NAME? error when opening a workbook that uses it.

Why does VSTACK show zeros when combining sheets?

Excel converts empty cells inside fixed ranges or 3D references into 0 when passing them into an array formula. Convert each source range to an Excel Table or wrap your 3D formula in LET, FILTER, and CHOOSECOLS to remove blank rows.

Can VSTACK combine sheets with different numbers of columns?

Yes. When one range has fewer columns than the widest range, VSTACK still combines them and pads the extra columns with #N/A. Wrap the formula in IFNA(VSTACK(...), "") to display empty cells instead.

Does VSTACK copy formatting from the source sheets?

No. Like other Excel formulas, VSTACK returns values only and does not carry over cell fills, borders, dates, or currency formatting. Apply number and date formatting directly to the destination columns on your summary sheet.

How do I remove duplicates after stacking sheets with VSTACK?

Wrap the entire formula in UNIQUE, such as =UNIQUE(VSTACK(NorthSales, SouthSales, WestSales)). Excel removes exact duplicate rows across all columns before spilling the combined result.

Conclusion

For most workbooks where new rows are added every week, Method 3 (named Excel Tables inside VSTACK) is the cleanest setup because it expands without leaving zero rows behind. Use Method 2 with a 3D reference and FILTER when you have dozens of consecutive tabs that share one layout, and reach for Method 1 when you just need a quick formula across two or three sheets.