How to combine ranges / arrays in Excel with VSTACK & HSTACK functions

Syntax

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.

Result

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)

Syntax

The HSTACK function takes the following arguments:

Where arrays are cell ranges or arrays to join. Only array1 is required; additional arrays are optional.

Result

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)

Stack arrays horizontally or vertically

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.

Merge ranges and array constants

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.

Combine multiple Excel tables into one

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)

Merge dynamic arrays

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.

Handle blanks cells

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.

Combine ranges and remove empty rows

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:

Combine ranges without duplicates

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:

Join and sort arrays at once

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:

• HSTACK(B2:E4, H2:K4) is the array to sort.
• 1 in the 2nd argument is the sort index, i.e. the array is sorted by the first row (Year).
• 1 in the 3rd argument indicates the ascending sort order.
• TRUE means to sort columns, not rows.

#NAME? error

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. For more information, see How to fix #NAME error in Excel.

#SPILL! error

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

#N/A error

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.