Excel VSTACK function

Summary

The Excel VSTACK function is designed to combine arrays or ranges vertically into a single array. This function is particularly useful for consolidating data from multiple arrays or ranges into one continuous vertical layout, which can be essential for data analysis or presentation.
Syntax
				
					=VSTACK(array1, [array2], ...)
				
			
  • array1: The first array or range to combine
  • array2: [Optional] The second array or range to combine, and so on
Return value
A single combined array or range, extending vertically.

How to use

Use VSTACK to combine two or more arrays or ranges vertically. Each additional array or range specified in the function arguments is placed beneath the preceding one. The function dynamically adjusts to changes in the data of the combined arrays or ranges.

Examples

Simple VSTACK
Combining Two Vertical Ranges: Vertically stacking two ranges:
				
					=VSTACK(A2:A5, A7:A10)
				
			
This formula vertically combines the ranges A2:A5 and A7:A10 into one continuous vertical array.
VSTACK with Multiple Columns
Stacking Ranges with Multiple Columns Vertically: Combining multiple column ranges vertically:
				
					=VSTACK(A1:C3, D1:F3)
				
			
Here, VSTACK combines two 3-column ranges (A1:C3 and D1:F3) one on top of the other, forming a taller array.
VSTACK with Array Constants
Using VSTACK with Array Constants: Combining an array constant with a range:
				
					=VSTACK({"Header1", "Header2"}, B2:C4)
				
			
This formula vertically stacks an array constant of two headers with the range B2:C4.
VSTACK for Data Consolidation
Consolidating Multiple Data Sets Vertically: To consolidate data from various columns into a single column:
				
					=VSTACK(G2:G10, H2:H10, I2:I10)
				
			
This formula combines data from columns G, H, and I into a single column, aligning the data vertically.
VSTACK with Different Sizes
Handling Arrays of Different Column Counts: Combining arrays of different column counts:
				
					=VSTACK(A2:B5, C2:D4)
				
			
In this example, VSTACK combines a larger array (A2:B5) with a smaller one (C2:D4). The smaller array will be padded with #N/A errors for unmatched columns.

Additional Notes

  • VSTACK is a dynamic function, and the output updates automatically if the source data changes.
  • When used with arrays of different sizes, the smaller array is expanded (or “padded”) with #N/A errors to match the size of the larger array.
  • VSTACK is useful for organizing and comparing data vertically and can be paired with functions like IFERROR to handle any error values resulting from array size mismatches.

Related Functions

Excel HSTACK function

The Excel HSTACK function combines arrays horizontally into one, streamlining data consolidation and comparison across sets.

Content Navigation