📎 This article includes 1 downloadable practice file ↓
In this article
VSTACK appends ranges one below another — combine monthly sheets or regional lists without copy-paste.
Syntax
=VSTACK(array1, [array2], ...)| Argument | What it means |
|---|---|
array1… |
Ranges or arrays to stack. |
Examples
Example 1
=VSTACK(North!A2:E100, South!A2:E100)One combined list.
Example 2
=VSTACK(Jan:Dec!A2:E100)3D reference: stacks the same range from every sheet between Jan and Dec.
Power combo
=LET(d, VSTACK(Jan:Dec!A2:E500), FILTER(d, CHOOSECOLS(d,1)<>""))Stack all months and drop the blank rows.
VSTACK in practice
Where you will use it
- Append monthly sheets into one list
- Combine tables
- Add a totals row
Worked example
| Case | Formula | Result |
|---|---|---|
| 1 | =ARRAYTOTEXT(CHOOSE(A2,VSTACK({"Apr",100},{"May",120}),VSTACK({1;2},{3}),VSTACK({"A","B"},{1}))) | Apr, 100, May, 120 |
| 2 | =ARRAYTOTEXT(CHOOSE(A3,VSTACK({"Apr",100},{"May",120}),VSTACK({1;2},{3}),VSTACK({"A","B"},{1}))) | 1, 2, 3 |
| 3 | =ARRAYTOTEXT(CHOOSE(A4,VSTACK({"Apr",100},{"May",120}),VSTACK({1;2},{3}),VSTACK({"A","B"},{1}))) | A, B, 1, #N/A |
Results calculated in Excel. Row 3 shows the #N/A padding you get when the arrays have different widths.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Different widths | Padded with #N/A; wrap in IFNA |
| Headers repeated | Use DROP to remove extra headers |
Related functions
HSTACK · FILTER · CHOOSECOLS
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.