Combining data with VSTACK, HSTACK and other dynamic arrays
By the Analistable team · Updated · 5 min read
In Microsoft 365 and Excel 2024, VSTACK stacks ranges on top of each other and HSTACK places them side by side. Combine them with FILTER to drop blank rows, UNIQUE to remove duplicates and SORT to order the result — all in one formula that updates live. VSTACK also accepts 3D references such as Jan:Dec!A2:D500.
Part of our guide: How to combine Excel sheets into one
Combine your sheets now
Files stay in your browser. Google access is read-only.
The functions
| Function | Does | Example |
|---|---|---|
| VSTACK | Stacks arrays vertically | =VSTACK(Jan!A2:C50, Feb!A2:C50) |
| HSTACK | Places arrays side by side | =HSTACK(A2:A50, D2:D50) |
| FILTER | Keeps rows that meet a condition | =FILTER(data, CHOOSECOLS(data,1)<>"") |
| UNIQUE | Removes duplicate rows | =UNIQUE(VSTACK(A2:A50, B2:B50)) |
| SORT / SORTBY | Orders the result | =SORT(data, 3, -1) |
| CHOOSECOLS | Picks and reorders columns | =CHOOSECOLS(data, 1, 3) |
| EXPAND | Pads an array to a size | =EXPAND("Jan", 10, 1, "Jan") |
Available in Microsoft 365 (Windows, Mac, web) and Excel 2024. Google Sheets has VSTACK, HSTACK, FILTER, UNIQUE and SORT too.
Stack every month and drop blank rows
=LET(
data, VSTACK(Jan:Dec!A2:D500),
FILTER(data, CHOOSECOLS(data, 1) <> "")
)Jan:Dec!A2:D500 is a 3D reference covering every sheet between the Jan and Dec tabs. Using generous ranges such as row 500 is safe because FILTER removes rows whose first column is empty.
Add a column with each sheet's name
A stacked list is more useful when every row says where it came from. Build each block with HSTACK and EXPAND:
=LET(
jan, Jan!A2:C4, feb, Feb!A2:C3,
VSTACK(
HSTACK(EXPAND("Jan", ROWS(jan), 1, "Jan"), jan),
HSTACK(EXPAND("Feb", ROWS(feb), 1, "Feb"), feb)
)
)| Month | Order | Product | Amount |
|---|---|---|---|
| Jan | 1001 | Desk | 420 |
| Jan | 1002 | Lamp | 65 |
| Jan | 1003 | Chair | 180 |
| Feb | 1004 | Desk | 420 |
| Feb | 1005 | Lamp | 65 |
Combine columns from different sheets
HSTACK joins columns by position: =HSTACK(Names!A2:A50, Scores!B2:B50) only lines up correctly if both lists are in the same order. If they aren't, match by key with XLOOKUP instead: =HSTACK(Names!A2:A50, XLOOKUP(Names!A2:A50, Scores!A2:A50, Scores!B2:B50, "")).
Gotchas
- Empty cells become 0. References to blank cells return 0 in a dynamic array. Append
&""to show blanks (it converts numbers to text), or useIF(ISBLANK(x), "", x). - #SPILL! means something is in the way of the result. Clear the cells below and to the right.
- Different widths are padded with #N/A. Wrap in
IFNA(..., ""). - Positional matching. VSTACK doesn't look at headers; reorder columns with CHOOSECOLS so they match.
For a combined table you can refresh, filter and load to a pivot, Power Query is often a better fit. See combining Excel sheets into one.
Worked example: stack, filter and sort in one formula
Three sheets, Jan, Feb and Mar, hold Region, Rep and Sales in A:C. To list every row with sales above 1,000, largest first:
=LET(
data, VSTACK(Jan!A2:C100, Feb!A2:C100, Mar!A2:C100),
kept, FILTER(data, CHOOSECOLS(data, 3) > 1000),
SORT(kept, 3, -1)
)LET names the stacked data once so it isn't recalculated three times. FILTER on column 3 removes blank rows too, because an empty Sales cell is not greater than 1,000. SORT with 3, -1 orders by Sales descending.
| Region | Rep | Sales |
|---|---|---|
| North | Amira | 2400 |
| West | Chloe | 1850 |
| North | Ben | 1320 |
| South | Amira | 1100 |
Summarise the stacked data
Wrap the stack in GROUPBY (newer Microsoft 365 builds) to total by a column without a pivot table: =GROUPBY(CHOOSECOLS(data,1), CHOOSECOLS(data,3), SUM) inside the same LET. If GROUPBY isn't in your build, list the regions with UNIQUE and total them with SUMIFS pointing at the stacked range through a spill reference such as E2#.
Spill references
If the VSTACK formula sits in E2, refer to its whole result as E2#. =ROWS(E2#) counts the combined rows, and =SUM(INDEX(E2#, 0, 3)) adds its third column. Spill references grow and shrink with the result, so downstream formulas never need new ranges.
Versions and alternatives
| Function | Microsoft 365 | Excel 2021 | Excel 2019 and older |
|---|---|---|---|
| FILTER, SORT, UNIQUE | Yes | Yes | No |
| LET | Yes | Yes | No |
| VSTACK, HSTACK, TOCOL, CHOOSECOLS | Yes | No | No |
| GROUPBY, PIVOTBY | Newer builds | No | No |
Excel 2024 includes VSTACK, HSTACK, TOCOL and CHOOSECOLS. If a colleague opens the file in an older version, these formulas show as errors or as static values. For a workbook shared widely, Power Query Append is the more portable choice; see pulling data from multiple sheets into one.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| #SPILL! | Cells in the spill area are not empty | Clear them or move the formula |
| #N/A in some columns | HSTACK or VSTACK of ranges with different sizes | Wrap in IFNA(..., "") or match the sizes |
| #CALC! from FILTER | No rows meet the condition | Add the third argument: FILTER(data, test, "None") |
| Formula is slow | Whole-column references such as A:C | Use tables or bounded ranges |
Match two sheets with dynamic arrays
Combining isn't always stacking. To bring a Price column from a Products sheet next to an Orders list in one formula, XLOOKUP accepts a whole column of lookup values and spills the answers:
=HSTACK(Orders!A2:C50, XLOOKUP(Orders!B2:B50, Products!A2:A200, Products!C2:C200, "Not found"))The result is the three Orders columns followed by the matched price, with "Not found" where a product code has no match. Filter that value to list the gaps. For a wider comparison of lookup functions, see VLOOKUP vs XLOOKUP vs INDEX/MATCH.
Common mistakes
- Including the header row in every range, so header text appears between each sheet's rows.
- Using
Jan:Mar!A2:C100when the sheets aren't adjacent in tab order — the 3D reference takes every sheet between them. - Typing over part of a spilled result; Excel then shows #SPILL! for the whole formula.
- Sorting the spilled range with the Sort button, which isn't allowed. Sort inside the formula with SORT or SORTBY.
Turning the result into fixed values
A spilled result recalculates whenever a source sheet changes. To send a snapshot, select the spill range, copy, and use Paste Special → Values on a new sheet. Keep the live formula in your own copy so next month's stack is one refresh away.
Frequently asked questions
- Does VSTACK work across sheets?
- Yes. List each range, as in =VSTACK(Jan!A2:C50, Feb!A2:C50), or use a 3D reference such as =VSTACK(Jan:Dec!A2:C50).
- Which Excel versions have VSTACK and HSTACK?
- Microsoft 365 on Windows, Mac and the web, and Excel 2024. They aren't in Excel 2021 or earlier.
- How do I remove duplicates from a VSTACK result?
- Wrap it in UNIQUE: =UNIQUE(VSTACK(range1, range2)).