HSTACK in Google Sheets

What is HSTACK in Google Sheets?

The HSTACK function in Google Sheets allows you to horizontally combine arrays or ranges by placing them side by side into a single, extended row or multi-column output. Instead of stacking data top-to-bottom like VSTACK Google Sheets function, this function arranges the information left-to-right, making it easier to compare or organize data across columns.

For example, if we’re compiling a list of scores from two different exams. The scores from Exam 1 are in cells A1:A3 and those from Exam 2 are in B1:B3.

=HSTACK(A2:B2, A3:B3, A4:B4) will display the scores in one row, starting with the values from column A followed by those from column B, all aligned in a single row.

Syntax

Let’s break down the syntax for HSTACK in Google Sheets:

=HSTACK(range1, [range2, …])

• range1: The initial array or set of cells to appear in the combined row.
• [range2, …] (Optional): Additional arrays or ranges that will be placed to the right of the previous range.
  You can stack as many ranges as needed, separated by commas.

How to Use HSTACK function in Google Sheets?

The HSTACK function is useful when you’re trying to combine values from different columns into a continuous horizontal layout. It eliminates manual copy-pasting and automatically updates if the source data changes. Here’s how to use it:

Enter HSTACK in Google Sheets Manually

Step 1: Open a spreadsheet. And type the names of students in Class A in Column A and below that, enter the students of Class B.

Step 2: Choose a blank cell where you’d like to display the result. Here, we choose D1.

Step 3: Start typing the formula by entering =HSTACK(.

Step 4: Now include the cell ranges to merge. Here, we write

=HSTACK(A1:A5, A6:A10)

This formula makes the names from A1:A5 and A6:A10 fit into rows, starting in cell D1.

Step 5: Press Enter.

You’ll now see the data from Class A followed by Class B, spread across a row horizontally.

Using the Menu Bar

1. Select a blank cell for the output.
2. Begin typing =HSTACK(. Google Sheets will suggest the function.
3. Press Tab or click on the suggestion.
4. Enter your desired ranges inside the brackets, e.g., A2:A4, B2:B4.
5. Press Enter and the horizontally merged result will appear.

Examples

Let us explore a few real-life examples to see how the HSTACK function can be applied effectively. These examples demonstrate how HSTACK can be used to merge data across columns, helping you maintain a structured horizontal view of information in Google Sheets.

Example #1

We have the exam scores for two different semesters stored in two separate sheets. You want to display the scores side by side in a summary sheet for comparison.

Step 1: Ensure the data is neatly arranged in each sheet. In the Semester1 sheet, student names and scores are in cells A2:B6. In the Semester2 sheet, student names and scores are in cells A2:B6 as well.

Step 2: Enter the following formula in D1 to combine the data from both semesters horizontally:

=HSTACK(Semester1!A2:B6, Semester2!A2:B6)

Here:

• Semester1!A2:B6 refers to the names and scores from the first semester.
• Semester2!A2:B6 refers to the names and scores from the second semester.

Step 3: Press Enter. The result will show the entire Semester 1 data followed by Semester 2 data, all arranged horizontally.

Now, you have a side-by-side comparison of students’ scores across two semesters — all in a single, organized row layout.

HSTACK is especially useful in scenarios like performance tracking or departmental reporting, where you want to align similar data types horizontally. It saves time, avoids manual errors from copy-pasting, and automatically reflects changes if the original data is updated.

Example #2 – Using HSTACK with INDIRECT Function

In this example, a business owner maintains sales records for different regions in separate named sheets: NorthRegion and SouthRegion. They want to create a view that places both data sets side by side using HSTACK in Google Sheets, and they also want the flexibility to change the sheet names dynamically without editing the formula each time.

Step 1: In the NorthRegion sheet, sales figures are listed in cells A2:A6. In the SouthRegion sheet, sales figures are listed in cells A2:A6 as well.

Now, in the summary sheet, use two helper cells (e.g., B1 and B2) to enter the sheet names as plain text:

• B1: NorthRegion
• B2: SouthRegion

Step 2: Click on a blank cell where you want the combined horizontal list to come and enter the following formula:

=HSTACK(INDIRECT(B1 & “!A2:A6”), INDIRECT(B2 & “!A2:A6”))

• INDIRECT(B1 & “!A2:A6”) pulls the range A2:A6 from the sheet named in cell B1.
• INDIRECT(B2 & “!A2:A6”) does the same for the sheet named in cell B2.
• HSTACK then places the two columns of sales data side by side in a horizontal layout.

Step 3:Press Enter. The output will display two side-by-side columns — one from NorthRegion, the other from SouthRegion — all on one row in the summary sheet.

This dynamic combination of HSTACK and INDIRECT allows the business owner to easily switch between regional data just by changing the sheet names in cells B1 and B2. It’s an efficient way to manage and compare data from multiple sources without rewriting formulas.

Example #3 – Using HSTACK with SORT Function

In this example, a professor maintains two separate lists: one containing student names and the other containing their corresponding scores. These lists are stored in separate columns. The teacher wants to display the names and scores side by side, sorted in ascending order of scores for a clean summary. We use HSTACK with the SORT function for this requirement.

Step 1: Enter the student names in Column A (A2:A6) and their scores in Column B (B2:B6). For example:

Step 2: Select a blank cell where you want the sorted horizontal data to appear. Here, we select cell D2.

=HSTACK(SORT(A2:A6, B2:B6, TRUE), SORT(B2:B6, B2:B6, TRUE))

• SORT(A2:A6, B2:B6, TRUE) sorts the names based on the corresponding scores in ascending order.
• SORT(B2:B6, B2:B6, TRUE) sorts the scores in ascending order.
• HSTACK places the sorted names and sorted scores next to each other, forming a horizontal view of each row’s sorted values.

Step 3: Press Enter. The result will display the names and their scores, sorted by score, in side-by-side columns starting from cell D2.

This combination of HSTACK with SORT is particularly useful for quick reporting or analysis. It allows users to cleanly organize related data sets side by side while maintaining a logical order — in this case, ascending scores. It’s a great technique for creating dynamic, auto-sorting summaries that update automatically as the underlying data changes.

Important Things to Note

1. All input ranges in HSTACK should ideally have the same number of rows; otherwise, Google Sheets will pad shorter ranges with blank cells to align with the longest one.
2. If any cell in the input ranges contains an error, such as #REF! or #DIV/0!, the error will appear in the final output exactly as it is.
3. HSTACK does not automatically remove duplicate values; if needed, you can use the UNIQUE function to filter out repetitions.
4. Blank cells in the source ranges are retained in the result, which may create empty spaces in the horizontally stacked output.
5. Any changes made to the original data ranges are reflected automatically in the HSTACK output, making it useful for real-time updates.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand HSTACK Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is HSTACK Function in Google Sheets. We learn its syntax and how to use it to stack data horizontally with examples. You can learn more from the following articles. –

PRICEDISC in Google Sheets

RECEIVED in Google Sheets

Hour in Google Sheets