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
=VSTACK(array1, [array2], ...)=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
| Argument | Required | Description |
|---|---|---|
array1 | Required | The first range, which goes at the top of the combined list. |
array2 | Optional | The 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.
=VSTACK(A2:B4,D2:E5)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item (Camden) | Amount | Item (Leeds) | Amount | Item | Amount | ||
| 2 | Notebook | 4,200 | Pen | 2,900 | Notebook | 4,200 | ||
| 3 | Pen | 3,650 | Binder | 1,850 | Pen | 3,650 | ||
| 4 | Binder | 2,100 | Notebook | 3,300 | Binder | 2,100 | ||
| 5 | Eraser | 640 | Pen | 2,900 | ||||
| 6 | Binder | 1,850 | ||||||
| 7 | Notebook | 3,300 | ||||||
| 8 | Eraser | 640 |
- 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.
=UNIQUE(VSTACK(A2:A4,C2:C5))| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | September Customers | October Customers | All Customers | ||
| 2 | North Market | Atlas Ltd | North Market | ||
| 3 | Atlas Ltd | Sea Foods | Atlas Ltd | ||
| 4 | West Build | North Market | West Build | ||
| 5 | Blue Electric | Sea Foods | |||
| 6 | Blue 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
Combine the sales lists of the Camden and Leeds branches into one list, one below the other
Convert to tableBring 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
- HSTACKYATAYYIĞ · Stacks arrays horizontally.
- UNIQUEBENZERSİZ · List of distinct values (count them with COUNTA). TRUE as the third argument keeps values that occur once.
- SORTSIRALA · Sorts a range (1 ascending, -1 descending).
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- TAKEAL · Takes rows or columns from the start or end of an array.
- CHOOSECOLSSÜTUNSEÇ · Returns chosen columns of an array.