ID EN
…
#Dynamic array

VSTACK

Excel Functions

Combine ranges or arrays vertically

Syntax

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

Arguments

Parameter Description
array1 The first array or range to combine.
array2 [optional] The second array or range to combine.

Return Value

A single combined range or array

Details

The Excel VSTACK function combines arrays vertically into a single array. Each subsequent array is appended to the bottom of the previous array. The result from VSTACK is a single array that spills onto the worksheet into multiple cells. VSTACK works equally well for ranges on a worksheet or in-memory arrays created by a formula. The output from VSTACK is fully dynamic. If data in the given arrays changes, the result from VSTACK will immediately update. VSTACK works well with Excel Tables, as seen in the worksheet above, since Excel Tables automatically expand when new data is added. Use VSTACK to combine ranges vertically and HSTACK to combine ranges horizontally. VSTACK stacks ranges or arrays vertically. In the example below, the range B3:B5 is combined with the range B8:B9. Each subseq

Examples

Basic usage

VSTACK stacks ranges or arrays vertically. In the example below, the range B3:B5 is combined with the range B8:B9. Each subsequent range/array is appe

EXCEL 
=VSTACK(B3:B5,B8:B9)
Range with array

VSTACK can work interchangeably with both arrays and ranges. In the worksheet below, we combine the array constant {"Color","Qty"} with the range B3:C

EXCEL 
=VSTACK({"Color","Qty"},B3:C7)
Arrays of different size

When VSTACK is used with arrays of different size, the smaller array will be expanded to match the size of the larger array. In other words, the small

EXCEL 
=VSTACK(B5:C8,B11:B13)
Arrays of different size

By default, the cells used for padding will display the #N/A error. One option for trapping these errors is to use the IFERROR function. The formula i

EXCEL 
=IFERROR(VSTACK(B5:C8,B11:B13),"")

See Also

HSTACK TRIMRANGE