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.
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.
| Formula | Excel shows | Case |
|---|---|---|
=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 .