Combine Dynamic Arrays in Excel Using VSTACK and HSTACK
A user in the Microsoft Excel Tech Community recently asked: “I have three separate calculation blocks—one for Sales, one for Returns, and one for Adjustments. Each uses its own FILTER formula. How can I combine these results into one long list automatically so I don’t have to manually copy-pasting every time the row counts change?”
In the past, combining data required complex VBA scripts or Power Query refreshes. However, with the introduction of Dynamic Array functions, you can now “stack” your calculations into a single, flowing block using VSTACK.
The Minimal Working Example
Section titled “The Minimal Working Example”To combine two different calculations (or ranges) vertically, use the following syntax:
=VSTACK(A2:C5, E2:G10)If you are combining dynamic calculations, such as two different filters, it looks like this:
=VSTACK( FILTER(A2:C100, B2:B100="Sales"), FILTER(A2:C100, B2:B100="Returns"))Tested on: Microsoft 365 (Desktop and Web) and Excel 2021. This feature is not available in Excel 2019 or earlier.
Why it Works: Understanding Argument Stacking
Section titled “Why it Works: Understanding Argument Stacking”The VSTACK function (Vertical Stack) takes multiple arrays and appends them to one another. The most powerful aspect is that it handles spilling. If your first calculation returns 5 rows today and 10 rows tomorrow, VSTACK automatically pushes the second calculation down to accommodate the new data.
| Argument | Requirement | Description |
|---|---|---|
array1 |
Required | The first range or formula result you want at the top. |
array2 |
Optional | The second range/formula to append below the first. |
[arrayN] |
Optional | You can add up to 254 additional arrays or ranges. |
Adapting This to Your Own Data
Section titled “Adapting This to Your Own Data”To make this production-ready, you often need to include headers or handle empty results. Follow these steps to build a robust stacked calculation block:
1. Add Constant Headers
Section titled “1. Add Constant Headers”If you want your dynamic list to have headers that move with the data, you can hardcode them into the VSTACK function using curly braces {}:
=VSTACK({"Date", "Type", "Amount"}, FILTER(A2:C100, B2:B100="Sales"))
2. Prevent #CALC! Errors
Section titled “2. Prevent #CALC! Errors”If one of your FILTER functions finds no matches, it will return a #CALC! error, which breaks the entire stack. Wrap individual blocks in IFERROR to keep the rest of the list visible:
=VSTACK(IFERROR(FILTER(A2:C10, D2:D10="Criteria"), "No Results"), E2:G10)
3. Sort the Final Stack
Section titled “3. Sort the Final Stack”Since VSTACK returns a new array, you can wrap the entire formula in a SORT function to organize the combined data:
=SORT(VSTACK(Range1, Range2), 1, 1) (This sorts the combined list by the first column in ascending order).
Common Follow-up Questions
Section titled “Common Follow-up Questions”What if my columns don’t match?
VSTACK requires that all arrays have the same number of columns. If you try to stack a 3-column array on top of a 2-column array, Excel will return a #N/A error in the missing slots. To fix this, you can use HSTACK or CHOOSECOLS to “pad” the smaller array so the widths match.
Can I stack data from different sheets?
Yes. You can reference ranges across multiple tabs:
=VSTACK('January'!A2:C10, 'February'!A2:C10).
If you have many sheets with the same layout, you can even use a 3D Reference like 'Jan:Dec'!A2:C10 inside certain functions, though VSTACK currently prefers explicit range selections or the use of Excel Tables.
Is there a way to stack horizontally instead?
If you want to place calculation blocks side-by-side rather than top-to-bottom, use the HSTACK function. It follows the exact same logic as VSTACK but expands the array to the right instead of downward. This is ideal for combining a list of names with a separate list of calculated IDs.