VSTACK and HSTACK

Combining data from multiple ranges has always been one of the more awkward tasks in Excel. The traditional approach — copy, paste, repeat — produces a static merged table that breaks the moment source data changes. VSTACK and HSTACK are the dynamic array solution. VSTACK stacks ranges on top of each other into a single vertical table. HSTACK places them side by side horizontally. Both are live formulas, so when source data grows or changes, the combined output updates automatically. These two functions are particularly powerful for consolidating data that arrives in monthly sheets, regional files, or separate named tables.

VSTACK — Stack Ranges Vertically

VSTACK takes two or more arrays and stacks them top-to-bottom, returning a single combined range. Each array becomes a block of rows in the output. All arrays must have the same number of columns — or VSTACK fills the gap with #N/A for any short rows.

=VSTACK(array1, array2, [array3], ...)

  array1, array2, ...  →  Ranges or arrays to stack top-to-bottom
                          All should have the same number of columns

Combine Q1 and Q2 sales data:
=VSTACK(Q1Data, Q2Data)

Stack three named tables:
=VSTACK(NorthSales, EastSales, SouthSales)

Include headers from the first table only:
=VSTACK(A1:D1, Q1Data, Q2Data)    (header row first, then data)

HSTACK — Stack Ranges Horizontally

HSTACK places arrays side by side, left to right, into a single wider table. All arrays must have the same number of rows — or HSTACK fills missing cells with #N/A. Use it to join related columns that live in separate ranges, add calculated columns alongside existing data, or assemble a report table from separate building blocks.

=HSTACK(array1, array2, [array3], ...)

  array1, array2, ...  →  Ranges or arrays to place left-to-right
                          All should have the same number of rows

Join names with their calculated bonus column:
=HSTACK(A2:B10, D2:D10)          (cols A, B, then D — skipping C)

Assemble a report from three separate column groups:
=HSTACK(NamesDept, SalesData, TargetData)

Add a rank column alongside a sorted table:
=HSTACK(SEQUENCE(10), SORT(A2:C11, 3, -1))

Adding a Row Number or Rank Column

A very common use of HSTACK is prepending a dynamic rank or row number column to a sorted table. SEQUENCE generates sequential numbers, and HSTACK places them as the first column of the output — no helper column required, and the numbers always stay correct because they come from a formula.

Leaderboard — rank numbers alongside sorted sales data:
=HSTACK(SEQUENCE(ROWS(A2:C6)), SORT(A2:C6, 3, -1))

How it works:
  SORT(A2:C6, 3, -1)          →  table sorted by sales descending
  ROWS(A2:C6)                  →  counts how many rows (5 here)
  SEQUENCE(5)                  →  {1;2;3;4;5} — one number per row
  HSTACK(...)                  →  rank column on the left, sorted data on the right

Dynamic — if rows are added to A2:C6 the rank column adjusts automatically

Handling Unequal Column Counts — #N/A Padding

When ranges passed to VSTACK have different numbers of columns, Excel pads the shorter rows with #N/A values. The same happens with HSTACK when ranges have different numbers of rows. You can replace those padding errors with something more meaningful by wrapping the result in IFERROR.

Table A has 3 columns, Table B has 4 columns — VSTACK pads Table A:
=VSTACK(TableA, TableB)   →  TableA rows get #N/A in the 4th column

Clean up padding errors with IFERROR:
=IFERROR(VSTACK(TableA, TableB), "")    →  blank instead of #N/A
=IFERROR(VSTACK(TableA, TableB), 0)     →  zero instead of #N/A

Same fix for HSTACK when row counts differ:
=IFERROR(HSTACK(ColGroup1, ColGroup2), "")

In practice, if you find yourself needing IFERROR around VSTACK regularly, it is a sign that the source tables have inconsistent structures. Standardising column counts across source sheets before stacking makes downstream formulas cleaner and avoids the padding issue entirely.

Combining VSTACK With Other Functions

Because VSTACK returns a standard array, you can pipe it into SORT, FILTER, UNIQUE, and any other function that accepts a range. This makes it the foundation of multi-source consolidation pipelines that stay fully live.

Stack all quarterly data, then sort by sales descending:
=SORT(VSTACK(Q1Data, Q2Data, Q3Data, Q4Data), 3, -1)

Stack, then filter to North region only:
=FILTER(VSTACK(Q1Data, Q2Data), VSTACK(Q1Reg, Q2Reg)="North")

Stack and deduplicate (remove rows that appear in both tables):
=UNIQUE(VSTACK(TableA, TableB))

Count total rows across all stacked sources:
=ROWS(VSTACK(Q1Data, Q2Data, Q3Data))

Full pipeline — all quarters, North only, sorted by sales:
=SORT(
  FILTER(
    VSTACK(Q1Data, Q2Data, Q3Data, Q4Data),
    VSTACK(Q1Reg,  Q2Reg,  Q3Reg,  Q4Reg) = "North"
  ),
  3, -1
)

VSTACK vs HSTACK — When to Use Each

Next up — the final lesson of Section III — HSTACK's closest companion in data assembly work, wrapping up the Advanced Functions section before we move into Power Query.