|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

HSTACK

HSTACK

  • Last updated on

Description

Use this function to horizontally stack multiple ranges or arrays into a single range.

Syntax

HSTACK(array_1, [array_2], …,[array_29])

Inputs

This function accepts the following argument:

Name Required Description Valid Input
array_1 Yes The first range or array to horizontally stack. A cell, a cell range, or a formula which results in either of these.
[array_2], …, [array_29] No Additional ranges or arrays to horizontally stack. Up to 29 arrays can be added. A cell reference, a range, or a formula which results in either of these.

 

Examples

Sample data

A B C D E F
1 Name Age Salary Title City Years
2 John 35 75000 Pilot New York 9
3 Alice 28 65000 Medic Chicago 2
4 Bob 42 85000 Manager Los Angeles 5
5 George 22 55000 Attendant Atlanta 1
6 Helen 30 71000 Jr. Pilot New York 8

 

Sample formulas

Ranges must match! If you are using multiple ranges with FILTER, the row values must be the same for all ranges specified as part of the filter expression.
Use Case Formula Explanation and Result
Create a data set listing only Name, Title, and City. =HSTACK(A1:A6,D1:E6)

This formula extracts the Name (A), Title (D) and City (E) columns into a new set, along with the column heads.
To not include the column heads, the formula would start using row 2 rather than row 1. For this data set it would return the following:

Name Title City
John Pilot New York
Alice Medic Chicago
Bob Manager Los Angeles
George Attendant Atlanta
Helen Jr. Pilot New York
Have a text string for an error if there is a mismatch in the formula. =IFERROR(HSTACK(A1:A4, {"City"}), "Invalid")

This formula uses IFERROR to handle mismatched array sizes gracefully. For this data set it would return the following:

Name City
John Invalid
Alice Invalid
Bob Invalid

 

Notes

  • As with all array functions, HSTACK dynamically populates results across adjacent cells, starting from the cell containing the HSTACK function.
  • HSTACK:
    • Combines multiple ranges horizontally into a single range.
    • Can stack ranges with different heights.
    • Is useful for combining data from multiple columns or sources.

 

Tips

  • If you have header row(s), start your array values one row down (for example in the above data set, start with A2 rather than A1) if you don't want the headers to be included.
  • The order of the arrays determines the order of stacking. The first array in the function will always be at the left.
  • HSTACK can be effectively combined with several other functions, including:
    • FILTER: To select and combine filtered data from different ranges.
    • UNIQUE: To remove duplicates from stacked arrays.
    • SORT: To organize data before or after stacking.
    • SEQUENCE: To add sequential numbering or indexing.
    • SUM: To calculate aggregated totals from stacked results.

Related functions

Here are the other array formula functions supported in Workiva spreadsheets. 

DescriptionUse this function to horizontally stack multiple ranges or arrays into a single range.SyntaxHSTACK(array_1...