🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel VSTACK & HSTACK Functions — Stack Ranges Into One Live Array

|

Excel VSTACK & HSTACK Functions — Stack Ranges Into One Live Array

TL;DR — VSTACK stacks ranges on top of each other (vertically); HSTACK sets them side by side (horizontally). Both merge several ranges or arrays into one result that spills and updates when the sources change. Syntax: =VSTACK(array1, [array2], …). The classic use is combining the same-shaped tables from several months or sheets into one live master without copy-paste. If the stacked ranges don't share width (VSTACK) or height (HSTACK), the gaps fill with #N/A. Excel 365 and 2021+.

=VSTACK(Jan, Feb, Mar)               ' three monthly tables, one live stack
=HSTACK(Names, Scores)               ' two columns set side by side

For years, "put January, February and March into one list" meant copy, paste, copy, paste — and redo it every month. VSTACK turns that into one formula that re-stacks the instant any source table changes. It's the dynamic-array answer to combining data, and with HSTACK it does the horizontal version too.

What you'll learn

  • VSTACK (vertical) vs HSTACK (horizontal) — which axis each one grows
  • Combining tables from several sheets into one live master, no copy-paste
  • Why mismatched widths/heights pad with #N/A, and how to avoid it
  • Dropping repeated headers with DROP so the stack stays clean
  • Querying the combined result with FILTER, SORT and UNIQUE

The mental model: copy-paste-to-the-bottom, but live

Pasting one table under another is a one-time action; do it again next month and last month's paste is stale. VSTACK describes the stack and recomputes it forever. Think of it as "append these ranges" as a formula: the result spills from the formula cell and grows as the sources grow.

' Manual: copy Feb, paste under Jan, copy Mar, paste under that... repeat monthly
' Live:
=VSTACK(JanTable, FebTable, MarTable)   ' re-stacks the moment any table changes

HSTACK is the same idea rotated 90°: instead of appending rows, it appends columns, lining ranges up left to right.

The rule that unlocks everything: match the axis or get #N/A

VSTACK stacks downward, so every range should have the same number of columns; HSTACK stacks rightward, so every range should have the same number of rows. When they don't match, Excel can't leave a hole empty — it fills the short side with #N/A:

=VSTACK(A2:C10, E2:F10)   ' 3 cols stacked on 2 cols -> a column of #N/A appears
=HSTACK(A2:A10, C2:C6)    ' 9 rows beside 5 rows -> 4 cells of #N/A at the bottom

The fix is to make the inputs the same shape, or wrap the result to clean up: =IFERROR(VSTACK(...), "") blanks the pad cells. But a column of #N/A is usually a signal you stacked the wrong ranges — check the widths first.

Drop the repeated headers

Real tables have header rows, and stacking three of them leaves a header buried in the middle of your data. The clean pattern pairs VSTACK with DROP to strip each table's header, then adds one header back:

' Each monthly table has a header row; keep only the first, drop the rest:
=VSTACK(JanTable, DROP(FebTable, 1), DROP(MarTable, 1))

DROP(range, 1) removes the top row. This is the idiom that makes multi-sheet consolidation actually usable — one header, all the data, fully live.

The judgment call: VSTACK or Power Query?

If you're copy-pasting a handful of same-shaped ranges that live in the same workbook and you want the result live, VSTACK is lighter than anything else — no query to refresh, it just recalculates. The honest limit is the same as for every dynamic-array function: for dozens of files, hundreds of thousands of rows, or messy column matching, Power Query is the right tool. For "stack these few tables and keep it current," VSTACK wins. Combine it with SORT, FILTER and TAKE to query the merged result in one formula.

How ExcelMaster helps

The real request is rarely just "stack these" — it's combine every region's tab into one sorted table, drop the repeated headers, and keep it live. ExcelMaster writes the VSTACK (or HSTACK), adds the DROP to strip headers, checks the widths match so you don't get a column of #N/A, and wraps it in SORT/FILTER if you want the merged view queried — all from a plain-English description.

Frequently asked questions

What's the difference between VSTACK and HSTACK?

VSTACK stacks ranges vertically, one under another (they should share column count). HSTACK stacks them horizontally, side by side (they should share row count). Both spill and update when the sources change.

How do I combine data from multiple sheets without Power Query?

Use =VSTACK(Sheet1!A2:C100, Sheet2!A2:C100, Sheet3!A2:C100). To avoid repeated headers, drop them: =VSTACK(First, DROP(Second,1), DROP(Third,1)). The result is live and re-stacks when any sheet changes.

Why does VSTACK return #N/A?

The stacked ranges don't have matching widths (VSTACK) or heights (HSTACK), so Excel pads the short side with #N/A. Make the inputs the same shape, or wrap in IFERROR to blank the pad cells.

Can I sort or filter a VSTACK result?

Yes — wrap it: =SORT(VSTACK(Jan, Feb, Mar), 2, -1) sorts the combined stack, and =FILTER(VSTACK(...), condition) queries it. The whole thing stays live.

Does VSTACK work in Excel 2016 or 2019?

No. VSTACK and HSTACK require Excel 365 or Excel 2021+. On older versions you copy-paste or use Power Query.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-17.

Related guides: Excel SEQUENCE · Excel TAKE & DROP · Excel SORT · Excel FILTER