VSTACK in Excel: combine tables from multiple sheets into one

The VSTACK syntax

=VSTACK(array1, [array2], …)

• Each array is a range, a table or the result of another formula. VSTACK appends them in the order you list them.
• The result is a dynamic array: it spills from the formula cell into as many rows and columns as it needs, so keep that area empty.
• The columns are combined by position, not by header name. Column 3 of the second range lands under column 3 of the first, whatever its header says.
• If one array is narrower than the others, the missing cells show #N/A. Wrap the stack in IFNA(…,"") or fix the source.

VSTACK is available in Microsoft 365, Excel 2024 and Excel for the web, not in Excel 2021 or older. Its partner HSTACK puts arrays side by side instead of on top of each other.

Stack three exports and keep one header row

Brightline Office Supply receives one order export per region, each with its own header row (rows 1, 7 and 12 in the example). Stacking them as they are, =VSTACK(A1:C5,A7:C10,A12:C16), returns 14 rows: 11 orders plus three header rows, two of them in the middle of the data, where they break sorting, counting and every total that expects numbers.

Keep the first range with its header and drop the header from the others: =VSTACK(A1:C5,DROP(A7:C10,1),DROP(A12:C16,1)). DROP(array,1) removes the first row. The result has 12 rows: one header and 11 orders. The 9th row of the stack is W-301, the first West order, right after the four North and three South orders. To stack only data rows, for example as the input to another formula, leave all headers out: =VSTACK(A2:C5,A8:C10,A13:C16).

Worked example: three regional exports stacked into one table

Rows 1 to 16 are the North, South and West exports, each with its own header row. Rows 19 to 29 ask questions of the stacked table. Each answer is one formula built on the same VSTACK, so you can check it against the exports by eye.

Worked example: three regional exports stacked into one table

	A	B	C	D
1	Order	Customer	Amount	North export
2	N-101	Harbor Print Co.	420
3	N-102	Maple Law Group	1,150
4	N-103	Coral Realty	310
5	N-104	Harbor Print Co.	680
6
7	Order	Customer	Amount	South export
8	S-201	Delta Logistics	890
9	S-202	Willow Pharma	240
10	S-203	Coral Realty	515
11
12	Order	Customer	Amount	West export
13	W-301	Maple Law Group	760
14	W-302	Willow Pharma	1,320
15	W-303	Harbor Print Co.	205
16	W-304	Delta Logistics	455
17
18	Question about the stacked table			Answer
19	Rows in the stack, one header row kept			12
20	Orders in the combined table			11
21	Rows without DROP (headers stacked too)			14
22	Total amount, all regions			6,945
23	Row 9 of the stack (first West order)			W-301
24	Customer of the largest order			Willow Pharma
25	Harbor Print Co., all regions			1,305
26	Distinct customers			5
27	Source column (HSTACK): region of order 8			West
28	West revenue from the tagged stack			2,740
29	Check: stack total minus the three exports			0

Fictional order data of a fictional company. In Excel you would put the VSTACK formula once in an empty cell, for example F1, and refer to the result as F1#. The grid repeats the VSTACK inside every answer so each one can be checked on its own.

Combine multiple sheets: 3D references and Excel Tables

When each region lives on its own sheet with the same layout, a 3D reference stacks all sheets between two tabs in one go: =VSTACK(North:West!A2:C200). It includes every sheet that sits between North and West in the tab order, so a new region sheet dragged into that block is picked up automatically. The catch: a fixed range such as A2:C200 includes the empty rows below the data, and VSTACK returns them as rows of zeros. Remove them with FILTER on a column that is never blank:

=LET(s,VSTACK(North:West!A2:C200),FILTER(s,CHOOSECOLS(s,1)<>""))

The cleaner way is to turn each export into an Excel Table (Ctrl+T) and stack the tables: =VSTACK(tblNorth,tblSouth,tblWest). A table reference covers exactly the data rows and grows with new orders, so there are no blank rows and no headers to drop. Add one header row on top with =VSTACK(tblNorth[#Headers],tblNorth,tblSouth,tblWest). The FILTER guide shows how to keep only the rows you need from the stacked result.

Add a source column with HSTACK

Once the exports are stacked, you can no longer see which row came from which region. Add the region as an extra column before stacking. IF(A2:A5<>"","North") returns "North" once for every row of the North block, and HSTACK puts it in front of the data:

=VSTACK(HSTACK(IF(A2:A5<>"","North"),A2:C5), HSTACK(IF(A8:A10<>"","South"),A8:C10), HSTACK(IF(A13:A16<>"","West"),A13:C16))

The tagged stack has four columns: Region, Order, Customer, Amount. In the example, its 8th row is tagged West, and filtering column 1 for West returns revenue of 2,740. Point the IF at a cell that holds the sheet or month name instead of typing the text, and renaming that cell renames all its rows. The same trick labels monthly files with their month, or two systems as "Shop" and "ERP" before you compare them.

Totals, lookups and summaries on the stacked table

Put the VSTACK formula once in an empty cell, say F1, and refer to the whole result with the spill reference F1#. =ROWS(F1#)-1 counts the orders (11), =SUM(INDEX(F1#,0,3)) totals the Amount column (6,945), and =XLOOKUP(MAX(INDEX(F1#,0,3)),INDEX(F1#,0,3),INDEX(F1#,0,2)) returns the customer of the largest order: Willow Pharma with 1,320. Because INDEX(F1#,0,3) is a real range, it also works inside SUMIFS: Harbor Print Co. ordered 1,305 across all three regions, and =ROWS(UNIQUE(INDEX(F1#,0,2))) finds 5 distinct customers.

For a summary table by region or customer, GROUPBY (Microsoft 365) groups and totals the stack in one formula, or use UNIQUE with SUMIFS for a version that also works where GROUPBY is missing. To pull extra columns such as the customer's segment from a master list, add an XLOOKUP column next to the stack.

VSTACK, Power Query or copy and paste?

Copy and paste works once, but every new month means doing it again, and a late correction in one export never reaches the combined sheet. VSTACK is live: change an amount in the West export and every total updates. It is the right tool when the sources are in the same workbook, have the same column order and stay within a few hundred thousand rows.

Power Query (Data > Get Data) is better when the files sit in a folder, arrive in different column orders, need cleaning steps or are very large. It appends by column name rather than position, but the result only changes when you refresh it. Many teams use both: Power Query to load and clean, VSTACK and formulas for the quick combined views on top.

Common mistakes

• Stacking the header rows. VSTACK(A1:C5,A7:C10) puts the second header in the middle of the data. Drop it with DROP(A7:C10,1).
• Columns in a different order. VSTACK matches columns by position, so an export with Amount before Customer silently mixes the columns. Reorder it with CHOOSECOLS first.
• Rows of zeros from fixed ranges. Empty rows in A2:C200 come back as 0. Use Excel Tables or wrap the stack in FILTER.
• #N/A in extra columns. A narrower range is padded with #N/A. Make all sources the same width or wrap the result in IFNA.
• SUMIFS on a VSTACK formula. SUMIFS(VSTACK(…),…) fails because SUMIFS needs ranges. Spill the stack once and use INDEX(F1#,0,3), or use SUM with FILTER.
• Cells in the way of the spill. Anything below or to the right of the formula blocks it with #SPILL!. Give the stack its own area or its own sheet.