HSTACK Function

The HSTACK function combines arrays horizontally into a single array. Each array is appended to the right of the previous one, and the result spills onto the worksheet. HSTACK works with ranges, Excel Tables, and arrays created by other formulas, and the result updates when the source data changes. Use HSTACK to combine ranges horizontally and VSTACK to combine ranges vertically.

VSTACK is often an early step in a formula, combining data before other functions work on it. HSTACK is more often the last step. It builds a table one column at a time, and each column can come from anywhere: a range, a column in a table, a calculation, or a single value. This is something the CHOOSECOLS function can't do, since it only selects columns that already exist in one array. Most of the examples below start with columns that don't exist yet, then show HSTACK working with other functions.

Key features

• Combines ranges or arrays from left to right, in the order given.
• Works with ranges, Excel Tables, table columns, array constants, single values, and arrays returned by other formulas.
• Returns as many columns as all the arrays combined, and as many rows as the tallest array. Shorter arrays are padded with #N/A.
• A single value fills only the first row.
• Empty cells come through as zeros.
• Returns an array, not a range. FILTER, SORT, and GROUPBY accept the result directly.
• COUNTIF and SUMIFS can't use the array inside a formula, but they can use the result's spill range on the worksheet, like F5#.

Table of contents

• Basic examples
• Change the column order
• Ranges with different row counts
• Add a calculated column
• Add a column with the same value
• Build a summary table
• Compare tables side by side
• Group by columns that aren't next to each other
• Return more than one result per row
• Return selected columns
• Notes

Basic examples

HSTACK combines its arguments from left to right, in the order given. The formulas below combine two and three ranges:

=HSTACK(range1,range2) // combine two ranges
=HSTACK(range1,range2,range3) // combine three ranges

HSTACK also accepts array constants and single values, so it can add a column of values to a range, or build a row from scratch:

=HSTACK({"Red";"Blue";"Green"},range) // add a column of values
=HSTACK("Name","Score","Grade") // returns {"Name","Score","Grade"}

A 3D reference puts the same range from several sheets side by side, and VSTACK can add a header row to the result:

=HSTACK(Sheet1:Sheet3!B5:B10) // same column from three sheets
=VSTACK({"Name","Score"},HSTACK(range1,range2)) // add headers

In the worksheet shown above, HSTACK combines three separate ranges. The formula in H4 is =HSTACK(B4:B9,D4:D9,F4:F9), and the result spills into H4:J9. Because each range includes its header, the headers come along with the data.

Change the column order

HSTACK doesn't need to follow the column order of the source data. The result follows the order of the arguments, so you can rearrange columns without touching the source. In the worksheet below, the goal is to list the same data with the ID first. The formula in F4 is:

=HSTACK(D4:D9,B4:B9,C4:C9)

HSTACK returns the ID column first, followed by Name and Dept, and the result spills into F4:H9. The source data stays as it is. CHOOSECOLS can do the same by column number: =CHOOSECOLS(B4:D9,3,1,2). For how the two compare, see Return selected columns.

Ranges with different row counts

HSTACK returns as many rows as the tallest range. When a range has fewer rows, HSTACK pads it with #N/A errors. You can see this behavior in the worksheet below, where the first range has five names and the second range has three. The formula in F5 is:

=HSTACK(B5:B9,D5:D7)

Group B has only three names, so the last two cells in its column show #N/A. To replace the errors with empty cells, wrap HSTACK in the IFNA function, as in I5:

=IFNA(HSTACK(B5:B9,D5:D7),"")

IFNA replaces each #N/A with an empty string (""), which displays as an empty cell. The IFERROR function also works, but it traps every error, so there is a risk it will hide real errors in the source data. Another option is to pad the short range yourself with the EXPAND function:

=HSTACK(B5:B9,EXPAND(D5:D7,ROWS(B5:B9),,""))

EXPAND extends D5:D7 to five rows and fills the new rows with empty strings, so only that range is padded.

Add a calculated column

HSTACK can add a column that doesn't exist in the source data. In the worksheet below, the goal is to list the orders from the West region with a new column for the total of each order. The data is in an Excel Table named orders, and the formula in G5 uses structured references to refer to the table and its columns:

=FILTER(HSTACK(orders,orders[Qty]*orders[Price]),orders[Region]="West")

Working from the inside out, orders[Qty]*orders[Price] returns one total for each order, and HSTACK appends these totals to the right of the table's four columns. Next, the FILTER function keeps only the rows where the region is "West". Because HSTACK runs before FILTER, the new Total column is filtered along with the rest of the data, and the four matching orders spill into G5:K8.

To include headers, extend the table's header row with HSTACK, and stack it on top with VSTACK. The worksheet below uses the same orders table, and the formula in B4 is:

=VSTACK(HSTACK(orders[#Headers],"Total"),FILTER(HSTACK(orders,orders[Qty]*orders[Price]),orders[Region]="West"))

orders[#Headers] returns the table's four headers, and HSTACK adds "Total" as a fifth. VSTACK stacks this header row on top of the filtered orders, and the result spills into B4:F8.

The added column can be any calculation that returns one value per row. For example, to number the rows of a result, put a SEQUENCE column on the left. The formula in B5 is:

=LET(result,FILTER(orders,orders[Region]="West"),HSTACK(SEQUENCE(ROWS(result)),result))

LET names the filtered orders result, so FILTER runs once. ROWS counts the rows in result, SEQUENCE returns the numbers 1 to 4, and HSTACK puts them to the left of the orders. The result spills into B5:F8.

Add a column with the same value

To add a column that repeats one value, like a label, you might try a formula like =HSTACK(East,"East"). However, this puts "East" in the first row only, and the rows below get #N/A errors, because HSTACK treats the single value as a one-row array and pads it like any shorter array. The solution is to make the value as tall as the data with the EXPAND function. In the worksheet below, the tables East and West list sales by name, and the goal is to combine them with a third column that shows where each row came from. The formula in H5 looks like this:

=VSTACK(
  HSTACK(East,EXPAND("East",ROWS(East),,"East")),
  HSTACK(West,EXPAND("West",ROWS(West),,"West"))
)

The ROWS function returns the number of rows in each table, and EXPAND extends the label to that many rows, padding the new rows with the label instead of #N/A. HSTACK appends each label column to its table, and VSTACK stacks the two labeled tables, which spill into H5:J11. The same idea adds a blank spacer column: EXPAND("",ROWS(data),,"").

Build a summary table

HSTACK is a good way to assemble a summary from separate calculations. In the worksheet below, the goal is to total the quantity for each color in a table named sales, sorted by total. The formula in F4 is:

=LET(
  colors,UNIQUE(sales[Color]),
  totals,SUMIFS(sales[Qty],sales[Color],colors),
  VSTACK({"Color","Total"},SORT(HSTACK(colors,totals),2,-1))
)

The UNIQUE function returns the four colors, and the SUMIFS function returns a total for each one. At this point, the colors and totals are two separate arrays. HSTACK joins them into a two-column table, which the SORT function sorts by the second column (the totals) in descending order. Finally, VSTACK adds a header row, and the result spills into F4:G8.

In Excel 365, the GROUPBY function can build this summary in one step. The formula =GROUPBY(sales[Color],sales[Qty],SUM,0,0,-2) returns the same colors and totals, sorted by total. GROUPBY is the better choice for a simple summary like this, but HSTACK works in Excel 2024, which doesn't have GROUPBY. HSTACK is also useful for assembling a custom table, as in the next example.

Many formulas on this site use the same pattern, for example VSTACK({"Year","Total"},HSTACK(years,sums)) in Sum by year. See also 10 most common text values. To add a total row at the bottom, see Add headers and a total row on the VSTACK page.

Compare tables side by side

Because each argument to HSTACK can be a separate calculation, each column in a summary can come from a different source. In the worksheet below, the tables April and May list quantities by color, and the goal is to compare the totals for each month side by side. The formula in H5 is:

=LET(
  colors,SORT(UNIQUE(VSTACK(April[Color],May[Color]))),
  HSTACK(
    colors,
    SUMIFS(April[Qty],April[Color],colors),
    SUMIFS(May[Qty],May[Color],colors)
  )
)

VSTACK combines the colors from both tables, and UNIQUE and SORT return a sorted list of the four colors, which LET names colors. Next, both SUMIFS functions use colors as their criteria: the first totals each color in April, the second totals each color in May, and HSTACK puts the colors and the two sets of totals side by side. Silver appears only in May, so its April total is 0. GROUPBY summarizes one set of data only, so it can't build this comparison directly from two tables.

Group by columns that aren't next to each other

HSTACK also works with GROUPBY. The row_fields argument in GROUPBY takes one range or array, so to group by more than one column, the columns normally need to be next to each other. HSTACK removes this limit. In the worksheet below, the goal is to total the quantity by region and color in a table named data, where the Region and Color columns are separated by the Rep column. The formula in H5 is:

=GROUPBY(HSTACK(data[Region],data[Color]),data[Qty],SUM)

HSTACK combines the Region and Color columns into a two-column array, and GROUPBY groups by both, returning a total for each region and color combination, plus a grand total. The columns can be in any order, and they can be calculated. For example, this formula groups by year and region:

=GROUPBY(HSTACK(YEAR(data[Date]),data[Region]),data[Qty],SUM)

HSTACK can also give GROUPBY more than one calculation. For example, HSTACK(SUM,AVERAGE) as the function argument returns a sum and an average for each group. For details, see GROUPBY with multiple calculations, and for a worked example that groups by a calculated month, see GROUPBY with monthly totals. GROUPBY is available in Excel 365 only.

Return more than one result per row

The BYROW function applies a calculation to each row in a range, but the calculation must return a single value. If you try to return two values per row with HSTACK, like this:

=BYROW(C5:E9,LAMBDA(r,HSTACK(SUM(r),MAX(r))))

BYROW returns a #CALC! error, because Excel won't return an array of arrays. The MAP and SCAN functions have the same limitation. The workaround is to turn the problem around: run BYROW once for each result, and combine the results with HSTACK. In the worksheet below, the goal is to show each student's total and best score. The formula in G5 is:

=HSTACK(B5:B9,BYROW(C5:E9,SUM),BYROW(C5:E9,MAX))

Each BYROW returns one column: the first sums the scores in each row, and the second returns the highest score in each row. HSTACK puts the names and the two columns side by side, and the result spills into G5:I9.

BYROW also accepts several functions inside HSTACK, as in BYROW(C5:E9,HSTACK(SUM,MAX,MIN)), but the result includes the first row only. See Multiple calculations with HSTACK on the BYROW page.

The same approach works with the SCAN function, which can't return more than one value per step either. To get running totals for two columns, run SCAN twice and combine the results: HSTACK(SCAN(0,qty,SUM),SCAN(0,amount,SUM)). When one result depends on the other, build the result row by row with REDUCE and VSTACK instead, as explained in Stack results in a loop with REDUCE.

Return selected columns

To return only some of the columns in a table, you can give FILTER an array built with HSTACK. In the worksheet below, the goal is to list the name and email of everyone in Sales. The data is in a table named staff, and the formula in G5 is:

=FILTER(HSTACK(staff[Name],staff[Email]),staff[Dept]="Sales")

HSTACK combines the Name and Email columns into a two-column array, and FILTER returns the rows where the department is "Sales". The CHOOSECOLS function can do the same job by column number:

=CHOOSECOLS(FILTER(staff,staff[Dept]="Sales"),1,3)

Both formulas return the same result. CHOOSECOLS is shorter when you pick columns by number from one range. HSTACK picks columns by name, so the structured references still point to the right columns if columns are inserted or moved in the table. HSTACK can also combine columns from different tables or ranges, which CHOOSECOLS can't do. For more details, see Filter and exclude columns.

The same idea works with XLOOKUP: =XLOOKUP("Priya",staff[Name],HSTACK(staff[Email],staff[Ext])) returns both values for one name. However, with more than one lookup value, XLOOKUP returns only the first column, so use FILTER instead.

Notes

• If the cells where the result should spill aren't empty, HSTACK returns #SPILL!. HSTACK also returns #SPILL! inside an Excel Table, because tables don't support spilling formulas.
• When arrays have different numbers of rows, HSTACK pads the shorter arrays with #N/A. To remove the errors, use IFNA rather than IFERROR, or pad an array yourself with EXPAND. See Ranges with different row counts.
• A single value fills only the first row. See Add a column with the same value.
• Empty cells come through as zeros, but they still count as empty inside the array: ="" and ISBLANK both return TRUE for them. To show them as blanks, see Combine ranges that grow on the VSTACK page.
• An empty argument, as in HSTACK(range1,,range2), returns #VALUE!. For a blank, use an empty string ("").
• An error in a source cell appears in its own cell in the result. But when an argument is an error, such as FILTER returning #CALC! when nothing matches, HSTACK returns that error instead of a combined result.
• Full-column references like HSTACK(A:A,B:B) return #SPILL! in any cell below row 1, because the result is 1,048,576 rows tall. Use ranges sized to the data, or trim references like A:.A.
• HSTACK inside BYROW, MAP, or SCAN returns #CALC!. See Return more than one result per row.
• The result is an array, not a range, so COUNTIF, SUMIFS, and the other RACON functions won't accept it inside a formula. They do accept a reference to the result's spill range on the worksheet, like F5#.
• HSTACK is available in Excel 2024+ and Excel 365, not Excel 2021. In Excel 2021, you can combine two columns of the same size with CHOOSE({1,2},range1,range2), as explained in Combine ranges with CHOOSE.