Excel Lesson 25 – HSTACK | Dataplexa

Lesson 25 · Advanced Practical

Array Assembly — HSTACK, CHOOSECOLS and CHOOSEROWS

Lesson 24 introduced VSTACK and HSTACK as a pair. This lesson goes deeper into the horizontal side of array assembly — and introduces two close companions: CHOOSECOLS and CHOOSEROWS. These four functions together give you complete control over how you select, reorder, and assemble columns and rows from any range. They are the building blocks of dynamic report tables that pull exactly the right columns in exactly the right order from one or more source ranges, without ever needing to restructure the source data.

HSTACK — A Deeper Look

You already know that HSTACK places arrays side by side. The key thing to understand about why it is so powerful is that each argument to HSTACK can itself be a formula result — a FILTER output, a SORT result, a CHOOSECOLS selection, or a calculated column. This means HSTACK is not just a join tool; it is an assembly tool for building exactly the table you want from dynamic pieces.

=HSTACK(array1, array2, [array3], ...)

Each argument can be:
  - A plain range:           A2:A10
  - A formula result:        SORT(A2:C10, 3, -1)
  - A calculated column:     {"Bonus"; A2:A10 * 0.1}
  - A literal value column:  SEQUENCE(10)
  - A CHOOSECOLS selection:  CHOOSECOLS(A2:F10, 1, 3, 5)

All arguments must have the same number of rows.
Column widths can differ — each argument contributes however many columns it has.

The practical implication: you can assemble a custom-column report — picking only the columns you need, in the order you want, from a source that has many more columns — entirely in one formula chain. No manual column hiding, no restructuring of the source data, no VLOOKUP to bring columns across.

CHOOSECOLS — Pick and Reorder Columns

CHOOSECOLS returns a selection of columns from an array, in the order you specify. You pass column index numbers — positive counts from the left, negative counts from the right — and the function returns just those columns. This is the clean way to subset or reorder columns from a wide source table.

=CHOOSECOLS(array, col_num1, [col_num2], ...)

  array     →  The source range to pick columns from
  col_num1  →  Index of first column to return (1 = leftmost, -1 = rightmost)
  col_num2  →  Additional column indices (in any order you choose)

Source table A2:F10 has: Name | Dept | Location | Salary | Start Date | Manager

Return only Name and Salary (columns 1 and 4):
=CHOOSECOLS(A2:F10, 1, 4)

Return Name, Salary, and Dept — reordered:
=CHOOSECOLS(A2:F10, 1, 4, 2)

Return the last column (Manager) using negative index:
=CHOOSECOLS(A2:F10, -1)

Return columns in reverse order:
=CHOOSECOLS(A2:F10, 6, 5, 4, 3, 2, 1)

CHOOSECOLS — Selecting 3 Columns From a Wide Table

Source — 6 columns (A2:F5)

Name col 1	Dept col 2	Location col 3	Salary col 4	Start col 5	Manager col 6
Priya	Finance	London	£61,000	2021-03	Singh
James	HR	Leeds	£55,000	2020-07	Patel

=CHOOSECOLS(A2:F5, 1, 4, 6) — return Name, Salary, Manager

Name	Salary	Manager
Priya	£61,000	Singh
James	£55,000	Patel

Dept, Location, and Start Date columns skipped entirely — no source restructuring needed

CHOOSEROWS — Pick and Reorder Rows

CHOOSEROWS is CHOOSECOLS flipped on its axis. Where CHOOSECOLS selects specific columns, CHOOSEROWS selects specific rows — by index number. This is useful for extracting header rows, pulling specific records by position, building custom row orders, or extracting the first and last rows of a dataset together.

=CHOOSEROWS(array, row_num1, [row_num2], ...)

  array     →  The source range to pick rows from
  row_num1  →  Index of first row to return (1 = top row, -1 = last row)
  row_num2  →  Additional row indices in any order

Source A1:D5 — a 5-row table including a header

Return just row 1 (header) and row 3:
=CHOOSEROWS(A1:D5, 1, 3)

Return the first and last data rows:
=CHOOSEROWS(A2:D10, 1, -1)

Extract top 3 rows of a sorted table:
=CHOOSEROWS(SORT(A2:C20, 3, -1), 1, 2, 3)

Reverse the row order of a table:
=CHOOSEROWS(A2:D6, 5, 4, 3, 2, 1)

CHOOSEROWS(A2:D10, 1, -1) → first data row and last data row returned together

The most useful CHOOSEROWS pattern in practice is extracting the top N rows from a SORT result — effectively building a Top 3 or Top 5 table. Combined with SORT and HSTACK, this produces a complete ranked view in a single formula chain.

Building a Custom Report Table — HSTACK + CHOOSECOLS

The combination of HSTACK and CHOOSECOLS lets you build a report table that shows exactly the columns you want, in the order you want, from one or more wide source tables — with calculated columns added in wherever they are needed. This is the cleanest way to produce a formatted output table from raw data without touching the source.

Source employee table A2:F50 — Name, Dept, Location, Salary, Start Date, Manager

Report: Name | Dept | Salary | Bonus (10%) | Manager
The source has no Bonus column — we add it as a calculated piece.

=HSTACK(
  CHOOSECOLS(A2:F50, 1, 2),        (Name, Dept)
  CHOOSECOLS(A2:F50, 4),           (Salary)
  CHOOSECOLS(A2:F50, 4) * 0.1,    (Bonus — calculated on the fly)
  CHOOSECOLS(A2:F50, 6)            (Manager)
)

Custom Report — HSTACK Assembling Columns + Calculated Bonus

CHOOSECOLS(,1,2) + CHOOSECOLS(,4) + col4 * 0.1 + CHOOSECOLS(,6)

Name	Dept	Salary	Bonus ✦	Manager
Priya	Finance	£61,000	£6,100	Singh
James	HR	£55,000	£5,500	Patel
Sarah	Ops	£48,000	£4,800	Singh

✦ Bonus column does not exist in source — calculated inline by HSTACK

Top N Records — CHOOSEROWS + SORT

One of the most useful CHOOSEROWS patterns is extracting the top N rows from a sorted result. This gives you a dynamic Top 3, Top 5, or Top 10 table that updates automatically as data changes — without any manual filtering or row selection.

Sales data A2:C20 — sorted by column 3 descending inside SORT

Top 3 performers:
=CHOOSEROWS(SORT(A2:C20, 3, -1), 1, 2, 3)

Top 5 performers:
=CHOOSEROWS(SORT(A2:C20, 3, -1), 1, 2, 3, 4, 5)

Bottom 3 performers (last 3 rows):
=CHOOSEROWS(SORT(A2:C20, 3, -1), -3, -2, -1)

Dynamic top N using SEQUENCE:
For a variable N stored in cell F1:
=CHOOSEROWS(SORT(A2:C20, 3, -1), SEQUENCE(F1))

Top 3 Table — CHOOSEROWS(SORT(data, 3, -1), 1, 2, 3)

Rep	Region	Sales
🥇 Priya	North	£119,000
🥈 James	East	£117,000
🥉 David	North	£114,000

Only 3 rows returned from a 20-row dataset — updates automatically when data changes

Complete Assembly Pattern — The Full Pipeline

The real power of these functions emerges when you chain them all together. A single formula can stack multiple sources, filter to a subset, select specific columns, add calculated columns, and return a clean ranked report — entirely dynamically.

Scenario:
  - Two quarterly tables: Q1 (Sheet1!A2:F20) and Q2 (Sheet2!A2:F20)
  - Columns: Name | Dept | Region | Sales | Target | Manager
  - Goal: North region only, top 5 by sales, showing Name + Sales + Bonus + Manager

Step 1 — Combine quarters:
AllData = VSTACK(Sheet1!A2:F20, Sheet2!A2:F20)

Step 2 — Filter to North:
NorthData = FILTER(AllData, CHOOSECOLS(AllData,3)="North")

Step 3 — Sort by sales:
Sorted = SORT(NorthData, 4, -1)

Step 4 — Top 5 rows:
Top5 = CHOOSEROWS(Sorted, 1,2,3,4,5)

