Available in:Excel 2024 or later (Works in Excel 2024, every later version, Microsoft 365 and Excel for the web)Category:Dynamic arrays

HSTACK

HOR.STAPELEN in Dutch Excel

Places ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G	H
1	Account	Name	Debit	Credit
2	11001100	Bank	€48,250.00€ 48.250,00	€0.00€ 0,00	11001100=HSTACK(A2:A6, C2:D6)=HSTACK(A2:A6; C2:D6)	€48,250.00€ 48.250,00	€0.00€ 0,00
3	13001300	Receivables	€62,100.00€ 62.100,00	€0.00€ 0,00	13001300	€62,100.00€ 62.100,00	€0.00€ 0,00
4	16001600	Payables	€0.00€ 0,00	€41,350.00€ 41.350,00	16001600	€0.00€ 0,00	€41,350.00€ 41.350,00
5	18001800	VAT payable	€0.00€ 0,00	€7,820.00€ 7.820,00	18001800	€0.00€ 0,00	€7,820.00€ 7.820,00
6	80008000	Revenue	€0.00€ 0,00	€61,180.00€ 61.180,00	80008000	€0.00€ 0,00	€61,180.00€ 61.180,00

=HSTACK(A2:A6, C2:D6)

=HSTACK(A2:A6; C2:D6)

What HSTACK does and when to use it

What it does: it glues ranges together sideways into one block, for example an account list with its debit and credit columns next to it.

When to use it: when you build a table from parts that are calculated separately, such as a UNIQUE list with SUMIFS totals beside it, or two specifications side by side. To stack ranges below each other use VSTACK.

Two main gains:

Stays live: unlike copied columns, the block follows the sources when they change.
Calculated pieces: the parts can be formula results such as UNIQUE and SUMIFS, which a copy cannot hold.

How Excel evaluates it

Excel goes through these steps:

it evaluates each argument into an array
it takes the height of the tallest array as the height of the result
it writes the first array into the leftmost columns, the second directly to its right, and so on
where an array is shorter than the tallest one, it gives the remaining cells #N/A

The block spills from the formula cell, and if any cell in that area is occupied the result is #SPILL!.

Watch out: HSTACK pairs rows by position, not by key: if the pieces are not built from the same list, the totals sit next to the wrong items and Excel shows no error.

Mental model

Bookends on a shelf in a row: each argument is a block of books, placed one after the other from the left, and the shelf is as tall as the tallest block.

Terms used on this page: spill, array, argument, range, cell

Syntax

Official HSTACK documentation

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

HSTACK(array1; [array2]; ...)

Arguments
Argument	Type	Meaning
`array1`	Required	The first range or array; it ends up in the leftmost columns.
`array2`	Optional	Another range or array to place to the right; add as many as you need.

Return value:One array with all inputs side by side; as tall as the tallest input, padded with #N/A

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

Trial balance: account, debit and credit side by side

You received the trial balance of Veldhuis Installatie BV with the account name between the account number and the amounts. For your workpaper you want only the account number followed by the debit and credit columns, as one block that follows the client's file.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G	H
1	Account	Name	Debit	Credit
2	11001100	Bank	€48,250.00€ 48.250,00	€0.00€ 0,00	11001100=HSTACK(A2:A6, C2:D6)=HSTACK(A2:A6; C2:D6)	€48,250.00€ 48.250,00	€0.00€ 0,00
3	13001300	Receivables	€62,100.00€ 62.100,00	€0.00€ 0,00	13001300	€62,100.00€ 62.100,00	€0.00€ 0,00
4	16001600	Payables	€0.00€ 0,00	€41,350.00€ 41.350,00	16001600	€0.00€ 0,00	€41,350.00€ 41.350,00
5	18001800	VAT payable	€0.00€ 0,00	€7,820.00€ 7.820,00	18001800	€0.00€ 0,00	€7,820.00€ 7.820,00
6	80008000	Revenue	€0.00€ 0,00	€61,180.00€ 61.180,00	80008000	€0.00€ 0,00	€61,180.00€ 61.180,00
7

Formula cell (F2)Spilled result

Formula in F2

=HSTACK(A2:A6, C2:D6)

=HSTACK(A2:A6; C2:D6)

Result: 1100 | €48,250.00 | €0.00 / 1300 | €62,100.00 | €0.00 / 1600 | €0.00 | €41,350.00 / 1800 | €0.00 | €7,820.00 / 8000 | €0.00 | €61,180.001100 | € 48.250,00 | € 0,00 / 1300 | € 62.100,00 | € 0,00 / 1600 | € 0,00 | € 41.350,00 / 1800 | € 0,00 | € 7.820,00 / 8000 | € 0,00 | € 61.180,00

