The Excel VSTACK function stacks two or more ranges or arrays vertically — one on top of the other — into a single spilled result. It replaces copy-paste consolidation: combine the regions, the months, or the sheets in one live formula, then feed the result straight into SORT, UNIQUE, or FILTER.
#N/A.
Syntax
| Argument | Description | |
|---|---|---|
array1 | Required | The first range or array to stack. |
array2, … | Optional | More ranges or arrays, appended in order below the previous one. Up to 254 in total. |
Available in: Excel for Microsoft 365, Excel 2024+, and Excel for the web. Older versions show #NAME?. The row counts of the arrays can differ freely; it’s the column counts that should match.
Stack two tables into one list
Two regional sales tables sit side by side — North in A1:B4, South in D1:E4. One formula combines their data rows into a single list:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Product | Sales | Product | Sales | |
| 2 | Laptops | $12,400 | Laptops | $9,800 | |
| 3 | Monitors | $8,100 | Tablets | $6,300 | |
| 4 | Keyboards | $2,950 | Monitors | $7,450 |
The combined result spills down from the formula cell:
| G | H | |
|---|---|---|
| 1 | Laptops | $12,400 |
| 2 | Monitors | $8,100 |
| 3 | Keyboards | $2,950 |
| 4 | Laptops | $9,800 |
| 5 | Tablets | $6,300 |
| 6 | Monitors | $7,450 |
Want the header row once on top? Just stack it first:
Try it: interactive VSTACK demo
Pick any two tables to stack — including a 1-column table, so you can see the #N/A padding for yourself.
The real power: combine sheets, then sort and de-duplicate
VSTACK accepts ranges from different sheets, including 3-D references across a run of identically laid-out tabs:
Because the output is a dynamic array, it nests cleanly inside other array functions. A sorted, de-duplicated master list from three sheets is one formula:
Trim the blanks: stacking fixed-size ranges often drags in empty rows. Wrap the stack in FILTER to keep only rows with data: =LET(s, VSTACK(Jan:Mar!A2:C20), FILTER(s, CHOOSECOLS(s,1)<>"")).
Errors & common pitfalls
#N/A — ragged widths. If one array has fewer columns than another, VSTACK pads the missing cells with #N/A. Supply equal-width arrays, or wrap the stack in IFERROR to blank the padding: =IFERROR(VSTACK(a, b), "").
#SPILL! — no room to land. The combined array needs empty cells below and beside the formula. Clear the blocking cells — the error tooltip points at them.
#NAME? — older Excel. VSTACK requires Excel for Microsoft 365 or Excel 2024+. In older versions the name isn’t recognized.
Pitfall: stacked headers. Stacking whole tables repeats each table’s header row in the middle of the data. Stack the header range once, then the data-only ranges.
Pitfall: the result is values only. VSTACK copies values, not formatting — currency formats, colors, and column widths don’t travel with the data.
Practice workbook
Frequently asked questions
Which Excel versions have VSTACK?
#NAME?.What happens when my arrays have different sizes?
#N/A. Supply equal widths, or wrap in IFERROR to blank the padding.Can VSTACK combine ranges from different sheets?
=VSTACK(Jan:Dec!A2:C20).How do I sort or remove duplicates from the stacked result?
=SORT(VSTACK(a, b)) or =UNIQUE(VSTACK(a, b)) — or both at once. The stack is a normal dynamic array and feeds any array function.What's the difference between VSTACK and HSTACK?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class