Step 5 — Pick columns + add bonus:
=HSTACK(
  CHOOSECOLS(Top5, 1),        Name
  CHOOSECOLS(Top5, 4),        Sales
  CHOOSECOLS(Top5, 4)*0.1,   Bonus
  CHOOSECOLS(Top5, 6)         Manager
)

All 5 steps as one nested formula:
=HSTACK(
  CHOOSECOLS(CHOOSEROWS(SORT(FILTER(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),CHOOSECOLS(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),3)="North"),4,-1),1,2,3,4,5),1),
  CHOOSECOLS(CHOOSEROWS(SORT(FILTER(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),CHOOSECOLS(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),3)="North"),4,-1),1,2,3,4,5),4),
  CHOOSECOLS(CHOOSEROWS(SORT(FILTER(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),CHOOSECOLS(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),3)="North"),4,-1),1,2,3,4,5),4)*0.1,
  CHOOSECOLS(CHOOSEROWS(SORT(FILTER(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),CHOOSECOLS(VSTACK(Sheet1!A2:F20,Sheet2!A2:F20),3)="North"),4,-1),1,2,3,4,5),6)
)

Pro tip: use LET() to name each step and avoid repeating the nested chain.

Top 5 North reps from both quarters: Name | Sales | Bonus | Manager — 4 columns, 5 rows, fully dynamic

Pipeline — 5 Steps From 2 Sources to Final Report

VSTACK	Combine Q1 + Q2 into 40 rows	40 rows
FILTER	Keep North region only	14 rows
SORT	Rank by Sales descending	14 rows
CHOOSEROWS	Extract top 5	5 rows
HSTACK + CHOOSECOLS	Select cols + add Bonus	4 cols

Section III — Advanced Functions Quick Reference

You have now completed all five Advanced Functions lessons. Here is a quick reference showing what each function does and the key pattern to remember for each.

Function	What it does	Key pattern
FILTER	Returns rows matching conditions	(A)*(B) AND · (A)+(B) OR
SORT / SORTBY	Sorts a range; SORTBY sorts by external array	SORTBY(arr, key, -1)
UNIQUE	Returns deduplicated values	SORT(UNIQUE(col))
VSTACK	Stacks ranges top-to-bottom	SORT(VSTACK(Q1,Q2,Q3))
HSTACK	Joins ranges left-to-right	HSTACK(SEQUENCE(n), SORT(...))
CHOOSECOLS	Selects and reorders columns	CHOOSECOLS(arr, 1, 4, 6)
CHOOSEROWS	Selects and reorders rows	CHOOSEROWS(SORT(...),1,2,3)

💡 Teacher's Note

When you find yourself writing the same nested expression three or four times inside a long HSTACK formula — which is very common when you are picking multiple columns from the same intermediate result — that is the signal to use LET(). LET lets you name intermediate results and reuse them. For example: =LET(all, VSTACK(Q1,Q2), north, FILTER(all, CHOOSECOLS(all,3)="North"), top5, CHOOSEROWS(SORT(north,4,-1),1,2,3,4,5), HSTACK(CHOOSECOLS(top5,1), CHOOSECOLS(top5,4), CHOOSECOLS(top5,4)*0.1, CHOOSECOLS(top5,6))). The formula is far more readable, and each stage is calculated once rather than repeated. LET is one of the most underused functions in Excel and it pays dividends exactly in situations like this.

🟠 Practice

Q1. Source data is in A2:F20. The columns are: Name, Dept, Region, Sales, Target, Manager. Write a CHOOSECOLS formula that returns only Name (col 1), Sales (col 4), and Manager (col 6).

Q2. Write a formula that returns the top 3 rows from a table A2:C20 sorted by column 3 descending.

Q3. You have Name in column A (A2:A10) and Salary in column D (D2:D10). Write an HSTACK formula that combines them side by side and adds a Bonus column calculated as Salary multiplied by 0.15.

Next up — Power Query Introduction, where we move beyond formulas entirely and into Excel's dedicated data transformation engine — the tool built specifically for importing, cleaning, and reshaping data at scale.

← Previous Course Index Next →

Excel Course