Step by step

Excel evaluates the first argument, A2:A6: one column of 5 account numbers, 1100, 1300, 1600, 1800, 8000.
It evaluates the second argument, C2:D6: two columns of 5 rows, debit and credit. Both arguments are 5 rows tall, so nothing needs padding.
The first array goes into the first column of the result (F), the second array directly to its right (G and H). That is 1 + 2 = 3 columns.
The block spills from F2 to H6:
- row 2: 1100, 48,25048.250, 0
- row 3: 1300, 62,10062.100, 0
- row 4: 1600, 0, 41,35041.350
- row 5: 1800, 0, 7,8207.820
- row 6: 8000, 0, 61,18061.180
Change the debit of account 1100 to 50,00050.000 and G2 shows 50,00050.000 at once. The block is a live view, not a copy.

Watch out: If anything is typed in F2:H6, the formula shows #SPILL! instead of the block. Keep the area to the right of and below the formula empty.

More on this example: why it works, 1 variation

Why it works

Each argument is turned into an array first, and HSTACK only decides where its columns go. Because the arguments need not be adjacent in the source, you can skip column B without deleting or hiding it.

Variations

=CHOOSECOLS(A2:D6, 1, 3, 4)

=CHOOSECOLS(A2:D6; 1; 3; 4)

Same block with CHOOSECOLS: shorter when everything comes from one range.

Intermediate

Subledger and general ledger lists side by side

You compare the invoices in the receivables subledger of Veldhuis Installatie BV with the invoices booked in the general ledger. The subledger holds 5 invoices, the ledger extract only 4. You want both lists in one block, and the missing ledger line must read Missing rather than #N/A.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G	H	I
1	Subledger invoice	Subledger amount	GL invoice	GL amount
2	25-1101	€12,100.00€ 12.100,00	25-1101	€12,100.00€ 12.100,00	25-1101=IFNA(HSTACK(A2:B6, C2:D5), "Missing")=IFNA(HSTACK(A2:B6; C2:D5); "Missing")	€12,100.00€ 12.100,00	25-1101	€12,100.00€ 12.100,00
3	25-1102	€4,235.00€ 4.235,00	25-1102	€4,235.00€ 4.235,00	25-1102	€4,235.00€ 4.235,00	25-1102	€4,235.00€ 4.235,00
4	25-1103	€9,680.00€ 9.680,00	25-1103	€9,680.00€ 9.680,00	25-1103	€9,680.00€ 9.680,00	25-1103	€9,680.00€ 9.680,00
5	25-1104	€2,420.00€ 2.420,00	25-1104	€2,420.00€ 2.420,00	25-1104	€2,420.00€ 2.420,00	25-1104	€2,420.00€ 2.420,00
6	25-1105	€6,050.00€ 6.050,00			25-1105	€6,050.00€ 6.050,00	Missing	Missing
7

Formula cell (F2)Spilled result

Formula in F2

=IFNA(HSTACK(A2:B6, C2:D5), "Missing")

=IFNA(HSTACK(A2:B6; C2:D5); "Missing")

Result: 25-1101 | €12,100.00 | 25-1101 | €12,100.00 / 25-1102 | €4,235.00 | 25-1102 | €4,235.00 / 25-1103 | €9,680.00 | 25-1103 | €9,680.00 / 25-1104 | €2,420.00 | 25-1104 | €2,420.00 / 25-1105 | €6,050.00 | Missing | Missing25-1101 | € 12.100,00 | 25-1101 | € 12.100,00 / 25-1102 | € 4.235,00 | 25-1102 | € 4.235,00 / 25-1103 | € 9.680,00 | 25-1103 | € 9.680,00 / 25-1104 | € 2.420,00 | 25-1104 | € 2.420,00 / 25-1105 | € 6.050,00 | Missing | Missing

Step by step

The first argument, A2:B6, is 5 rows tall and 2 columns wide: the subledger invoices and amounts.
The second argument, C2:D5, is only 4 rows tall and 2 columns wide: the ledger invoices and amounts. It stops at row 5 on purpose, because the ledger has no fifth line.
HSTACK takes the taller height, 5, for the result. The first array fills columns F and G. The second fills H and I for 4 rows; in the fifth row the two cells H6 and I6 have nothing to show and get #N/A.
IFNA looks at all 20 cells. 18 hold a value and pass through unchanged. The two #N/A cells are replaced by Missing, so row 6 reads 25-1105, 6,0506.050, Missing, Missing.
Without IFNA the same block would show #N/A in H6 and I6, and a SUM over column I would also return #N/A.

