VSTACK & HSTACK Patterns

🎯 What You'll Learn

Explain how VSTACK and HSTACK combine ranges and arrays in a single formula
Write formulas that embed column headers directly — no separate header row needed
Apply 12 real-world patterns to build filtered, sorted, and calculated reports
Combine VSTACK with FILTER, SORT, MAP, and CHOOSECOLS for advanced outputs
Avoid the most common errors (header count mismatch, duplicate labels)

📋 Before You Begin

You know how to write basic Excel formulas (SUM, IF, etc.)
You've used named ranges or structured table references before
You're on Microsoft 365 or Excel 2021+ (VSTACK requires this)
No prior experience with dynamic array functions required

⚡ Beginner ⏱ ~3 min

The Core Concept

The pattern uses VSTACK and HSTACK to build structured, labeled reports entirely inside a formula. The trick: use curly brace arrays to hard-code your header row right inside the formula.

Instead of typing headers in row 1 and formulas in row 2, you write one formula that does both:

excel formula

=VSTACK({"Name","Sales","Region"}, North_Table, South_Table)

Result — spills into your sheet

Name      | Sales | Region
Alice     | 500   | North
Bob       | 300   | North
Dave      | 400   | South
Eve       | 450   | South

The {"Name","Sales","Region"} part IS the header row. VSTACK places it first, then stacks your data tables below it — all in one go.

How the formula builds the output:

{"Name","Sales","Region"}
header array

→

North_Table
3 rows

→

South_Table
3 rows

→

7-row report
with headers ✓

⚡ Beginner ⏱ ~2 min

The Master Data Setup

All 12 examples below use this regional sales dataset. Each table starts at row 2 (row 1 would be headers — we skip it to avoid duplicating labels).

Table Name (Range)	Salesperson	Sales	Region
North_Table (A2:C4)	Alice	500	North
	Bob	300	North
	Charlie	450	North
South_Table (E2:G4)	Dave	400	South
	Eve	450	South
	Frank	200	South
East_Table (I2:K4)	Grace	600	East
	Heidi	550	East
	Ivan	300	East
West_Table (M2:O4)	Jack	700	West
	Karl	350	West
	Liam	800	West

⚡ Intermediate ⏱ ~6 min

12 Formula Examples

Click any formula card to expand it. Formulas are grouped by complexity.

Basic Two-Region Stack

Combines North + South into one labeled list.

=VSTACK({"Name","Sales","Region"}, North_Table, South_Table)

Global Stack — All 4 Regions

Creates a master list of every salesperson company-wide.

=VSTACK({"Name","Sales","Region"}, North_Table, South_Table, East_Table, West_Table)

Summary from Disjointed Columns

Uses HSTACK to pick Name and Sales only, then adds headers.

=VSTACK({"Employee","Total Sales"}, HSTACK(A2:A4, B2:B4))

Append a Static Year Column

Adds a literal "2023" column to the right using a vertical array.

=VSTACK({"Name","Sales","Year"}, HSTACK(A2:B4, {"2023";"2023";"2023"}))

Dynamic Commission (Lambda)

Appends a calculated 10% commission column automatically.

=VSTACK({"Name","Sales","Comm"}, HSTACK(A2:B4, B2:B4*0.1))

Add a Status Label Column

Stacks data with a hard-coded "Active" status column appended.

=VSTACK({"Name","Amt","Status"}, HSTACK(A2:B4, {"Active";"Active";"Active"}))

Filtered Stack — Sales > 400

Combines two tables then filters to only high performers.

=VSTACK({"Name","Sales","Region"}, FILTER(VSTACK(North_Table, South_Table), CHOOSECOLS(VSTACK(North_Table, South_Table),2)>400))

Sorted Master List

All 4 regions combined, sorted by Sales descending.

=VSTACK({"Name","Sales","Region"}, SORT(VSTACK(North_Table, South_Table, East_Table, West_Table),2,-1))

Performance Tier via MAP

