Skip to main content
Back to HomeReturn Home
More Menu
Reading ListGanti ke TerangSearch
Reading List

Queue · 0 items

Your reading list is empty. Save articles to read them later.

Start Reading
ESCto close
↑↓to navigate

Excel VSTACK and HSTACK: Combine Data Ranges Like a Pro

SheetHub7 min
You have January sales in Sheet1, February in Sheet2, March in Sheet3. Every month you copy-paste them into a master sheet, adjust column widths, fix formatting, and pray you didn't miss a row. There has to be a better way. There is. VSTACK and HSTACK turn multi-range data assembly from a manual chore into a single formula. One function appends ranges vertically, the other horizontally. No copy-paste, no macros, no risk of missing data.

What Are VSTACK and HSTACK?

VSTACK and HSTACK are part of Excel's dynamic array family. They take two or more arrays and combine them into one continuous result that spills across as many cells as needed.
FunctionWhat It DoesSyntax
VSTACKStacks arrays vertically (appends rows)=VSTACK(array1, array2, ...)
HSTACKStacks arrays horizontally (appends columns)=HSTACK(array1, array2, ...)
 Availability: Excel 365 (desktop, Mac, and web). Not available in Excel 2021 or earlier. The key difference from manual approaches: these formulas are dynamic. Add a row to your source data, and the VSTACK result updates automatically. You do not need to refresh it, and there is nothing to forget.

VSTACK — Vertical Stacking

VSTACK appends arrays one below the other. Think of it as stacking sheets of paper — each range becomes a new set of rows underneath the previous one. Basic syntax: 
=VSTACK(Jan!A2:C100, Feb!A2:C100, Mar!A2:C100)
This takes rows 2 through 100 from three sheets and produces one continuous table. Important: VSTACK does not deduplicate or manipulate headers. If each sheet has a header row, you will get repeated headers in your output. The common pattern is to include the header only from the first range: 
=VSTACK(Jan!A1:C1, Jan!A2:C100, Feb!A2:C100, Mar!A2:C100)
This keeps a single header row from January, then stacks data rows from all three months underneath.

Practical Example — Monthly Report Consolidation

Say you run a quarterly review. Each month's data lives in its own sheet with identical columns: Date, Product, Sales, Region. 
=VSTACK(Q1!A2:D500, Q2!A2:D500, Q3!A2:D500, Q4!A2:D500)
One formula gives you the full year. Filter or chart the result directly — it updates whenever source data changes.

HSTACK — Horizontal Stacking

HSTACK appends arrays side by side. You use it when the data you need lives in separate ranges that share the same row count. Basic syntax: 
=HSTACK(A2:B100, D2:E100)
This combines columns A-B with columns D-E into one four-column result, skipping column C.

Practical Example — Side-by-Side Comparison

You have actual sales in one range and budget figures in another. HSTACK puts them next to each other for easy comparison: 
=HSTACK(Actuals!B2:B100, Budget!B2:B100)
RangeWhat It ContainsPosition After HSTACK
Actuals!B2:B100Sales achievedColumn 1
Budget!B2:B100Sales targetColumn 2
 Column count mismatch: If the ranges have different numbers of rows, HSTACK fills the shorter range with #N/A. Wrap the result in IFERROR or FILTER to clean it up.

Real-World Use Cases

Here is how you will actually use these functions:
ScenarioFormulaWhy It Works
Consolidate 12 monthly sheets into yearly report=VSTACK(Jan!A2:B, Feb!A2:B, ..., Dec!A2:B)Single formula, auto-updates when monthly data changes
Compare actual vs budget side by side=HSTACK(Actuals!B2:B, Budget!B2:B)Instant column alignment without manual lookups
Automate your data cleaning pipeline with VSTACK=VSTACK(cleaned_data, new_data)Append new cleaned data to existing clean set
Combine filtered results from multiple regionsVSTACK + FILTER (see below)Only include rows that meet criteria

VSTACK + FILTER — Dynamic Data Assembly

This is where VSTACK gets powerful. Combine it with FILTER to stack only the rows that matter. 
=VSTACK(
    FILTER(Jan!A2:C100, Jan!C2:C100>1000),
    FILTER(Feb!A2:C100, Feb!C2:C100>1000),
    FILTER(Mar!A2:C100, Mar!C2:C100>1000)
)
This formula pulls high-value sales (over $1,000) from three months and stacks them. When new data arrives or values change, the result recalculates automatically. You can extend this pattern: LAMBDA can process each VSTACK component before assembly, letting you transform data before combining it. 
=LET(
    jan_high, FILTER(Jan!A2:C100, Jan!C2:C100>1000),
    feb_high, FILTER(Feb!A2:C100, Feb!C2:C100>1000),
    VSTACK(jan_high, feb_high)
)
Using LET here caches each filtered range so Excel only calculates it once — a performance boost when working with large datasets.

Common Errors and Troubleshooting

A few things can trip you up with VSTACK and HSTACK: #N/A — mismatched column counts (VSTACK). If one range has 4 columns and another has 5, VSTACK fills the missing columns with #N/A. Wrap the result in IFERROR or use CHOOSECOLS to standardize column counts before stacking: 
=IFERROR(VSTACK(Jan!A2:C100, Feb!A2:D100), "")
=HSTACK(CHOOSECOLS(A2:C100, 1, 2), D2:E100)
#VALUE! — blank rows filtered the wrong way. Testing the full multi-column array against "" doesn't work — the comparison applies to the whole array and throws #VALUE!. Stack the data once with LET, then filter on a single column with INDEX: 
=LET(
    stacked, VSTACK(Jan!A2:C100, Feb!A2:C100),
    FILTER(stacked, INDEX(stacked,,1)<>"")
)
INDEX(stacked,,1) returns the first column of the stacked result, and the FILTER keeps only rows where that column isn't empty. #NAME? — VSTACK not recognized. VSTACK and HSTACK are Excel 365 only. Excel 2021, 2019, and earlier return #NAME? for these functions. In shared workbooks, confirm everyone is on Microsoft 365, or fall back to Power Query for the consolidation. Large datasets: VSTACK with 100,000+ rows works but can slow down calculation. For truly massive consolidations, Power Query (Get & Transform) handles the load better and gives you more transformation options.

VSTACK/HSTACK vs Alternatives

MethodProsCons
VSTACK / HSTACKSingle formula, auto-updates, no setupExcel 365 only
Power QueryHandles any size, data transformations, merge queriesRequires setup and manual refresh
Manual copy-pasteWorks in any Excel versionError-prone, static, time-consuming
 Power Query is the right choice for one-time imports, complex merges, or data exceeding 500K rows. VSTACK and HSTACK are ideal for recurring reports where you want the result to update live without refresh.

AI Prompt Callout

Now that you have seen how VSTACK and HSTACK work with real examples, here is a template you can use with ChatGPT, Gemini, or Claude to generate a formula for your specific data:
AI Prompt Template
I have monthly sheets named Jan, Feb, and Mar with identical column structure (Date, Product, Sales) in rows 2 through 100. Write a formula to combine all three into one continuous table. Each month's data should include only rows where Sales exceeds $1,000. Use VSTACK and FILTER.
Adjust the sheet names, column structure, and threshold to match your workbook. The pattern works for any scenario where you need to stack filtered data from multiple ranges.

SheetHub's Take

VSTACK and HSTACK solve one of the most common Excel frustrations: combining data from multiple ranges with a simple, dynamic formula. They are part of the modern Excel toolkit alongside FILTER, SORT, UNIQUE, and LET. Next time you reach for copy-paste to combine data, reach for VSTACK instead. Open a workbook with data spread across multiple sheets, type =VSTACK( and select your ranges. You will not go back.
Topics

Topics in this article

Explore related topics and continue reading similar content.

ExcelFormulasVSTACKHSTACKDynamic ArraysTutorial

Share this article

Discussion

Preparing the comments area...

You Might Also Like