Practice free

VSTACK function

VSTACK appends arrays one under another and returns them as a single array, such as combining monthly tables into one list. Narrower arrays are padded with #N/A.

Example

Read it as a sentence: stack B1:C2 on top of B6:C7 to make one list, and show the top left value, which gives Item.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

array1 (required)
First array.
array2 (optional)
More arrays to append below.

Returns: The arrays stacked vertically.

One VSTACK formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=VSTACK({1,2},{3,4}) 1 Two rows
=VSTACK({1,2},{3}) 1 Uneven widths
=VSTACK(1,"a") 1 Scalars
=VSTACK(Items!B1:C2,Items!B6:C7) Item Ranges
=VSTACK(Data!F1,Data!F2) 0 Blank cell

The Items sheet these formulas read

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Narrower arrays are padded with #N/A.
  • Use DROP to remove repeated header rows when stacking tables.

Where it works

Excel
VSTACK arrived in Excel 2024. Older versions do not have it. 365, Excel for the web and Excel 2024; not in older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use. Sheets has VSTACK too; a {range1; range2} array literal also works.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .