HSTACK function
Quick answer
HSTACK places ranges side by side to build one table, so you can pick columns that aren't next to each other, put them in a new order, or add a calculated column, all in one formula that updates with the source.
- Syntax
=HSTACK(array1, [array2], ...)- Most common use
=HSTACK(A2:A5,D2:D5)returns Ava Ruiz (Two columns that aren't next to each other)
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- One array with the inputs side by side. Shorter inputs are padded with #N/A. It spills.
Syntax
=HSTACK(array1, [array2], ...)
| Argument | Required? | What it is |
|---|---|---|
array1 | Required | The first range or array. |
array2 | Optional | The next range to place to the right. Add as many as you need, separated by commas. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =HSTACK(A2:A5,D2:D5) | Ava Ruizspills 4 × 2 | Two columns that aren't next to each other |
| I2 | =HSTACK(C2:C5,A2:A5) | ava@example.comspills 4 × 2 | Columns in a new order: email, then name |
| L2 | =SORT(HSTACK(A2:A5,D2:D5),2,-1) | Chloe Parkspills 4 × 2 | The two-column table sorted by salary |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | Dept | Salary | Name and salary | Email first | Highest paid first | |||||||
| Ava Ruiz | Sales | ava@example.com | $68,000 | Ava Ruiz | $68,000 | ava@example.com | Ava Ruiz | Chloe Park | $82,000 | |||
| Ben Cole | Support | ben@example.com | $54,000 | Ben Cole | $54,000 | ben@example.com | Ben Cole | Ava Ruiz | $68,000 | |||
| Chloe Park | Finance | chloe@example.com | $82,000 | Chloe Park | $82,000 | chloe@example.com | Chloe Park | Dev Shah | $61,000 | |||
| Dev Shah | Sales | dev@example.com | $61,000 | Dev Shah | $61,000 | dev@example.com | Dev Shah | Ben Cole | $54,000 |
Practice HSTACK
Graded instantly, then re-checked on changed data.
Name and revenue
F2, return a two-column table: each product name from A2:A6 next to its revenue, units times price (B2:B6 times C2:C6).Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A at the bottom of a column | One range is shorter than the others. Make every range cover the same rows. |
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |
| #NAME? | HSTACK needs Excel 2024 or Microsoft 365. |
Tips
- Add a calculated column on the fly: =HSTACK(A2:A9,B2:B9*C2:C9).
- Feed it to FILTER to return non-adjacent columns: =FILTER(HSTACK(A2:A5,D2:D5),B2:B5="Sales").
Questions
How do I return non-adjacent columns with FILTER?
Build the source with HSTACK first: =FILTER(HSTACK(A2:A9,D2:D9),B2:B9="Sales") returns names and salaries for the Sales team.
What is the difference between HSTACK and the & operator?
& joins text into one cell. HSTACK keeps each value in its own cell and returns a table with several columns.
Lessons that use HSTACK
Answers that use HSTACK
In other languages
HSTACK is APILARH in Spanish Excel and HSTAPELN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .