Combining two or more arrays in an Excel workbook used to be a complicated and tedious task. However, the introduction of the VSTACK and HSTACK functions in late 2022 was a game-changer that made this process much easier.
The VSTACK and HSTACK functions are available to those using Excel for Microsoft 365, Excel for the web , and the Excel mobile and tablet apps.
The VSTACK and HSTACK Syntaxes
The VSTACK function appends arrays vertically, using the following syntax:
where argument a is the array that will appear first at the top of the stack, and argument b is the first of up to 253 additional arrays to be appended immediately below.
On the other hand, the HSTACK function appends arrays horizontally:
where argument a is the leftmost array in the appended result, and argument b is the first of up to 253 additional arrays to be appended immediately to the right.
If you try to stack more than 254 arrays overall, you'll see a message reading "You've entered too many arguments for this function," and you'll have to reduce the number of arguments before you can commit the formula. You can get around this by nesting additional VSTACK and HSTACK combinations, but be aware that doing so can significantly hamper your workbook's performance.
VSTACK and HSTACK are dynamic array functions, meaning the result spills from the cell in which you type the formula, and any changes to the data in the source arrays are reflected in the stacked arrays. However, dynamic arrays are incompatible with Excel tables , so you must type the formula in a regular cell.
VSTACK and HSTACK in Use: Examples
You can use Excel's VSTACK and HSTACK functions to append data in regular cell ranges, Excel tables, or named ranges.
Here, typing:
into cell E1 stacks the three arrays in the cell ranges A2 to C6, A9 to C13, and A16 to C20 vertically.
After typing the equal sign, function name, and opening parentheses, rather than typing the ranges manually, use your mouse to select them.
By the same token, typing:
stacks the data in cells A2 to C6 and G2 to G6 horizontally.
On the other hand, after naming the ranges LgA , LgB , and LgC , typing:
achieves the same result as when using direct cell references.
However, the benefit of using named ranges instead of cell references is that they're easier to understand and parse, and you can quickly add named ranges to formulas without touching your mouse. You can apply exactly the same principle to the HSTACK function.
In all the examples above, if more rows or columns of data are added to any of the source ranges, VSTACK and HSTACK won't pick them up. However, if your source data is formatted as Excel tables , any extra rows or columns will be added to the result. This is because the formula references the tables' names (also known as a structured reference ) rather than set ranges of cells:
Tables also come in handy when using VSTACK and HSTACK to append data from multiple worksheets within a workbook. If your datasets are stored in regular ranges in separate worksheets, you have to jump between tabs to select the ranges manually, or use 3D referencing if each worksheet is identically structured. However, since all tables in an Excel workbook must be named uniquely, you don't need to tell Excel which sheet it's on—you can simply type the first letter, use the Arrow keys to jump to the relevant table, and press Tab to confirm.
Again, the same notion applies to the HSTACK function.
Combining VSTACK and HSTACK
Sometimes, you might need to use VSTACK and HSTACK together in a single formula to append parts of arrays.
In this example, let's say you want to create a new array with the league in the first column, the team in the second, the score in the third, and the bonus in the fourth.
One way to do this is to nest VSTACK inside HSTACK:
First, the formula vertically stacks the data in cells A2 to C6, A9 to C13, and A16 to C20. Second, it does the same with the data in cells G2 to G6, G9 to G13, and G16 to G20. Finally, it takes these two VSTACK arrays and appends them horizontally using HSTACK.
You can also nest HSTACK inside VSTACK:
This time, the formula first uses HSTACK to append the data in cells A2 to C6 with the data in cells G2 to G6, and then it does the same with the other two leagues. Finally, it takes these three HSTACK arrays and appends them vertically using VSTACK.
So, which of the two functions you nest inside the other depends on whether you need to perform more vertical or horizontal stacks. In the example above, because there are three arrays to stack vertically and only two horizontally, the formula is shorter when VSTACK is nested inside HSTACK. However, if you need to perform more horizontal stacks than vertical ones, it's best to nest HSTACK inside VSTACK.
Appending Arrays of Different Sizes Using VSTACK and HSTACK
When appending arrays vertically using VSTACK, the total number of rows is the combined count of all the rows in the selected arrays. However, the number of columns the result occupies is the same as the widest array in the selection.
So, if you use VSTACK to append the arrays in the screenshot below, the result is 15 rows high (five rows in each appended array) and four columns across (the width of the widest appended array). However, because the smaller arrays have been expanded to match the size of the largest one, #N/A is returned in place of the missing values.
One way to tidy up the resultant VSTACK array is to wrap the whole formula inside IFNA, and use double quotes to replace empty strings with blank cells:
Similarly, when using HSTACK to append arrays horizontally, the total number of columns is determined by the column count in the source data, but the number of rows is the same as the tallest selected array. So, in this example, where there are varying array heights, the formula returns the #N/A error where the smaller arrays have been expanded to match the largest one.
Again, to fix this, wrap the HSTACK formula inside IFNA, with double quotes replacing the errors:
Dealing With Blank Cells and Zeros in VSTACK and HSTACK
By default, VSTACK and HSTACK return both blanks and zeros in the source data as zeros in the result. While this could be ideal in some scenarios, it could become confusing when blank cells and zero values mean different things in your data.
In this example, the Coyotes were disqualified, so their score of zero should be reflected in the result of a VSTACK formula. However, the Leopards' score has yet to be entered, so that needs to remain blank. However, when the VSTACK function is used to append the data from each league vertically, both show as zeros.
To fix this, nest the VSTACK or HSTACK function in a SUBSTITUTE formula, and replace empty strings with empty strings:
Even though it seems strange for both the old text and new text arguments of the SUBSTITUTE function to be empty strings (represented by double quotes), this solution works well. Indeed, now, the Coyotoes' score is correctly showing as zero, while the Leopards' score is correctly showing as empty.
Adding Column Headers to VSTACK Outputs
When using VSTACK in Excel, you should always append data without column headers because, otherwise, the headers will be repeated in the result and be counted as part of the data during sorting and filtering.
However, one issue with omitting column headers is that the appended array could be unclear. To overcome this, use a formula to duplicate the header from the source data. Here, typing:
into cell E1, directly above the result of a VSTACK formula, dynamically replicates the header row above the first array, updating automatically if the original headers are changed.
You can use the same approach for row headers when using HSTACK. However, well-structured datasets tend to have fields as columns and records as rows, so if you find yourself needing row headers, consider transposing your data and using column headers instead.
The same principle applies to tables, but this time, you can use a structured reference to the header row rather than direct cell references:
The benefit of referencing table column headers is that if more columns are added, they're automatically picked up by both the formula containing a structured reference to the column header and the VSTACK formula. What's more, because the result of the VSTACK function is as wide as the widest referenced array, it's ready for you to expand the remaining tables and input your new data.
Sorting Appended VSTACK Arrays
One of the benefits of the VSTACK function is that it appends like-for-like data that was previously in separate ranges into a continuous array, meaning you can perform comparative analyses more efficiently.
But what if you want to sort the newly appended array? For example, this result would be much more valuable if the Score column were arranged in descending order.
In Excel, you can't reorder dynamic arrays using the filter button, because only the cell where you entered the formula is considered active. All the other cells serve only to accommodate the spill from the active cell, so they don't technically have any values that can be sorted.
Instead, nest the whole VSTACK formula inside the SORT function :
where 3 (the second argument of the SORT function) tells Excel to sort the array by the third column, and -1 (the third argument of the SORT function) tells Excel to sort this column in descending order.
If you're using SORT with HSTACK, the second argument of the SORT function represents the row by which you want to sort the result, the third argument is 1 for ascending order or -1 for descending order, and the fourth argument must be the Boolean value TRUE so that Excel knows you want to sort horizontally, not vertically.
VSTACK and HSTACK aren't the only functions for reshaping data in Microsoft Excel . For example, the TOCOL and TOROW functions let you convert an array into a single column or row, the WRAPCOLS and WRAPROWS functions turn a one-dimensional column or row into a two-dimensional array, and the PIVOTBY function lets you group and aggregate your figures without the need for a PivotTable .