Uses MAP + LAMBDA to label each row "High" or "Low" based on sales.

=VSTACK({"Name","Sales","Tier"}, HSTACK(A2:B4, MAP(B2:B4, LAMBDA(s, IF(s>400,"High","Low")))))

#10

Reorder Columns with CHOOSECOLS

Swaps Region before Name using CHOOSECOLS inside the stack.

=VSTACK({"Region","Name"}, CHOOSECOLS(North_Table, 3, 1))

#11

Add a TOTAL Row at the Bottom

Stacks headers, data, then a final summary row with SUM.

=VSTACK({"Name","Sales"}, A2:B4, {"TOTAL", SUM(B2:B4)})

#12

Full Dashboard Table

Name, Sales, and a 5% bonus column — all in one nested formula.

=VSTACK({"Rep","Sales","Bonus"}, HSTACK(A2:A4, B2:B4, B2:B4*0.05))

Deep Dive: Example #8 — Sorted Master List

Let's break down the most powerful single-formula example:

excel formula

=VSTACK(
  {"Name","Sales","Region"},           ← header row hard-coded as array
  SORT(
    VSTACK(                            ← inner VSTACK combines all 4 tables
      North_Table, South_Table,
      East_Table, West_Table
    ),
    2,                                 ← sort by column 2 (Sales)
    -1                                 ← -1 = descending (high to low)
  )
)

Output — top rows shown

Name   | Sales | Region
Liam   | 800   | West
Jack   | 700   | West
Grace  | 600   | East
Heidi  | 550   | East
...

⚡ Beginner ⏱ ~1 min

How VSTACK "Thinks" About Your Formula

Imagine Excel talking through what it does when it evaluates your formula:

You (the formula)

Hey Excel, stack {"Name","Sales","Region"} first, then North_Table below it.

Excel

Got it — I'm treating {"Name","Sales","Region"} as a 1-row, 3-column array. Placing it at the top.

You

Now add South_Table below that.

Excel

Stacking South_Table's 3 rows right underneath North_Table. Total: 7 rows including header.

Excel

⚠️ One thing — make sure your header array has exactly 3 items. If you write {"Name","Sales"} for a 3-column table, I'll throw a #N/A error.

⚡ Intermediate ⏱ ~3 min

Test Your Understanding

Apply what you've learned to these scenarios — no scrolling back needed!

1. A colleague gives you this formula and says it throws a #N/A error. What's most likely wrong?
=VSTACK({"Rep","Total"}, HSTACK(A2:A4, B2:B4, C2:C4))

2. You want a report that shows only salespeople with sales above 350, sorted by sales high to low, with a header row. Which combination would you use?

3. Your data tables have columns in order: Region, Name, Sales. You want the final report as: Name, Sales, Region. Which function helps you reorder without rearranging source data?

4. You're building a monthly consolidation report. January data is in sheet Jan_Sales (A2:C31), February in Feb_Sales (A2:C28). Starting at A1 would cause which problem?

⚡ Intermediate ⏱ ~1 min

Pro Tips

⚡ Things to Remember

Match header count exactly: If your data has 3 columns, your header array must have exactly 3 items — {"A","B","C"}. A mismatch throws #N/A.
Start data at row 2, not row 1: If your source tables already have header rows in row 1, always reference A2 onward — otherwise original headers appear mid-report.
VSTACK across sheets: This pattern works beautifully for monthly consolidations — =VSTACK({"Month","Sales"}, Jan_Sales, Feb_Sales, Mar_Sales).

✅ Key Takeaways

VSTACK stacks arrays vertically; HSTACK stacks them horizontally — use both together for full control.
Curly brace arrays {"A","B"} let you hard-code headers inside a formula — no separate header row needed.
Nest SORT, FILTER, MAP, and CHOOSECOLS inside VSTACK to build powerful one-formula reports.
Semicolons in {"val";"val"} create vertical (column) arrays; commas create horizontal (row) arrays.