VSTACK function

The VSTACK function in Excel is used to vertically stack multiple ranges or arrays into a single array. It places one array directly below another, effectively creating a column of values from multiple ranges.

Syntax

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

Parameters

• array1: The first array or range of cells to stack vertically.
• [array2] (optional): Additional arrays or ranges to stack below array1. You can include multiple arrays.

How It Works

The VSTACK function combines the specified ranges or arrays by placing each subsequent array below the previous one. It works similarly to concatenating multiple rows into one continuous column.

Examples

Example 1: Stacking Two Ranges

Suppose you have the following data in two ranges:

• Range A1:A3: John, Mary, Alex
• Range B1:B3: Anna, Bob, Eve

To stack these two ranges vertically, use:

=VSTACK(A1:A3, B1:B3)

This will return:

John
Mary
Alex
Anna
Bob
Eve

Example 2: Stacking Multiple Arrays

If you want to combine multiple arrays or ranges into one, you can use:

=VSTACK(A1:A3, B1:B3, C1:C3)

This will stack the ranges A1:A3, B1:B3, and C1:C3 vertically, showing the combined list of values from each column.

Key Points to Remember

• VSTACK is useful for combining ranges into a single array or list.
• The ranges do not need to be the same size; Excel will stack them and fill missing cells with #N/A if necessary.
• The function works well when you want to merge data from different rows or columns into a single list without modifying the original data.

Use Case Scenarios

• Data Consolidation: When you have data in different columns or tables and need to consolidate it into a single list.
• Organizing Data: Useful when arranging data from different ranges in a sequential order to perform further analysis.

The VSTACK function simplifies stacking ranges into one continuous vertical array.