Cellwise Start free

Dynamic arrays function

Excel VSTACK function

VSTACK stacks ranges, arrays or values one under another and returns a single spilled list. Use it to combine monthly lists, data from several sheets, or a table with a header and a totals row.

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

Arguments

array1
The first range, array or value. It goes at the top.
array2
Optional. More pieces, each placed below the one before.

VSTACK examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1ItemQtyItemQty
2Paper12Paper9
3Toner3Staples15
4Pens40Toner5
5Folders20
  1. =VSTACK(A2:B4,D2:E5)

    Result: spills 7 rows × 2 columns

    Paper12
    Toner3
    Pens40
    Paper9
    Staples15
    Toner5
    Folders20

    The January list, then the February list: seven rows in one array.

  2. =VSTACK(A1:B1,A2:B4,D2:E5)

    Result: spills 8 rows × 2 columns

    ItemQty
    Paper12
    Toner3
    Pens40
    Paper9
    Staples15
    Toner5
    Folders20

    Start with one header row, then both lists, so the result reads like a table.

  3. =UNIQUE(VSTACK(A2:A4,D2:D5))

    Result: spills 5 rows × 1 column

    Paper
    Toner
    Pens
    Staples
    Folders

    Combine the item columns and drop repeats: Paper, Toner, Pens, Staples, Folders.

  4. =VSTACK(A2:B4,HSTACK("Total",SUM(B2:B4)))

    Result: spills 4 rows × 2 columns

    Paper12
    Toner3
    Pens40
    Total55

    Adds a totals row under the list. HSTACK builds the row, VSTACK puts it at the bottom.

How to use VSTACK

  1. Click the top-left cell of an empty area.
  2. Type =VSTACK( and select the first range.
  3. Add a comma and the next range, as many as you need.
  4. Close the bracket and press Enter.

Common VSTACK mistakes

Blank rows become zeros

Empty cells in a stacked range come back as 0. Select only the filled rows, or filter them out: =FILTER(VSTACK(A2:B20,D2:E20),VSTACK(A2:A20,D2:D20)<>"").

Pieces of different widths

A narrower piece is padded with #N/A on the right. Stack ranges with the same number of columns, or wrap it in IFERROR(...,"").

Headers stacked twice

Select the data without headers in each range and add one header row as the first argument.

Using it in older Excel

VSTACK needs Microsoft 365, Excel 2024 or Excel for the web. Older versions show #NAME?.

VSTACK FAQ

How do I combine two lists into one in Excel?

Use =VSTACK(A2:A4,D2:D5). Wrap it in UNIQUE to remove duplicates or SORT to order it.

How do I combine data from several sheets?

Give VSTACK one range per sheet: =VSTACK(Jan!A2:C50,Feb!A2:C50,Mar!A2:C50). Wrap it in FILTER to drop the empty rows.

Why does VSTACK return 0 or #N/A?

0 comes from empty cells in the ranges. #N/A fills the gaps when one range has fewer columns than another.

Related functions

Updated