Combining data with VSTACK, HSTACK and other dynamic arrays
By the Analistable team · Updated · 2 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
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.
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)).