VSTACK function
VSTACK stacks ranges on top of each other into one list, such as this month's orders under last month's or one region's reps under another's. Wrap the result in SORT, UNIQUE or FILTER to work with everything as a single table.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- One array with the inputs stacked top to bottom. Narrower inputs are padded with #N/A. It spills.
Syntax
=VSTACK(array1, [array2], ...)
| Argument | Required? | What it is |
|---|---|---|
array1 | Required | The first range or array. |
array2 | Optional | The next range to place underneath. Add as many as you need, separated by commas. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | =VSTACK(A2:B4,D2:E5) | Anaspills 7 × 2 | Both lists in one: East first, then West |
| J2 | =SORT(VSTACK(A2:B4,D2:E5),2,-1) | Leespills 7 × 2 | The combined list, sorted by sales |
| M2 | =UNIQUE(VSTACK(A2:A4,D2:D5)) | Anaspills 5 × 1 | Each rep once, even those in both regions |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| East rep | Sales | West rep | Sales | All reps | By sales | Distinct reps | ||||||
| Ana | $1,250 | Raj | $860 | Ana | $1,250 | Lee | $2,140 | Ana | ||||
| Lee | $2,140 | Kim | $1,980 | Lee | $2,140 | Kim | $1,980 | Lee | ||||
| Kim | $905 | Mo | $1,625 | Kim | $905 | Mo | $1,625 | Kim | ||||
| Ana | $540 | Raj | $860 | Ana | $1,250 | Raj | ||||||
| Kim | $1,980 | Kim | $905 | Mo | ||||||||
| Mo | $1,625 | Raj | $860 | |||||||||
| Ana | $540 | Ana | $540 |
Practice VSTACK
Graded instantly, then re-checked on changed data.
One leaderboard from two teams
G2, combine the East list (A2:B5) and the West list (D2:E6) into one table, sorted by sales, highest first.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A in some cells | The ranges have different widths, so the narrower one is padded with #N/A. Stack ranges of the same width, or wrap the result in IFERROR(…,""). |
| Header rows in the middle | Each range included its header. Stack only the data rows and put one header above the formula. |
| #NAME? | VSTACK needs Excel 2024 or Microsoft 365. |
Tips
- Stack the same range from two sheets: =VSTACK(Jan!A2:C20,Feb!A2:C20).
- Add a header row of your own: =VSTACK(A1:B1,SORT(A2:B20,2,-1)).
Questions
How do I combine two lists into one in Excel?
=VSTACK(A2:A10,C2:C10) places the second list under the first. Wrap it in UNIQUE to drop duplicates or SORT to order it.
What is the difference between VSTACK and HSTACK?
VSTACK puts ranges one under another; HSTACK puts them side by side.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.