Watch out: If you write C2:D6 for the second argument, the empty cells C6 and D6 come through as 0, and the block shows 0 and 0 instead of #N/A. IFNA then has nothing to replace and the gap looks like a posting of zero.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

HSTACK never drops a row from the taller list; it makes up the difference with #N/A, which marks exactly the places where the shorter list has no data. IFNA replaces only #N/A, so a real error elsewhere in the block, such as #DIV/0!, would still be visible.

More to watch out for

The two lists sit side by side by position only. If the ledger extract is sorted differently, row 3 no longer holds the same invoice on both sides, and the block looks fine but compares the wrong lines.

Variations

=HSTACK(A2:B6, C2:D5)

=HSTACK(A2:B6; C2:D5)

Without IFNA: keep the #N/A when you want the gap to stand out or to count it with COUNTIF later.

Advanced

Purchase invoices per supplier: net amount and VAT in one table

In the VAT recalculation for Veldhuis Installatie BV you have 7 purchase invoices with supplier, net amount and VAT. You want one summary table with each supplier once, the total net amount and the total VAT, built by one formula, so that new suppliers appear in the table by themselves once the ranges cover the new invoices.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G	H
1	Invoice	Supplier	Net amount	VAT
2	25-2101	C2001	€1,000.00€ 1.000,00	€210.00€ 210,00	C2001=HSTACK(UNIQUE(B2:B8), SUMIFS(C2:C8, B2:B8, UNIQUE(B2:B8)), SUMIFS(D2:D8, B2:B8, UNIQUE(B2:B8)))=HSTACK(UNIQUE(B2:B8); SUMIFS(C2:C8; B2:B8; UNIQUE(B2:B8)); SUMIFS(D2:D8; B2:B8; UNIQUE(B2:B8)))	€3,000.00€ 3.000,00	€630.00€ 630,00
3	25-2102	C2002	€2,500.00€ 2.500,00	€525.00€ 525,00	C2002	€3,700.00€ 3.700,00	€777.00€ 777,00
4	25-2103	C2001	€1,500.00€ 1.500,00	€315.00€ 315,00	C2003	€1,200.00€ 1.200,00	€252.00€ 252,00
5	25-2104	C2003	€800.00€ 800,00	€168.00€ 168,00
6	25-2105	C2002	€1,200.00€ 1.200,00	€252.00€ 252,00
7	25-2106	C2001	€500.00€ 500,00	€105.00€ 105,00
8	25-2107	C2003	€400.00€ 400,00	€84.00€ 84,00
9

Formula cell (F2)Spilled result

Formula in F2

=HSTACK(UNIQUE(B2:B8), SUMIFS(C2:C8, B2:B8, UNIQUE(B2:B8)), SUMIFS(D2:D8, B2:B8, UNIQUE(B2:B8)))

=HSTACK(UNIQUE(B2:B8); SUMIFS(C2:C8; B2:B8; UNIQUE(B2:B8)); SUMIFS(D2:D8; B2:B8; UNIQUE(B2:B8)))

Result: C2001 | €3,000.00 | €630.00 / C2002 | €3,700.00 | €777.00 / C2003 | €1,200.00 | €252.00C2001 | € 3.000,00 | € 630,00 / C2002 | € 3.700,00 | € 777,00 / C2003 | € 1.200,00 | € 252,00

Step by step

The innermost function is UNIQUE(B2:B8). It reads the 7 suppliers C2001, C2002, C2001, C2003, C2002, C2001, C2003 and keeps each first occurrence: C2001, C2002, C2003. That is 3 rows, in order of first appearance.
The first SUMIFS gets that list as criteria, so it calculates once per supplier. C2001: 1,0001.000 + 1,5001.500 + 500 = 3,0003.000. C2002: 2,5002.500 + 1,2001.200 = 3,7003.700. C2003: 800 + 400 = 1,2001.200.
The second SUMIFS does the same on the VAT column. C2001: 210 + 315 + 105 = 630. C2002: 525 + 252 = 777. C2003: 168 + 84 = 252.
HSTACK now has three arrays of 3 rows each: the suppliers (1 column), the net totals (1 column) and the VAT totals (1 column). Because UNIQUE gave all three the same order, row 1 of each describes C2001, row 2 C2002, row 3 C2003.
The block spills from F2 to H4: C2001, 3,0003.000, 630; C2002, 3,7003.700, 777; C2003, 1,2001.200, 252. Check: net 3,0003.000 + 3,7003.700 + 1,2001.200 = 7,9007.900 and VAT 630 + 777 + 252 = 1,6591.659, which is 21% of 7,9007.900.
B2:B8 is already full, so an eighth invoice for a new supplier C2004 in row 9 is only picked up after you widen the ranges to row 9 (or use an Excel Table, whose columns grow automatically); then a fourth row appears by itself.

