Skip to content

Excel function · Lookup & reference

Excel VSTACK function

VSTACK appends the ranges you give it one below the other and returns a single array: two branches' sales lists, two months' customer lists. It is available only in Microsoft 365.

In Turkish Excel: DÜŞEYYIĞ

What it does

VSTACK takes as many ranges as you like; the rows of the first go at the top and the others follow in order. The result spills down from the formula cell, and every change in the source ranges shows up in the combined list.

If the ranges have different numbers of columns, the missing cells of the narrower one are filled with #N/A; wrap the result in IFERROR to hide them. You can make the combined list unique with UNIQUE, sort it with SORT or filter it with FILTER.

To join ranges side by side, use its partner HSTACK. Outside Microsoft 365, the lists are copied one under the other by hand.

Syntax

English Excel=VSTACK(array1, [array2], ...)
Turkish Excel=DÜŞEYYIĞ(dizi1; [dizi2]; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
array1RequiredThe first range, which goes at the top of the combined list.
array2OptionalThe range appended below it; add as many ranges as you need the same way.

Examples

1Combine the sales lists of two branches

Camden's rows are in A:B and Leeds's in D:E. The formula in G2 puts Camden's three rows first and Leeds's four rows below them.

G2fx=VSTACK(A2:B4,D2:E5)
Combine the sales lists of two branches: example table
ABCDEFGH
1Item (Camden)AmountItem (Leeds)AmountItemAmount
2Notebook4,200Pen2,900Notebook4,200
3Pen3,650Binder1,850Pen3,650
4Binder2,100Notebook3,300Binder2,100
5Eraser640Pen2,900
6Binder1,850
7Notebook3,300
8Eraser640
  • G2 (the result spills into G2:H8)=VSTACK(A2:B4,D2:E5)

The combined list spills into G2:H8: Notebook on the first row and Eraser (640) on the last. When an amount changes in either branch list, the combined list changes too.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Combine two months' customers without repeats

The September and October customer lists are stacked, then UNIQUE keeps customers who appear in both months only once.

E2fx=UNIQUE(VSTACK(A2:A4,C2:C5))
Combine two months' customers without repeats: example table
ABCDE
1September CustomersOctober CustomersAll Customers
2North MarketAtlas LtdNorth Market
3Atlas LtdSea FoodsAtlas Ltd
4West BuildNorth MarketWest Build
5Blue ElectricSea Foods
6Blue Electric
  • E2 (the result spills into E2:E6)=UNIQUE(VSTACK(A2:A4,C2:C5))

Atlas Ltd and North Market appear twice in the stacked list; once the repeats are removed, five customers remain, ending with Blue Electric.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: The ranges have different numbers of columns, so the missing cells of the narrower range are filled with #N/A.

Fix: Select ranges of the same width, or wrap the result in IFERROR(…,"").

#SPILL!

Cause: A cell in the spill area is not empty.

Fix: Clear the cells below and to the right of the formula.

#NAME?

Cause: The file was opened in a version of Excel other than Microsoft 365.

Fix: In those versions, combine the lists by hand.

Good to know

  • To put the header once at the top, pass the header row as the first range: =VSTACK(A1:B1,A2:B4,D2:E5).
  • If the source ranges contain blank rows, filter the combined result: =FILTER(VSTACK(A2:B20,D2:E20),VSTACK(A2:A20,D2:D20)<>"").

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Combine the sales lists of the Camden and Leeds branches into one list, one below the other

Convert to table

Bring both lists into the tool, as separate files or as two sheets of the same file, and type this. Nexell builds the combined list and shows it in the preview first; once you approve, you download it as .xlsx.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I stack two lists with a formula in Excel?

In Microsoft 365, use =VSTACK(A2:B4,D2:E5); the second list is appended below the first and the result updates as either list changes. Wrap it in UNIQUE to remove repeats.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free