How to use the HSTACK function
What is the HSTACK function?
The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array
What is HSTACK and abbreviation of?
HSTACK and abbreviation of horizontal stacking.
Which Excel versions have the HSTACK function?
The HSTACK function is available to Excel 365 users and is in the "Array manipulation" category.
Table of Contents
1. Syntax
HSTACK(array1,[array2],...)
Back to top
2. Arguments
| array1 | Required. The first cell range or array. |
| [array2] | Optional. The second cell range or array to merge. |
3. Example
The image above demonstrates how the HSTACK function merges the ranges B2:D4 (blue) and F2:H4 (red). It appends the second cell range (red) to the right of the first cell range (blue).
Formula in cell E4:
Cell range B2:D4 are merged with F2:H4 horizontally. The first array is the leftmost array in the joined array, the remaining arrays are joined in sequence to the right of the first array.
The HSTACK function is great for advanced calculations using the LAMBDA functions, it is there it really shines. The LAMBDA functions let you build recursive formulas that wasn't possible before unless VBA was used.
3.1 Explaining formula
Step 1 - HSTACK function
The HSTACK function has one or more arguments, the first argument is required, the subsequent arguments are optional. However, you need at least two arguments to join two cell ranges or arrays.
There is an exception to that if you use parentheses and a comma to differentiate the cell references in the first arguemnt. Here is an example:
=HSTACK((E1:E2,E3:E4))
The formula above joins cell ranges E1:E2 and E3:E4 and both are entered in the first argument. This is however, a very special case but is good to know.
HSTACK(array1,[array2],...)
Step 2 - Populate arguments
array1 - B2:D4
[array2] - F2:H4
Step 3 - Evaluate function
HSTACK(B2:D4, F2:H4)
becomes
HSTACK({89, 68, 19;27, 84, 92;26, 98, 62}, {37, 89, 99;63, 8, 1;100, 31, 70})
and returns
{89, 68, 19, 37, 89, 99;
27, 84, 92, 63, 8, 1;
26, 98, 62, 100, 31, 70}
4. HSTACK Function error
The image above demonstrates what happens when you try to append two cell ranges with different numbers of rows. The first cell range (blue) has three columns and the second cell range (red) has 2 columns.
The result is an array containing #N/A errors in locations where no value exists.
Formula in cell E4:
There is a workaround to eliminate errors from the output of the HSTACK function. The IFNA function lets you remove #N/A errors. There is however no argument in the HSTACK function that lets you pad empty cells with a text string.
Formula in cell B8:
The image above shows two different arrays in B2:D4 and F2:H3. The latter array is one row smaller and the HSTACK function pads the empty cells with #N/A! errors.
The HSTACK function in cell B8 replaces all #N/A errors with nothing. Use two double quotes in the second argument to create empty cells. This is demonstrated in cell B8 and cells below and cells to the right.
4.1 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail.
Step 1 - Stack cell ranges horizontally
HSTACK(B2:D4, F2:H3)
becomes
HSTACK({89, 68, 19;
27, 84, 92;
26, 98, 62},
{37, 89, 99;
63, 8, 1}
)
and returns
{89,68,19,37,89,99;
27,84,92,63,8,1;
26,98,62,#N/A,#N/A,#N/A}
Step 2 - Remove #N/A errors
The IFNA function catches #N/A errors and replaces them with a value you provide.
IFNA(value, value_if_na)
IFNA(HSTACK(B2:D4, F2:G:4), "")
becomes
IFNA(
{89,68,19,37,89,99;
27,84,92,63,8,1;
26,98,62,#N/A,#N/A,#N/A}
, "")
and returns
{89,68,19,37,89,99;
27,84,92,63,8,1;
26,98,62,"","",""}
Note that the IFNA function replaces the #N/A errors with an empty string "".
5. Extract unique distinct columns from multiple cell ranges
The image above shows a formula that extracts unique distinct columns from multiple cell ranges.
Formula in cell C10:
Explaining formula
Step 1 - Join cell ranges
HSTACK(array1,[array2],...)
HSTACK(B3:D5, F3:H5)
becomes
HSTACK({89, 27, 26;
"Charles", "Tina", "Linda";
19, 45, 62},
{89, 27, 26;
"Charles", "Tina", "Laura";
24, 45, 62})
and returns
{89,27,26,89,27,26;
"Charles","Tina","Linda","Charles","Tina","Laura";
19,45,62,24,45,62}.
Bolded values are two duplicate columns.
Step 2 - Extract unique distinct columns
The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.
UNIQUE(array, [by_col], [exactly_once])
UNIQUE(HSTACK(B3:D5, F3:H5), TRUE)
becomes
UNIQUE({89,27,26,89,27,26;
"Charles","Tina","Linda","Charles","Tina","Laura";
19,45,62,24,45,62}, TRUE)
and returns
{89,27,26,89,26;
"Charles","Tina","Linda","Charles","Laura";
19,45,62,24,62}
6. HSTACK function with 3D cell references
This example demonstrates how to stack cell ranges horizontally using 3D cell references.
What is a 3D cell reference?
A 3D cell reference in Microsoft Excel refers to data that spans across multiple worksheets within the same workbook. This allows you to perform calculations or aggregate data from the same cell, or range of cells, across several sheets. A 3D reference includes the sheet range and cell range you want to refer to.
Excel 365 dynamic array formula in cell B4:
The HSTACK function stacks the cell ranges B2:B14 from worksheets Sheet2, Sheet3, and Sheet4 to cell B4 and adjacent cells to the right and below as far as needed.
How to enter a 3D cell reference in the HSTACK function?
- Select the destination cell.
- Type =HSTACK(
- Select the first cell range you want to horizontally stack, in this example cell reference B2:B14 in worksheet named Sheet2.
- Press and hold SHIFT key.
- Press with mouse on the last worksheet name you want to include in the formula. This example uses Sheet4 as the last worksheet.
- Release the SHIFT key on your keyboard.
- Type the ending parentheses )
- Press Enter.
You can also type the 3D cell reference in the formula bar if you prefer that.
6.1 Benefits of Using 3D Cell References
- Efficient Data Aggregation Across Sheets: A 3D reference enables you to perform calculations, like SUM, AVERAGE, COUNT, etc., across multiple sheets with just one formula. This is useful in workbooks where each sheet represents similar data for different periods.
- Simplifies Workbook Management: With a 3D reference, if you add more sheets within the worksheet range, they automatically get included in the formula. This helps keep data updated without needing to modify formulas every time a new sheet is added.
- Improved Accuracy: Reduces errors as you avoid repetitive manual references and ensure consistent formula application across multiple sheets.
- Time Efficiency: Saves time since you can perform operations across multiple sheets in one go without manually linking each sheet's cells separately.
7. Get Excel file
Useful resources
HSTACK function - Microsoft support
How to combine ranges / arrays in Excel with VSTACK & HSTACK functions
'HSTACK' function examples
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Functions in 'Array manipulation' category
The HSTACK function function is one of 11 functions in the 'Array manipulation' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form