HSTACK joins two or more arrays side by side into a single wider array. It is the horizontal counterpart to VSTACK, making it easy to combine columns without manual copying.
=HSTACK(array1, [array2], …)What does HSTACK do?
HSTACK is a Lookup & reference function that takes two or more arrays and places them next to each other horizontally, returning one combined array. Think of it as stacking columns like books on a shelf: the first array goes on the left, the second immediately to its right, and so on.
Before HSTACK, combining columns from different places meant copying and pasting, or writing complex formulas with INDEX and CHOOSE. HSTACK does it in one step and works with ranges, arrays, and even dynamic arrays from other functions. It is especially useful when you need to build a report table from separate lists, such as names from one column and sales from another.
A common real situation: you have a list of products in column A and their prices in column C, with other data in between. You want a clean two-column table of product and price. =HSTACK(A2:A10, C2:C10) gives you exactly that, and the result updates automatically if the source data changes.
When to use HSTACK
- Combine separate columns into one table without copy-paste.
- Build a report table from non-adjacent columns.
- Join a list of names with a list of scores side by side.
- Merge dynamic array results from two formulas horizontally.
- Create a single array for further processing with functions like SORT or FILTER.
- Rearrange columns when the original order is not suitable for analysis.
HSTACK syntax
=HSTACK(array1, [array2], …)| Argument | Required? | What to enter |
|---|---|---|
array1 | Required | The first array or range to include. This becomes the leftmost columns of the result. Example: A2:A10. |
array2 | Optional | The second array or range to place to the right of array1. You can add more arrays after this. Example: C2:C10. |
What it returns: A single array that contains all the input arrays placed side by side horizontally. The result has the same number of rows as the tallest input array, and the total number of columns is the sum of the columns in all input arrays. Empty cells are returned where an input array has fewer rows.
HSTACK examples
1 Combine two columns into one table
You have a list of first names in column A and last names in column B. You want a single table with both names side by side. The formula =HSTACK(A2:A4, B2:B4) takes the range A2:A4 as the first array and B2:B4 as the second, and returns a 3-row by 2-column array. The first column contains the first names, the second column contains the last names.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First Name | Last Name | |||
| 2 | John | Smith | John | ||
| 3 | Mary | Johnson | |||
| 4 | Peter | Brown | |||
| 5 |
2 Join product and price from non-adjacent columns
Products are in column A and prices in column C, with a category column B in between. You want a clean two-column list of product and price. =HSTACK(A2:A5, C2:C5) uses A2:A5 as the first array and C2:C5 as the second. The result is a 4-row by 2-column array where the first column is product names and the second is prices. The category column is ignored.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | |||
| 2 | Laptop | Electronics | 1200 | Laptop | ||
| 3 | Mouse | Accessories | 25 | |||
| 4 | Keyboard | Accessories | 45 | |||
| 5 | Monitor | Electronics | 300 | |||
| 6 |
3 Add a calculated column next to data
You have quantities in column A and unit prices in column B. You want a table that includes the original columns plus a total column calculated as quantity times price. =HSTACK(A2:A4, B2:B4, A2:A4*B2:B4) stacks the quantity range, the price range, and the product of the two ranges. The result is a 3-row by 3-column array: quantity, price, and total.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quantity | Unit Price | |||
| 2 | 10 | 5 | 10 | ||
| 3 | 4 | 12 | |||
| 4 | 7 | 3 | |||
| 5 |
4 Combine employee details from separate lists
You have employee IDs in column A, names in column B, and departments in column C. You want a single table with ID, name, and department side by side. =HSTACK(A2:A5, B2:B5, C2:C5) stacks the three ranges horizontally. The result is a 4-row by 3-column array with IDs, names, and departments in that order.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Name | Department | |||
| 2 | 101 | Alice | HR | 101 | ||
| 3 | 102 | Bob | IT | |||
| 4 | 103 | Carol | Finance | |||
| 5 | 104 | David | IT | |||
| 6 |
5 Build a sales report table with dates and amounts
You have sales dates in column A, sales amounts in column B, and regions in column C. You want a report table with date, region, and amount in that order. =HSTACK(A2:A6, C2:C6, B2:B6) rearranges the columns so that region comes before amount. The result is a 5-row by 3-column array with dates, regions, and amounts.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Amount | Region | |||
| 2 | 2024-01-05 | 1500 | North | 45296 | ||
| 3 | 2024-01-06 | 2200 | South | |||
| 4 | 2024-01-07 | 1800 | East | |||
| 5 | 2024-01-08 | 2500 | West | |||
| 6 | 2024-01-09 | 1900 | North | |||
| 7 |
Common HSTACK errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One or more arguments are not arrays or ranges, such as a text string that cannot be interpreted as an array. | Ensure every argument is a valid range, array constant, or reference. For example, use A2:A10 instead of "A2:A10". |
| #N/A | The arrays have different numbers of rows and Excel cannot align them, often because one array is a single cell and another is a multi-row range. | Make sure all arrays have the same number of rows, or use a single cell that will be broadcast. For example, HSTACK(A2:A10, B2) will repeat B2 for each row. |
| #NAME? | The function name is misspelled or the version of Excel does not support HSTACK. | Check the spelling: HSTACK. Ensure you are using Excel 365 or Excel 2021 or later. |
| #SPILL! | The result array cannot spill because there are already values in the cells where it needs to expand. | Clear the cells to the right and below the target cell, or move the formula to an empty area. |
Tips for using HSTACK
- Use HSTACK to rearrange columns without copying and pasting; the result updates automatically when source data changes.
- Combine HSTACK with SORT or FILTER to create dynamic reports that reorder or filter columns.
- If you need to stack vertically, use VSTACK instead.
- You can include calculated arrays, such as
A2:A10*B2:B10, directly in HSTACK. - When combining arrays with different row counts, HSTACK pads with #N/A; ensure row counts match for clean results.
Frequently asked questions
What is the difference between HSTACK and VSTACK?
HSTACK joins arrays side by side horizontally, adding columns. VSTACK joins arrays vertically, adding rows.
Can HSTACK combine more than two arrays?
Yes, you can provide up to 253 arrays. They are placed left to right in the order given.
Does HSTACK work with dynamic arrays?
Yes, HSTACK accepts dynamic arrays and will spill the combined result. It is designed to work with Excel's dynamic array engine.
What happens if the arrays have different numbers of rows?
HSTACK returns #N/A for missing rows in shorter arrays. To avoid this, ensure all arrays have the same number of rows or use a single value that can be broadcast.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. HSTACK reference