Combine Ranges with VSTACK & HSTACK

Excel 365

To append ranges into one — stacking January, February, and March tables into a single list, or joining columns side by side — use VSTACK (vertically) and HSTACK (horizontally). One formula merges them, live.

Quick formula: to stack three monthly ranges into one list:

=VSTACK(Jan, Feb, Mar)

VSTACK piles the ranges on top of each other; HSTACK places them side by side. They update as the sources change.

Functions used (tap for the full reference guide):

VSTACK HSTACK

The example

Two short lists combined into one with VSTACK.

	A	C
1	List 1	Combined
2	Apple	Apple
3	Banana	Banana
4	List 2	Cherry
5	Cherry	Date
6	Date

The formula

The two lists, stacked into one column:

=VSTACK(A2:A3, A5:A6) // Apple, Banana, Cherry, Date

How it works

Stacking is exactly what it sounds like:

VSTACK(range1, range2, …) places each range below the previous one, producing one tall spilled array.
HSTACK instead places them side by side into one wide array.
The ranges can be different sizes; gaps are padded with #N/A (which you can wrap to hide).
Because it’s a formula, adding rows to a source range reflows the combined result automatically.

Consolidate sheets in one formula: =VSTACK(Jan!A2:C100, Feb!A2:C100, Mar!A2:C100) merges identical monthly tables into a single dataset — then FILTER, SORT, or pivot the combined spill.

Try it: interactive demo

Live demo

Choose stack direction for two lists.

Stack

Result:

Variations

Stack with headers once

Keep one header row, then the data:

=VSTACK(headers, Jan, Feb, Mar)

Hide the #N/A padding

Wrap uneven stacks:

=IFERROR(VSTACK(A2:A5, B2:B3), "")

Stack then dedupe and sort

Combine and clean in one shot:

=SORT(UNIQUE(VSTACK(A2:A50, B2:B50)))

Pitfalls & errors

Uneven widths pad with #N/A. VSTACK of ranges with different column counts fills the gaps with #N/A. Make the ranges the same width, or wrap in IFERROR.

Excel 365 only. Older versions lack VSTACK/HSTACK — copy-paste or Power Query to append there.

#SPILL! The combined result needs empty cells to land in. Clear the spill area.

Practice workbook

📊

Download the free Combine Ranges with VSTACK & HSTACK practice workbook

VSTACK/HSTACK examples (combine lists, stack with headers, dedupe+sort) with results shown, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I combine multiple ranges into one in Excel?

Use =VSTACK(range1, range2, ...) to stack them vertically into one list, or =HSTACK(...) to join them side by side. Both spill and update live. Requires Excel 365.

How do I consolidate data from several sheets with a formula?

Use VSTACK across the sheets: =VSTACK(Jan!A2:C100, Feb!A2:C100, Mar!A2:C100) merges identical tables into one dataset you can then filter or sort.

Why does VSTACK show #N/A?

The stacked ranges have different column counts, so the gaps are padded with #N/A. Make the ranges the same width or wrap the formula in IFERROR.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: VSTACK · HSTACK