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.

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.

Back to top

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}

Back to top

4. 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 "".

The HSTACK function returns

• #NAME? error if you misspell the function name.
• propagates errors, meaning that if the input contains an error (e.g., #VALUE!, #REF!), the function will return the same error.

4.2 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

4.3 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue..

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B2:D4 converted to hard-coded value using the F9 key. The HSTACK function requires non-error values which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

4.4 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

Back to top

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}

Back to top

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?

1. Select the destination cell.
2. Type =HSTACK(
3. Select the first cell range you want to horizontally stack, in this example cell reference B2:B14 in worksheet named Sheet2.
4. Press and hold SHIFT key.
5. Press with mouse on the last worksheet name you want to include in the formula. This example uses Sheet4 as the last worksheet.
6. Release the SHIFT key on your keyboard.
7. Type the ending parentheses )
8. 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

Functions in 'Array manipulation' category

The HSTACK function function is one of 11 functions in the 'Array manipulation' category.

Excel function categories

Excel categories

Leave a Comment