VSTACK Function

Stacks arrays on top of each other into one taller array.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2024

Returns

A spilled array

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

Overview

VSTACK appends ranges one below another. It's the easiest way to combine lists — for example, the same table from several monthly sheets.

If the arrays have different widths, the narrower ones are padded with #N/A.

Syntax

=VSTACK(array1, [array2], …)

Arguments

ArgumentRequired?What it does
array1YesThe first array or range.
array2, …OptionalMore arrays to add below.

Examples

Example 1 — Combining two lists

Stack the East team above the West team.

D1fx=VSTACK(A1:A2, B1:B2)
ABCD
1AnaCaraAna
2BenDevBen
3Cara
4Dev
=VSTACK(A1:A2, B1:B2) Explain

Example 2 — Stacking sheets with one header

Combine three monthly sheets, keeping only the first header (the later ranges start at row 2).

=VSTACK(Jan!A1:C50, Feb!A2:C50, Mar!A2:C50) Explain

A 3D reference works too when sheets sit side by side: =VSTACK(Jan:Mar!A2:C50).

Wrap the result in FILTER(…, CHOOSECOLS(…,1)<>"") to remove empty rows picked up from oversized ranges.

Common mistakes

  • #N/A in the result. The arrays have different widths. Use IFNA(…, "") or line the columns up first.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.