VSTACK

Stack two or more ranges on top of each other to build one taller array in a single formula.

Lookup & Reference
|
Excel 2021+
|
Google Sheets Supported

Spreadsheet editor

Syntax

=VSTACK(array1, [array2], ...) Returns: Array

Arguments

Argument Required Description
array1 Yes The first range or array to stack. This forms the top block of the combined result.
array2, ... No Extra ranges or arrays to append below array1, in the order you list them. You can keep adding more, each one stacking under the last.

About

VSTACK takes several ranges or arrays and appends them one below the other, returning a single combined array that spills into the cells under your formula. Think of it as a quick way to pile separate blocks of data into one continuous list without copying and pasting. List the ranges in the order you want them stacked, and VSTACK lines them up top to bottom.

Use it when your data lives in more than one place and you want it together: monthly sheets you need in one table, a header row you want to sit above a result, or several team lists you want merged for sorting. Because the output is a live array, it updates on its own whenever the source ranges change, so you never have to rebuild the combined list by hand. Pair it with SORT or FILTER to clean up the stacked result, or with UNIQUE to strip out duplicates after merging.

VSTACK works in Microsoft 365, Excel for the web, and Excel 2021 and later. If your ranges have different widths, VSTACK matches the widest one and fills the extra cells with #N/A. To join ranges side by side instead of top to bottom, use its companion HSTACK, and to pull specific columns back out of a stacked array, reach for CHOOSECOLS or TAKE.

Examples

Watch #N/A appear from mismatched widths

See what happens when you stack a two-column range on top of a one-column range. VSTACK matches the widest block and pads the gaps with #N/A. Widen the narrow range to clear the errors.

Spreadsheet editor

Merge two months, then sort the result

Pile the January and February tables into one array and sort the whole thing by sales from high to low. Change a sales figure and watch the combined ranking update on its own.

Spreadsheet editor

Stack two lists, then drop duplicates

Combine two sign-up lists into one column and pass the result straight to UNIQUE for a clean, duplicate-free set. Edit either list and see the merged result rebuild itself.

Spreadsheet editor

Watch out for

⚠

#N/A from mismatched widths

When the ranges you stack have different numbers of columns, VSTACK matches the widest one and pads the gaps with #N/A.

→ Make sure every range has the same number of columns, or wrap the formula in IFERROR to swap the #N/A padding for a blank.

⚠

Spill range is blocked

VSTACK needs empty cells below the formula to fill. If anything is in the way, you get a #SPILL! error instead of the stacked array.

→ Clear the cells under and beside the formula so the combined result has room to spill.

⚠

Not available in older Excel

VSTACK only exists in Microsoft 365 and Excel 2021 and later, so it returns a #NAME? error in Excel 2019 and earlier.

→ On older versions, copy each range below the last by hand, or rebuild the list with helper columns.

⚠

Wrong stacking order

VSTACK adds ranges in the exact order you list them, so a header or summary block can end up in the wrong spot.

→ List the range you want on top first, then each block below it in the order it should appear.

Tips & notes

VSTACK stacks ranges top to bottom; its companion HSTACK joins them left to right. The result rows equal the total rows of every range added together, and the width matches the widest range. Because the output is a live array, leave room below the formula and let it spill on its own.

Common questions

What is the difference between VSTACK and HSTACK?

VSTACK stacks ranges vertically, one below the other, to make a taller array. HSTACK joins ranges horizontally, side by side, to make a wider one.

Why does VSTACK return #N/A in some cells?

That happens when the stacked ranges have different widths. VSTACK matches the widest range and fills the missing cells with #N/A. Give every range the same number of columns to avoid it.

Can I sort or filter the result of VSTACK?

Yes. Wrap VSTACK inside SORT, FILTER, or UNIQUE to clean up the combined array in the same formula.

Does VSTACK work in Excel 2019 or Google Sheets?

It needs Microsoft 365 or Excel 2021 and later, so it is not in Excel 2019. Google Sheets supports VSTACK.