VSTACK stacks arrays on top of each other, returning one taller array that contains the rows of the first array followed by the rows of each additional array. It is the fastest way to combine separate lists or tables into a single continuous range without copying and pasting.
=VSTACK(array1, [array2], …)What does VSTACK do?
VSTACK is a lookup and reference function that joins two or more arrays vertically. You give it a list of ranges or arrays, and it returns a single array where the first range sits at the top, the second range sits directly beneath it, and so on. The result spills into the cells below the formula, so one formula can replace a manual copy-and-paste job.
This matters because office work is full of lists that live in separate places: monthly sales files, regional customer lists, or tables from different departments. Combining them by hand is slow and easy to get wrong when new rows are added. VSTACK keeps the combined list live, so when a source range changes, the stacked result updates automatically.
A typical situation is a quarterly report where January, February, and March sales are kept in three separate blocks on one sheet. Instead of pasting them into a fourth block, you point VSTACK at the three ranges and get one clean list ready for sorting, filtering, or a pivot table.
When to use VSTACK
- Combining monthly or regional tables into one master list
- Appending new rows from a second sheet to an existing table
- Building a single list from several department exports
- Preparing data for sorting, filtering, or a pivot table
- Merging headers and data blocks into one continuous range
- Creating a dynamic combined list that updates when sources change
VSTACK syntax
=VSTACK(array1, [array2], …)| Argument | Required? | What to enter |
|---|---|---|
array1 | Required | The first range or array to place at the top of the result. Example: A2:B4. |
array2 | Optional | The next range or array to place directly beneath array1. You can add more arrays after it. Example: D2:E4. |
What it returns: A single spilled array in which the rows of each array appear in the order you supplied them, one block on top of the next. The result has as many columns as the widest array, and shorter arrays are padded with #N/A in the extra columns.
VSTACK examples
1 Stack two short lists into one column
The goal is to combine two separate name lists into one continuous list. The formula =VSTACK(A2:A4,D2:D4) takes the first range A2:A4 and places it at the top, then places D2:D4 directly beneath it. It returns a single column of six names in the order North, South, East, West, Central, Coastal. The result spills down from the target cell, so you only enter the formula once.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | List 1 | List 2 | |||||
| 2 | North | West | North | ||||
| 3 | South | Central | |||||
| 4 | East | Coastal | |||||
| 5 |
2 Stack two two-column tables
Here two small tables with the same two columns are combined into one table. The formula =VSTACK(A2:B4,D2:E4) stacks the first table on top of the second. Because both ranges have two columns, the result is a six-row, two-column array. The values appear in the order of the source rows, with the first table first.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Units | Product | Units | ||||
| 2 | Pen | 10 | Ruler | 5 | Pen | |||
| 3 | Pencil | 20 | Marker | 8 | ||||
| 4 | Eraser | 15 | Tape | 12 | ||||
| 5 |
3 Combine three monthly sales blocks
Three monthly sales blocks sit side by side, each with a month label and an amount. The formula =VSTACK(A2:B3,D2:E3,G2:H3) stacks January, then February, then March into one six-row list. Each block contributes its two rows in order. The result is ready to be sorted or summarised without any copying.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Sales | Month | Sales | Month | Sales | ||||
| 2 | Jan | 1200 | Feb | 1100 | Mar | 1400 | Jan | |||
| 3 | Jan | 1350 | Feb | 1250 | Mar | 1500 | ||||
| 4 |
4 Add a header row above stacked data
Sometimes the source tables have no headers, but the combined result needs one. The formula =VSTACK(A1:B1,A3:B5,D3:E5) puts the header row A1:B1 at the top, then stacks the two data blocks beneath it. The result has one header row and six data rows. This keeps the final list self-describing and ready for a table or pivot.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | ||||||
| 2 | Item | |||||||
| 3 | Chair | 4 | Shelf | 3 | ||||
| 4 | Desk | 2 | Stool | 5 | ||||
| 5 | Lamp | 6 | Bin | 7 | ||||
| 6 |
5 Stack a name column and a date column from different tables
This example combines a customer list and an order list that share the same two-column shape. The formula =VSTACK(A2:B4,D2:E4) stacks the customer block on top of the order block. The first column holds names and the second holds dates, so the result is a single two-column array of six rows. It is useful when you want one combined log of people and dates.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Date | Customer | Date | ||||
| 2 | Ava | 2024-01-10 | Dan | 2024-02-01 | Ava | |||
| 3 | Ben | 2024-01-12 | Ella | 2024-02-03 | ||||
| 4 | Cara | 2024-01-15 | Finn | 2024-02-07 | ||||
| 5 |
Common VSTACK errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One of the arguments is not a valid range or array, such as a text string that is not a reference. | Check that every argument points to a real range or array, for example A2:B4, not "A2:B4". |
| #N/A | The arrays have different widths, so shorter arrays are padded with #N/A in the extra columns. | Make all arrays the same number of columns, or wrap the result in IFERROR if the padding is acceptable. |
| #SPILL! | The spilled result would overlap existing data or run off the edge of the sheet. | Clear the cells below and to the right of the target, or move the formula to an empty area. |
| #NAME? | The function name is misspelled or the Excel version does not support VSTACK. | Type =VSTACK( and let Excel suggest it; if it is missing, update to Microsoft 365 or Excel 2021 or later. |
| #REF! | A referenced range was deleted or moved so the reference is no longer valid. | Edit the formula and reselect the intended ranges. |
Tips for using VSTACK
- Keep source ranges the same width to avoid #N/A padding in the result.
- Use
VSTACKwith full-column references only when needed; they can make the result very large. - Combine
VSTACKwithSORTorFILTERto clean up the stacked list in one formula. - Place the formula in an empty area so the spilled result has room to expand.
- If you need a header row, include it as the first argument rather than typing it separately.
- Remember that the result is dynamic: adding rows to a source range updates the stacked output.
Frequently asked questions
What does VSTACK do in Excel?
VSTACK joins two or more arrays vertically, placing the first array at the top and each later array directly beneath it, and returns one combined spilled array.
Does VSTACK work in Excel 2019?
No. VSTACK is available in Microsoft 365 and Excel 2021 and later. Older versions return #NAME?.
Why does my VSTACK result show #N/A?
The arrays have different numbers of columns, so Excel pads the narrower arrays with #N/A. Make all arrays the same width to remove the padding.
Can VSTACK combine data from different sheets?
Yes. Each argument can be a range on another sheet, such as Sheet2!A2:B10, as long as the ranges are valid.
How is VSTACK different from HSTACK?
VSTACK stacks arrays on top of each other (vertically), while HSTACK places them side by side (horizontally).
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. VSTACK reference