Analistable

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

Dynamic array functions for combining data
FunctionDoesExample
VSTACKStacks arrays vertically=VSTACK(Jan!A2:C50, Feb!A2:C50)
HSTACKPlaces arrays side by side=HSTACK(A2:A50, D2:D50)
FILTERKeeps rows that meet a condition=FILTER(data, CHOOSECOLS(data,1)<>"")
UNIQUERemoves duplicate rows=UNIQUE(VSTACK(A2:A50, B2:B50))
SORT / SORTBYOrders the result=SORT(data, 3, -1)
CHOOSECOLSPicks and reorders columns=CHOOSECOLS(data, 1, 3)
EXPANDPads 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)
  )
)
Result
MonthOrderProductAmount
Jan1001Desk420
Jan1002Lamp65
Jan1003Chair180
Feb1004Desk420
Feb1005Lamp65

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 use IF(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.

Result
RegionRepSales
NorthAmira2400
WestChloe1850
NorthBen1320
SouthAmira1100

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

Where each function is available
FunctionMicrosoft 365Excel 2021Excel 2019 and older
FILTER, SORT, UNIQUEYesYesNo
LETYesYesNo
VSTACK, HSTACK, TOCOL, CHOOSECOLSYesNoNo
GROUPBY, PIVOTBYNewer buildsNoNo

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

Dynamic array errors when combining
SymptomCauseFix
#SPILL!Cells in the spill area are not emptyClear them or move the formula
#N/A in some columnsHSTACK or VSTACK of ranges with different sizesWrap in IFNA(..., "") or match the sizes
#CALC! from FILTERNo rows meet the conditionAdd the third argument: FILTER(data, test, "None")
Formula is slowWhole-column references such as A:CUse 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:C100 when 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)).

Related guides