by Alexander Frolov, updated on
The tutorial shows how to merge multiple arrays in Excel 365 vertically and horizontally using VSTACK and HSTACK functions.
Combining two or more ranges has traditionally been a daunting task in Excel. Not any longer! The two functions recently introduced in Excel 365, VSTACK and HSTACK, have made the job incredibly easy. Now, merging ranges and arrays, even dynamic ones whose sources are variable in size, is as easy as supplying a couple of range references to a formula.
The VSTACK function in Excel combines multiple arrays or ranges vertically into a single array. Each subsequent array is appended to the first blank cell at the bottom of the previous array.
The VSTACK function takes the following arguments:
Where arrays are cell ranges or arrays to join. Array1 is required; array2 and subsequent ones are optional.
The output of VSTACK is a single array that has as many columns as the largest of the source arrays and as many rows as all of the source arrays combined.
For example, to combine two ranges vertically, the formula is:
=VSTACK(B5:D9, B14:D18)
The HSTACK function in Excel merges multiple ranges or arrays horizontally into a single array. Each subsequent array is added to the right of the preceding array.
The HSTACK function takes the following arguments:
Where arrays are cell ranges or arrays to join. Only array1 is required; additional arrays are optional.
As an output, the HSTACK function returns a single array that has as many columns as all of the source arrays combined and as many rows as the tallest of the source arrays.
For example, to combine two ranges horizontally, the formula is:
=HSTACK(C4:F6, I4:L6)
Both functions are only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.
To effectively use the VSTACK and HSTACK functions in your worksheets, here are some key facts to take note of:
With the VSTACK and HSTACK functions, combining ranges in Excel is really easy and straightforward. The below examples will give you some inspiration to get started and show best practices approaches.
Depending on the structure of your original arrays and the layout of your worksheet, you can prefer combining ranges vertically or horizontally.
To merge multiple ranges into a single horizontal stack, use the HSTACK function. In this case, the ranges are stacked next to each other. For instance:
=HSTACK(B3:B7, B12:B16, B21:B25)
To combine multiple ranges into a single vertical stack, utilize the VSTACK function. In this case, the ranges are placed below each other. For example:
=VSTACK(B3:B7, B12:B16, B21:B25)
For better visualization, we are combining ranges including their headers. In real worksheets, you would normally join only data without column headers.
In situations when column headers or some values are missing the original ranges, you can supply the missing data in an array constant directly to the VSTACK or HSTACK function. For example:
=VSTACK({"Item","Color","Size"}, A2:C6, A10:C14)
As a result, you'll get two ranges stacked vertically below each other.
Aside from arrays and ranges, the VSTACK and HSTACK functions can also merge multiple Excel tables into one. Given that Excel tables automatically expand to include new data, this is a perfect solution for data sets of variable size. Simply, supply table references to VSTACK and HSTACK, and you won't have to worry about updating your formulas no matter how many new row/columns of data are added to the source tables.
=VSTACK(Table1, Table2)
If the arrays you are combining are dynamic, i.e. returned by dynamic array formulas, you can join such arrays by using the spill range operator, which is a hash tag (#) appended to the address of the upper left cell in the spill range.
For example, to combine two arrays generated by the UNIQUE function in the screenshot below, you can use this formula:
=VSTACK(E3#, E12#)
Where E3 is the topmost cell of the first array and E12 is the topmost cell of the second array.
The benefit of this approach is that with any change in the source data, all 3 arrays will be recalculated and updated automatically by Excel.
In case the sources ranges contain empty cells, the returned array will have zeros in the place of blanks. To fix this, you can nest your VSTACK or HSTACK function in the first argument of SUBSTITUTE and tell it to replace zeros with empty strings (""). For example:
=SUBSTITUTE(VSTACK(A3:B7, A12:B16), 0, "")
If your original arrays contain zero values, then substitute empty strings rather than zeros to avoid replacing absolutely all zeros in the returned array. For example:
=SUBSTITUTE(VSTACK(A3:B7, A12:B16), "", "")
Substituting an empty string with an empty string may seem illogical, but it does work. In a stacked array, Excel displays zeroes instead of blanks only for better visualization; in fact those cells are empty.
When there are any blank rows or columns in the sources ranges, the resulting array will have zero-filled rows or columns in their place. To eliminate such rows and/or columns, you can filter out blanks with the help of the FILTER function.
The general idea is to supply the VSTACK or HSTACK output to the array argument of the FILTER function and use instruct it to filter non-empty entries (<>"").
Essentially there can be three basic scenarios:
To filter all rows in which a particular column is not blank, check that specific column. For example, to combine rows that have any value in column A, the formula in D4 is:
=FILTER(VSTACK(A3:B7, A12:B16), VSTACK(A3:A7, A12:A16)<>"")
To remove empty rows that do not have a single value in them, each column should be checked individually. To have it done, in the include argument of the FILTER function, you supply a separate criterion for each column and use the plus sign (+) to test them with the OR logic.
For example, to only remove absolutely blank rows and keep those that have a value in any column, the formula in G4 is:
=FILTER(VSTACK(A3:B7, A12:B16), (VSTACK(A3:A7, A12:A16)<>"")+(VSTACK(B3:B7, B12:B16)<>""))
For more information, please see How to filter with multiple OR criteria.
To filter out rows that have at least one empty cell in any column, you again check each individual column for non-blanks, but this time test the criteria with the AND logic using the asterisk (*).
For example, to remove rows that have blanks in either column A or B, the formula in E16 is:
=FILTER(VSTACK(A3:B7,A12:B16),(VSTACK(A3:A7,A12:A16)<>"")*(VSTACK(B3:B7,B12:B16)<>""))
For more details, please see How to filter with multiple AND criteria.
Tips:
To combine only unique records from two or more ranges, use the stacking functions together with UNIQUE.
For example, to stack ranges B2:E4 and H2:K4 horizontally without duplicates, the formula in C8 is:
=UNIQUE(HSTACK(B2:E4, H2:K4), TRUE)
The logical value TRUE in the 2nd argument of UNIQUE tells the function to compare data across columns, not rows.
As the result, the 2nd occurrence of the year 2017 is left out of the resulting array:
When merging huge arrays, as a finishing touch, you may want to sort the output. This is quite easy to do by nesting HSTACK or VSTACK in another dynamic array function - SORT.
In our case, the formula takes this form:
=SORT(HSTACK(B2:E4, H2:K4), 1, 1, TRUE )
Breaking down the formula, this is what we have:
To combine the same range of cells from different worksheets, use a 3D reference in your HSTACK or VSTACK formula.
To make a 3D VSTACK formula for the datasets below, this is what you need to do:
The completed formula will look as follows:
=VSTACK('Store 1:Store 3'!A2:C6)
If your original datasets are likely to expand in future, you can include more rows than are currently populated with data, and then filter out empty rows. For instance:
=FILTER(VSTACK('Store 1:Store 3'!A2:C100), VSTACK('Store 1:Store 3'!A2:A100)<>"")
When merging two or more arrays of different size, Excel "expands" smaller array(s) to match the size of the largest one and returns #N/A errors in place of the missing values. To catch and handle those errors, you can use the IFNA function in Excel 2013 and higher or IFERROR in earlier versions.
In the below formula, IFNA is configured to replace #N/A errors with empty strings (""), which look like blank cells to the human eye.
=IFNA(HSTACK(B2:E4, H2:J3), "")
If the functions do not work as expected or are not available in your Excel, you are likely to get one of these errors.
Normally, occurs if the function's name is misspelled, but may also indicate that the function is not available in your Excel version. Please remember that VSTACK and HSTACK are only supported in Excel 365.
Occurs if one or more cells in the spill range are not completely blank. To fix it, just clear the obstructing cells. For more information, please see SPILL! error in Excel: causes and fixes
If one of the source arrays has fewer columns than the largest array, VSTACK returns #N/A errors in the columns where values are missing.
If one of the source arrays has fewer rows than the largest array, HSTACK returns #N/A errors in extra rows.
The solution is to use the IFNA or IFERROR function to replace errors with empty stings or another value of your choice as shown in this example.
That's how to combine arrays in Excel 365 using the VSTACK and HSTACK functions. I thank you for reading and hope to see you on our blog next week!
Stack ranges in Excel – formula examples (.xlsx file)
Table of contents