Can HSTACK merge data from different sheets?

No, HSTACK only combines arrays from the same worksheet. To merge data from other sheets, use INDIRECT or sheet name references to pull the data first, then apply HSTACK to the resulting arrays within the current sheet.

What happens if my arrays have different numbers of rows?

HSTACK pads shorter arrays with blank cells to match the tallest array's height. All data aligns to the top of each column, with blanks filling the bottom of shorter columns. This makes it safe to merge datasets with varying row counts.

Can I rearrange my columns when using HSTACK?

Yes—change the order of your arguments in the formula. For example, =HSTACK(B2:B6, A2:A6) places Priority before Ticket ID, reversing their original order. You have complete control over column sequence through argument order.

How is HSTACK different from using a pivot table?

HSTACK is a lightweight dynamic array formula that merges columns instantly, while pivot tables require more setup and are designed for data summarization and grouping. For simple column merging and combining datasets, HSTACK is faster and more direct, though pivot tables are more powerful for complex analytics.

HSTACK function

HSTACK combines multiple arrays horizontally (side by side) into a single rectangular array, expanding shorter arrays with blanks to create a unified output.

=HSTACK(array1, ...)

Generate a HSTACK formula

Describe what you need. The generator will reach for HSTACK where HSTACK is the right tool, and tell you when it is not.

Arguments

How HSTACK reads its arguments

Argument	Required	Description
array1	Required	The first array or range to stack horizontally. Required parameter. Can be a single cell, a multi-cell range like A2:A6, or an array expression created by another function.
...	Repeating	Additional arrays or ranges to stack. Optional and repeating—you can pass 2 to 254 total arrays. Each is placed immediately to the right of the previous one. If arrays differ in height, HSTACK pads shorter ones with blanks, aligning all data to the top.

Returns

Returns a single rectangular array with all input arrays merged left-to-right, handling different heights by padding shorter arrays with blank cells.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Combine ticket ID with priority

ID	Priority
T001	High
T002	Medium
T003	Low
T004	High
T005	Medium

=HSTACK(A2:A6, B2:B6)

HSTACK takes the ID column and Priority column and places them side by side in a single output array, preserving all 5 rows without modifying the original data. The result is a rectangular array ready for reporting or further processing.

2. Merge ticket ID, agent, and satisfaction score

ID	Agent	CSAT
T001	Alice	5
T002	Bob	4
T003	Alice	5
T004	Carol	3
T005	Bob	4

=HSTACK(A2:A6, E2:E6, F2:F6)

Result: A 5×3 array: T001|Alice|5, T002|Bob|4, T003|Alice|5, T004|Carol|3, T005|Bob|4

HSTACK combines three columns (A, E, F) that are scattered throughout the spreadsheet, pulling them together into one consolidated array. Non-adjacent columns are handled identically to adjacent ones—the function simply takes each array in order and places it to the right of the previous one.

3. Add a calculated resolution time column

ID	Opened	Closed
T001	2026-01-15	2026-01-16
T002	2026-01-15	2026-01-18
T003	2026-01-16	2026-01-17
T004	2026-01-16	2026-01-20
T005	2026-01-17	2026-01-19

=HSTACK(A2:A6, DAYS(D2:D6, C2:C6))

Result: A 5×2 array: T001|1, T002|3, T003|1, T004|4, T005|2

HSTACK combines ticket IDs with a calculated field created by the DAYS function, which computes the duration between opened and closed dates for each row. The result dynamically shows each ticket alongside its resolution time in days, all merged into one rectangular output.

Common errors

Which HSTACK error are you seeing?

Error	Why it happens	How to fix it
#SPILL!	The result array needs to spill into cells that already contain data. HSTACK requires empty space to the right and below the formula cell to output the entire merged array.	Clear or delete contents of cells where the array will spill, or place the formula in a location with empty cells below and to the right. Alternatively, use Ctrl+Shift+Enter to confirm the range where results will appear.
#VALUE!	One or more arguments are not valid arrays or ranges, such as a cell containing #N/A or a reference to a single value that cannot be interpreted as an array.	Ensure all inputs are valid ranges (like A2:A6) or array expressions. Check source data for error values and resolve them before using HSTACK. All inputs must return array-shaped results.
#REF!	One of the input ranges references cells that have been deleted, moved, or are otherwise invalid in the spreadsheet.	Update the formula to point to valid cell ranges, or restore deleted rows and columns in the source data. Verify that all range references in the formula still exist.

Tips and when to use something else

HSTACK only stacks horizontally (left to right). To stack arrays vertically (rows stacked on top of rows), use VSTACK instead. For rotating data 90 degrees, use TRANSPOSE.
When arrays have different heights, HSTACK pads shorter ones with blanks to match the longest array, always aligning data to the top. This makes it safe to merge incomplete or partial datasets.
HSTACK combines arrays as-is without sorting or filtering. Use FILTER, SORT, or UNIQUE before HSTACK if you need to reorder data, remove duplicates, or extract a subset before merging.
Combine HSTACK with dynamic functions like FILTER or UNIQUE to keep merged arrays in sync as your source data changes. The output automatically updates whenever the underlying data updates.

Related functions

VSTACKVSTACK combines multiple arrays or ranges by stacking them vertically into a single array, placing each input one below the other.TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.UNIQUEReturns all distinct values from an array, removing duplicates by row or column; use it to extract clean, unique item lists from repetitive data.TOROWTOROW converts a multi-dimensional array into a single horizontal row, arranging all elements left-to-right, top-to-bottom by default.

Frequently asked questions

Can HSTACK merge data from different sheets?: No, HSTACK only combines arrays from the same worksheet. To merge data from other sheets, use INDIRECT or sheet name references to pull the data first, then apply HSTACK to the resulting arrays within the current sheet.
What happens if my arrays have different numbers of rows?: HSTACK pads shorter arrays with blank cells to match the tallest array's height. All data aligns to the top of each column, with blanks filling the bottom of shorter columns. This makes it safe to merge datasets with varying row counts.
Can I rearrange my columns when using HSTACK?: Yes—change the order of your arguments in the formula. For example, =HSTACK(B2:B6, A2:A6) places Priority before Ticket ID, reversing their original order. You have complete control over column sequence through argument order.
How is HSTACK different from using a pivot table?: HSTACK is a lightweight dynamic array formula that merges columns instantly, while pivot tables require more setup and are designed for data summarization and grouping. For simple column merging and combining datasets, HSTACK is faster and more direct, though pivot tables are more powerful for complex analytics.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17