Excel VSTACK Function

VSTACK combines two or more ranges or arrays into a single array by stacking them vertically, one on top of the next. It returns a spilled array, so you only enter the formula once and Excel fills the rest of the cells automatically. The gotcha that trips up almost everyone: if your source ranges have different widths, VSTACK pads the shorter rows to match, and by default that padding shows up as #N/A.

Available in: Excel 365 and Excel for the web. Not available in Excel 2021, Excel 2019, or earlier versions.

Syntax

=VSTACK(array1, [array2], ...)

Parameter	Required	Description
`array1`	Yes	The first range or array to stack. This becomes the top block of the result.
`array2, ...`	No	Additional ranges or arrays, added below the previous one in the order you list them. You can supply as many as you need.

⚠

When the arrays you stack don't have the same number of columns, VSTACK pads the narrower rows with #N/A so every row in the result has equal width. This happens silently, no error message, just #N/A sitting in your output. Wrap the formula in IFERROR if a clean result matters more than flagging the mismatch.

Basic Example

You track quarterly sales in three separate tables, one per region, each with the same three columns: Rep, Product, Units. To combine them into one list for a pivot table:

=VSTACK(east_sales, west_sales, central_sales)

// east_sales    = first block, rows 2 through 14
// west_sales    = appended directly below east_sales
// central_sales = appended below west_sales

VSTACK reads each named range in order and stacks the rows underneath one another. If east_sales has 13 rows and west_sales has 9, the combined array starts west_sales on row 14 of the output, no gaps, no renumbering needed. Enter this formula once in a single cell and the result spills down automatically.

How VSTACK Works

It stacks in the order you list the arguments

VSTACK doesn't sort or merge by key. It appends each array directly beneath the one before it, in argument order. If you want west_sales above east_sales, swap the order in the formula. There's no automatic alignment by column header, so mismatched column order between sources will just stack misaligned data.

Mismatched widths get padded with #N/A

If array1 has 4 columns and array2 has only 3, VSTACK adds a fourth column to array2's rows and fills it with #N/A. This is Excel forcing every row in the result to have the same column count. It's a deliberate design choice, not a bug, but it catches people because nothing warns you before the formula runs.

Blank cells in the source become zeros

If a source range has empty cells, VSTACK doesn't preserve the blank. It returns 0 in that position instead. This is different from the #N/A padding behavior above: padding happens when the shape doesn't match, zero-filling happens when a cell inside the range genuinely has nothing in it. A blank in your Units column becomes a visible 0 in the stacked result, which can quietly throw off a SUM or AVERAGE if you're not expecting it.

Number formats and data types don't survive the stack

Stack a date column against a text column and VSTACK doesn't complain, but it also doesn't preserve formatting. Dates frequently return as their underlying serial number (46000-something) rather than a formatted date, especially when the columns being combined don't share consistent formatting to begin with. Reformat the output column manually after stacking if dates or currency need to display correctly.

The formula spills from one cell

You enter =VSTACK(...) in exactly one cell, the top-left corner of where you want the result to land. Excel expands the array downward (and rightward, if column counts differ) into whatever empty cells are available. If something else occupies those cells, you'll get a #SPILL! error instead of a result.

Common Use Cases

Combining Excel Tables of variable size

Two Tables with the same columns, one for online orders and one for in-store orders, both growing as new rows get added.

=VSTACK(OnlineOrders, StoreOrders)   // returns combined order list, updates automatically as either Table grows

Because Excel Tables expand automatically when you add rows, this formula never needs to be touched again, even as both source Tables grow independently.

Consolidating identical ranges across multiple sheets

You have the same report layout on Jan, Feb, and Mar tabs and want one combined view.

=VSTACK(Jan!B2:E20, Feb!B2:E20, Mar!B2:E20)

For more than three or four sheets, listing each one explicitly gets unwieldy fast. If your sheets are laid out identically and named sequentially, restructuring the source data into Excel Tables or consolidating with Power Query is usually more maintainable than a VSTACK formula with a dozen sheet arguments.

Deduplicating values across several ranges

You want a single list of unique customer names pulled from three regional customer lists.

=UNIQUE(VSTACK(east_customers, west_customers, central_customers))

VSTACK builds the combined array first, then UNIQUE filters it down to distinct values. This is the standard pairing for merging lookup lists before deduping.

Stacking with a formula that removes blank rows

Combine two ranges of different row counts, then strip out any fully blank rows the padding introduced.

=LET(
  combined, VSTACK(range1, range2),
  FILTER(combined, INDEX(combined,,1)<>0)
)

LET runs VSTACK once and reuses the result in both the array and the condition, instead of recalculating it twice inside FILTER.

Handling Errors

VSTACK itself rarely throws an error unless your target cells are occupied, but the arrays you feed into it can carry errors that show up in the stacked output.

Common causes of errors in a VSTACK result:

#N/A from automatic padding when source arrays have different column counts
#DIV/0!, #VALUE!, or other errors that already existed in a source range, which pass straight through into the stacked result
#SPILL! when the destination cells aren't empty
#REF! when a named range or Table reference inside the formula has been deleted

Before reaching for IFERROR, know what it actually does here: wrapping the entire VSTACK call suppresses every error in the result, including legitimate data errors from your source ranges, not just the #N/A padding VSTACK generates on its own.

=IFERROR(VSTACK(range1, range2), "Check source data")

✓

IFERROR wrapping the entire VSTACK call catches every error in the array, but it replaces the whole result with a single fallback value, not just the individual error cells. If you only want to fix the padding gaps and leave real data errors visible, use IF with ISNA inside a MAP or wrap just the affected column instead of the whole stack.

Notes & Gotchas

Why does VSTACK return #N/A in some cells but not others?

This happens when your source arrays don't have matching column counts. VSTACK pads the narrower rows to match the widest array in the stack, and the padding value is #N/A by default. It only affects the extra columns added to the narrower rows, everything else stacks normally.

Why did my blank cells turn into zeros?

VSTACK converts empty cells in the source range into 0 in the output array. This is separate from the padding behavior. A common fix is nesting the whole formula inside SUBSTITUTE to replace "0" with "", but only do this if your actual data never legitimately contains a zero value, otherwise you'll erase real zeros along with the fake ones.

Does VSTACK work with tens of thousands of rows?

Yes, but calculation speed degrades as the combined array grows into the tens of thousands of rows, especially if VSTACK feeds into another volatile array function like FILTER or SORT downstream. For very large consolidations across many sheets or workbooks, Power Query typically handles the volume with less recalculation overhead than a live VSTACK formula.

What happens if a source range already contains an error like #DIV/0!?

VSTACK doesn't catch or suppress errors already present in the source data. It passes them straight through into the corresponding cell of the stacked array. That's different from the #N/A padding error, which VSTACK generates itself, and IFERROR wrapped around the whole formula will catch both types indiscriminately unless you scope it more narrowly.

Can VSTACK combine ranges of different data types, like a date column and a text column?

Yes, but formatting is lost in the process. A date column stacked next to or below a differently formatted range often returns as a raw serial number instead of a readable date, since VSTACK carries values, not cell formatting. Reapply number formatting to the result cells after the formula spills.

✓

Enter the VSTACK formula in the top-left cell of an empty block with enough room below and to the right. If that space isn't clear, you'll get #SPILL! instead of your combined data, and Excel won't tell you which cell is blocking it.

Related Functions

Function	Use this when...
`UNIQUE`	You want to remove duplicates from a combined array, often paired directly with VSTACK.
`FILTER`	You need to strip out blank or unwanted rows after stacking arrays of different sizes.