Watch out: A supplier code exported as C2001 with a trailing space counts as a separate supplier in UNIQUE, so you get a fourth row with a partial total and the three-row table still looks plausible. Compare the grand total with the source total.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

UNIQUE and SUMIFS both work on the same ranges, so the list of suppliers and the totals are born in the same order. SUMIFS accepts an array as criteria and then returns one total per element, which is what lets one formula replace a column of copied SUMIFS formulas.

More to watch out for

If you replace one UNIQUE with a sorted copy, for example SORT(UNIQUE(B2:B8)), and leave the others, the totals sit next to the wrong suppliers without any error. Sort the finished block instead, as in the first variation.

Variations

=SORT(HSTACK(UNIQUE(B2:B8), SUMIFS(C2:C8, B2:B8, UNIQUE(B2:B8)), SUMIFS(D2:D8, B2:B8, UNIQUE(B2:B8))), 2, -1)

=SORT(HSTACK(UNIQUE(B2:B8); SUMIFS(C2:C8; B2:B8; UNIQUE(B2:B8)); SUMIFS(D2:D8; B2:B8; UNIQUE(B2:B8))); 2; -1)

The same table sorted by net amount, largest supplier on top (C2002, C2001, C2003).

=HSTACK(UNIQUE(B2:B8), SUMIFS(C2:C8, B2:B8, UNIQUE(B2:B8)))

=HSTACK(UNIQUE(B2:B8); SUMIFS(C2:C8; B2:B8; UNIQUE(B2:B8)))

Only the net amounts: two arguments are enough when you do not need the VAT.

Master

Receivables per customer: only the differences against the subledger

You reconcile the receivables account 1300 of Veldhuis Installatie BV to the subledger. The ledger extract has 7 postings, the subledger 4 customer balances. You want one table with customer, subledger balance, ledger total and difference, showing only the customers where the two do not agree.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G	H	I
1	GL customer	GL posting	Subledger customer	Subledger balance
2	D1001	€5,000.00€ 5.000,00	D1001	€6,000.00€ 6.000,00	D1002=LET( customer, C2:C5, subledger, D2:D5, ledger, SUMIFS(B2:B8, A2:A8, customer), difference, ledger - subledger, FILTER(HSTACK(customer, subledger, ledger, difference), difference <> 0, "No differences") )=LET( customer; C2:C5; subledger; D2:D5; ledger; SUMIFS(B2:B8; A2:A8; customer); difference; ledger - subledger; FILTER(HSTACK(customer; subledger; ledger; difference); difference <> 0; "No differences") )	€2,700.00€ 2.700,00	€2,500.00€ 2.500,00	-€200.00€ -200,00
3	D1002	€3,200.00€ 3.200,00	D1002	€2,700.00€ 2.700,00	D1004	€3,600.00€ 3.600,00	€4,000.00€ 4.000,00	€400.00€ 400,00
4	D1001	€2,500.00€ 2.500,00	D1003	€1,800.00€ 1.800,00
5	D1003	€1,800.00€ 1.800,00	D1004	€3,600.00€ 3.600,00
6	D1002	-€700.00€ -700,00
7	D1004	€4,000.00€ 4.000,00
8	D1001	-€1,500.00€ -1.500,00
9

Formula cell (F2)Spilled result

Formula in F2

=LET(
  customer, C2:C5,
  subledger, D2:D5,
  ledger, SUMIFS(B2:B8, A2:A8, customer),
  difference, ledger - subledger,
  FILTER(HSTACK(customer, subledger, ledger, difference), difference <> 0, "No differences")
)

=LET(
  customer; C2:C5;
  subledger; D2:D5;
  ledger; SUMIFS(B2:B8; A2:A8; customer);
  difference; ledger - subledger;
  FILTER(HSTACK(customer; subledger; ledger; difference); difference <> 0; "No differences")
)

Result: D1002 | €2,700.00 | €2,500.00 | -€200.00 / D1004 | €3,600.00 | €4,000.00 | €400.00D1002 | € 2.700,00 | € 2.500,00 | € -200,00 / D1004 | € 3.600,00 | € 4.000,00 | € 400,00

Step by step

LET stores the names. customer is C2:C5 (D1001, D1002, D1003, D1004) and subledger is D2:D5 (6,0006.000, 2,7002.700, 1,8001.800, 3,6003.600).
ledger is SUMIFS over the 7 postings with each customer as criterion. D1001: 5,0005.000 + 2,5002.500 - 1,5001.500 = 6,0006.000. D1002: 3,2003.200 - 700 = 2,5002.500. D1003: 1,8001.800. D1004: 4,0004.000. So ledger is 6,0006.000, 2,5002.500, 1,8001.800, 4,0004.000.
difference is ledger - subledger, element by element: 6,0006.000 - 6,0006.000 = 0; 2,5002.500 - 2,7002.700 = -200; 1,8001.800 - 1,8001.800 = 0; 4,0004.000 - 3,6003.600 = 400.
HSTACK(customer, subledger, ledger, difference) builds the full 4 by 4 table, one row per customer, in the order of the subledger.
FILTER tests difference <> 0: FALSE, TRUE, FALSE, TRUE. It keeps rows 2 and 4. The result spills from F2 to I3: D1002, 2,7002.700, 2,5002.500, -200 and D1004, 3,6003.600, 4,0004.000, 400.
Correct the subledger balance of D1002 to 2,5002.500 and that row disappears. When all four agree, F2 shows No differences instead of #CALC!.

Watch out: The mirror-image mistake is driving the table from the ledger side: UNIQUE(A2:A8) as customer with the subledger looked up by position. Then a customer missing in the subledger is shown, but the subledger column is only aligned if both lists are sorted the same way, which nothing checks.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

The names compute each piece once, and HSTACK uses the same customer list for all columns, so every row is consistent; difference is used twice, as a column and as the filter test, without being calculated twice.

Do not use this when the ledger contains customers that the subledger lacks: the subledger list drives the table, so those postings are never looked at. Then drive from UNIQUE of the ledger customers, or use a PivotTable on both sources, which shows every customer without any formula.

More to watch out for

With cents, difference <> 0 flags floating point noise such as 0.0000000001 as a difference. Use ABS(difference) >= 0.005 as the test.
The range C2:D5 must match the subledger exactly. When the client adds a customer below row 5, the fixed range leaves that customer out without any warning.
Make the subledger an Excel Table or extend the ranges, and check the row count against the specification.

Variations

=FILTER(HSTACK(C2:C5, D2:D5, SUMIFS(B2:B8, A2:A8, C2:C5)), SUMIFS(B2:B8, A2:A8, C2:C5) <> D2:D5, "No differences")

=FILTER(HSTACK(C2:C5; D2:D5; SUMIFS(B2:B8; A2:A8; C2:C5)); SUMIFS(B2:B8; A2:A8; C2:C5) <> D2:D5; "No differences")

Without LET and without the difference column: shorter, but SUMIFS is written twice and the reviewer cannot see the difference.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Totals sit next to the wrong accounts	You stack a UNIQUE list next to a SORT of the amounts and the totals sit next to the wrong accounts.	Build every piece from the same array, for example SUMIFS over UNIQUE(A2:A8), so row 3 of each piece describes the same item.
#N/A in the last rows of a short list	The shorter list shows #N/A in its last rows.	That is the padding. Wrap the whole HSTACK in IFNA(..., "") or another text to replace it, or make the ranges the same height on purpose.
Zeros instead of #N/A in the short range	You pass two ranges of unequal height and the short one shows zeros, not #N/A.	You stretched the short range over empty cells, and an empty cell comes through as 0. Point each argument at exactly the rows that hold data.
#SPILL! although the formula is correct	The result is #SPILL! even though the formula is correct.	Something sits in the cells the block needs to the right or below. Choose Select Obstructing Cells from the error menu and clear them.

Used in combinations

Differences above the reporting threshold with LET, FILTER, HSTACK and ABSReconcile the ledger to the client's specification per customer and list only the differences above the reporting threshold: LET names the differences once, HSTACK pairs them with the customer, FILTER keeps the rows where ABS exceeds the threshold.Excel 2024 or later (This combination needs Excel 2024 or later)
Journal entries where debit does not equal creditList the entries whose debit and credit totals differ: take the unique entry numbers, total debit and credit per entry with SUMIFS, and keep the unequal ones with FILTER over HSTACK, all named in one LET.Excel 2024 or later (This combination needs Excel 2024 or later)
Mini trial balance from journal lines with HSTACK, UNIQUE and SUMIFSBuild a list of accounts with their net postings from raw journal lines: UNIQUE gives the accounts, SUMIFS totals each one, HSTACK puts both side by side and SORT orders them.Excel 2024 or later (This combination needs Excel 2024 or later)