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

VSTACK

VERT.STAPELEN in Dutch Excel

Puts ranges or arrays below each other into one spilled block, so separate exports become a single list you can sort, filter or deduplicate.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G
1	INK entry	INK amount	MEM entry	MEM amount
2	25-0412	€3,025.00€ 3.025,00	25-0013	€15,000.00€ 15.000,00	25-0412=VSTACK(A2:B4, C2:D4)=VSTACK(A2:B4; C2:D4)	€3,025.00€ 3.025,00
3	25-0418	€1,210.00€ 1.210,00	25-0014	€2,500.00€ 2.500,00	25-0418	€1,210.00€ 1.210,00
4	25-0421	€6,050.00€ 6.050,00	25-0015	€480.00€ 480,00	25-0421	€6,050.00€ 6.050,00
5					25-0013	€15,000.00€ 15.000,00
6					25-0014	€2,500.00€ 2.500,00
7					25-0015	€480.00€ 480,00

=VSTACK(A2:B4, C2:D4)

=VSTACK(A2:B4; C2:D4)

What VSTACK does and when to use it

What it does: it puts arrays under each other into one list, for example a ledger export per month into one long ledger. The list spills into the cells below.

When to use it: when the same kind of data arrives in pieces, such as a trial balance per entity. It replaces copying and pasting exports under each other, and the combined list updates when a source changes. For many files or a refresh that must run unattended, Power Query is sturdier.

Two main gains:

Stays live: the combined list updates when a source changes, while pasted copies do not.
Any source: each argument can be a range or the result of another function, such as FILTER.

How Excel evaluates it

Excel first finds the widest argument: that sets the number of columns of the result. It then writes array1 from the top, array2 directly below the last row of array1, and so on. The total height is the sum of the heights, and the block spills from the formula cell.

Headers are not treated specially: a header row inside a range is stacked like any other row.

Watch out: If an argument is narrower than the widest one, the empty cells to its right get #N/A instead of staying blank. Wrap the whole VSTACK in IFNA to replace the padding.

Mental model

Stacking printed lists on a desk: the second pile goes directly on top of the first one's bottom edge, and a narrower sheet leaves a blank strip that VSTACK marks with #N/A.

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

Syntax

Official VSTACK documentation

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

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

Arguments
Argument	Type	Meaning
`array1`	Required	The first range or array: it ends up at the top of the result.
`array2`	Optional	Further ranges or arrays, each placed below the previous one.

Return value:One array: the rows of array1, then the rows of array2, and so on

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

Combine two journals into one list for testing

At Veldhuis Installatie BV the XAF auditfile gives you the purchase journal INK and the memorial journal MEM as two separate lists, each with entry number and amount. For journal entry testing you want one list with all lines under each other.

The INK list sits in A and B, the MEM list in C and D.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G
1	INK entry	INK amount	MEM entry	MEM amount
2	25-0412	€3,025.00€ 3.025,00	25-0013	€15,000.00€ 15.000,00	25-0412=VSTACK(A2:B4, C2:D4)=VSTACK(A2:B4; C2:D4)	€3,025.00€ 3.025,00
3	25-0418	€1,210.00€ 1.210,00	25-0014	€2,500.00€ 2.500,00	25-0418	€1,210.00€ 1.210,00
4	25-0421	€6,050.00€ 6.050,00	25-0015	€480.00€ 480,00	25-0421	€6,050.00€ 6.050,00
5					25-0013	€15,000.00€ 15.000,00
6					25-0014	€2,500.00€ 2.500,00
7					25-0015	€480.00€ 480,00
8

Formula cell (F2)Spilled result

Formula in F2

=VSTACK(A2:B4, C2:D4)

=VSTACK(A2:B4; C2:D4)

Result: 25-0412 | €3,025.00 / 25-0418 | €1,210.00 / 25-0421 | €6,050.00 / 25-0013 | €15,000.00 / 25-0014 | €2,500.00 / 25-0015 | €480.0025-0412 | € 3.025,00 / 25-0418 | € 1.210,00 / 25-0421 | € 6.050,00 / 25-0013 | € 15.000,00 / 25-0014 | € 2.500,00 / 25-0015 | € 480,00

Step by step

Excel looks at the two arguments: A2:B4 is 3 rows by 2 columns, C2:D4 is also 3 rows by 2 columns. The widest is 2 columns, so the result is 2 columns wide.
It writes the first argument at the top: 25-0412 with 3,0253.025, 25-0418 with 1,2101.210 and 25-0421 with 6,0506.050.
Directly below the third row it writes the second argument: 25-0013 with 15,00015.000, 25-0014 with 2,5002.500 and 25-0015 with 480.
The height is 3 + 3 = 6 rows, so the result spills from F2 to G7. Both lists had the same width, so no #N/A appears.
Change C4 from 25-0015 to 25-0099 and the last row of the list changes with it, because the stack is a formula, not a pasted copy.

Watch out: The order of the arguments is the order of the rows. VSTACK(C2:D4, A2:B4) puts the MEM lines first, which matters if you number or sample from the top.

More on this example: why it works, 1 variation

Why it works

VSTACK treats each argument as a block and puts the blocks one after the other in the order you list them. Because both blocks have the same two columns, entry numbers line up under entry numbers and amounts under amounts.

Variations

=VSTACK(A2:B4, C2:D4, A2:B4)

=VSTACK(A2:B4; C2:D4; A2:B4)

Any number of arguments is allowed, and the same range may appear twice; here the INK lines come back at the bottom as well.

Intermediate

Stack ledger and bank lines of unequal width

You are reconciling cash at Veldhuis Installatie BV. The ledger extract has entry, amount and account; the bank statement extract has only entry and amount. You stack them into one list for matching, but the missing account of the bank lines must read unknown instead of #N/A.

The ledger sits in A to C, the bank lines in D and E.

Sheet excerpt: G2 holds the formula
Row	A	B	C	D	E	G	H	I
1	Ledger entry	Ledger amount	Account	Bank entry	Bank amount
2	25-0601	€4,200.00€ 4.200,00	11001100	B-1101	€4,200.00€ 4.200,00	25-0601=IFNA(VSTACK(A2:C4, D2:E3), "unknown")=IFNA(VSTACK(A2:C4; D2:E3); "unknown")	€4,200.00€ 4.200,00	11001100
3	25-0602	€9,680.00€ 9.680,00	80008000	B-1102	€2,750.00€ 2.750,00	25-0602	€9,680.00€ 9.680,00	80008000
4	25-0603	€1,500.00€ 1.500,00	45004500			25-0603	€1,500.00€ 1.500,00	45004500
5						B-1101	€4,200.00€ 4.200,00	unknown
6						B-1102	€2,750.00€ 2.750,00	unknown
7

Formula cell (G2)Spilled result

Formula in G2

=IFNA(VSTACK(A2:C4, D2:E3), "unknown")

=IFNA(VSTACK(A2:C4; D2:E3); "unknown")

Result: 25-0601 | €4,200.00 | 1100 / 25-0602 | €9,680.00 | 8000 / 25-0603 | €1,500.00 | 4500 / B-1101 | €4,200.00 | unknown / B-1102 | €2,750.00 | unknown25-0601 | € 4.200,00 | 1100 / 25-0602 | € 9.680,00 | 8000 / 25-0603 | € 1.500,00 | 4500 / B-1101 | € 4.200,00 | unknown / B-1102 | € 2.750,00 | unknown

Step by step

Excel measures the arguments: A2:C4 is 3 rows by 3 columns, D2:E3 is 2 rows by 2 columns. The widest is 3 columns, so the result is 3 columns wide and 3 + 2 = 5 rows tall.
The ledger fills rows 1 to 3 completely: 25-0601, 4,2004.200, 1100; 25-0602, 9,6809.680, 8000; 25-0603, 1,5001.500, 4500.
The bank lines go into rows 4 and 5: B-1101 with 4,2004.200 and B-1102 with 2,7502.750 in the first two columns. The third column has no source, so VSTACK puts #N/A in it for both rows (I5 and I6 on the sheet).
IFNA receives the whole 5 by 3 block and checks every cell. Only the two #N/A cells are replaced by unknown; numbers and entry numbers pass through unchanged.
The result spills from G2 to I6. Without IFNA, I5 and I6 would show #N/A, and any SUM over column I would return #N/A as well.

Watch out: The padding always lands on the right. Here the account is the last column, so the bank lines stay aligned. If the missing field were the first column in the ledger layout, amounts would end up under the wrong heading with no error at all.

More on this example: why it works, 1 variation

Why it works

VSTACK always returns a rectangle, so a narrower argument has to be padded, and #N/A is the padding. IFNA works on a whole array at once and touches only #N/A, so genuine errors such as #DIV/0! would still show up.

Variations

=VSTACK(A2:C4, HSTACK(D2:E3, IF(D2:D3<>"", "unknown", "")))

=VSTACK(A2:C4; HSTACK(D2:E3; IF(D2:D3<>""; "unknown"; "")))

Pad the narrow range yourself with HSTACK; the result is the same, and the intent is visible in the formula instead of hidden in IFNA. IF supplies one value per row: a single text value would be padded with #N/A below its first row.

Advanced

Unique, sorted invoice population from two monthly files

Veldhuis Installatie BV sends the paid supplier invoices as one file for October (column A) and one for November (column B). Some invoices were delivered in both files.

Before you draw a sample you want one population with every invoice number once, in ascending order.

Sheet excerpt: D2 holds the formula
Row	A	B	D
1	October invoices	November invoices
2	25-2001	25-2005	25-2001=SORT(UNIQUE(VSTACK(A2:A6, B2:B6)))=SORT(UNIQUE(VSTACK(A2:A6; B2:B6)))
3	25-2004	25-2002	25-2002
4	25-2002	25-2008	25-2003
5	25-2007	25-2004	25-2004
6	25-2003	25-2006	25-2005
7			25-2006
8			25-2007
9			25-2008
10

Formula cell (D2)Spilled result

Formula in D2

=SORT(UNIQUE(VSTACK(A2:A6, B2:B6)))

=SORT(UNIQUE(VSTACK(A2:A6; B2:B6)))

Result: 25-2001 / 25-2002 / 25-2003 / 25-2004 / 25-2005 / 25-2006 / 25-2007 / 25-200825-2001 / 25-2002 / 25-2003 / 25-2004 / 25-2005 / 25-2006 / 25-2007 / 25-2008

Step by step

The innermost function is VSTACK(A2:A6, B2:B6). It puts the 5 October invoices on top of the 5 November invoices: 25-2001, 25-2004, 25-2002, 25-2007, 25-2003, 25-2005, 25-2002, 25-2008, 25-2004, 25-2006. That is one column of 10 rows.
UNIQUE reads that column from the top and keeps the first appearance of each value. The second 25-2002 (position 7) and the second 25-2004 (position 9) are dropped. 8 values remain, in this order: 25-2001, 25-2004, 25-2002, 25-2007, 25-2003, 25-2005, 25-2008, 25-2006.
SORT sorts the 8 values in ascending order: 25-2001, 25-2002, 25-2003, 25-2004, 25-2005, 25-2006, 25-2007, 25-2008. The default sort_index 1 and sort_order 1 are what we want.
The result spills from D2 to D9. The check is 10 stacked invoices minus 2 duplicates = 8 unique ones.
Replace B3 with 25-2009 and the list gains a ninth number, 25-2009, at the bottom of the sorted list, because the whole chain recalculates.

Watch out: If the November file is shorter and you point at B2:B10, the empty cells are stacked as well. UNIQUE then returns a 0 as an extra value, and the sorted list starts with it, so the population looks one invoice bigger than it is.

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

Why it works

VSTACK produces an ordinary array, so any array function can take it as input without a helper column. The order matters: UNIQUE first, so SORT only has to order 8 values, and the formula works from the inside outward.

More to watch out for

A number exported with a trailing space, such as '25-2002 ', counts as a different value for UNIQUE and stays in the list twice. Clean both columns with TRIM before you stack if the exports come from different systems.

Variations

=VSTACK("Invoice no.", SORT(UNIQUE(VSTACK(A2:A6, B2:B6))))

=VSTACK("Invoice no."; SORT(UNIQUE(VSTACK(A2:A6; B2:B6))))

Add one header on top: the header is stacked once, above the finished list, instead of copying a header from either export.

=SORT(UNIQUE(VSTACK(A2:A6, B2:B6),,TRUE))

=SORT(UNIQUE(VSTACK(A2:A6; B2:B6);;TRUE))

Set exactly_once to TRUE to list only the invoices that occur in one file: 25-2001, 25-2003, 25-2005, 25-2006, 25-2007, 25-2008. The two shared invoices are left out.

Master

Consolidated trial balance of two entities with a check total

Two entities of the Veldhuis group each deliver a trial balance with account and balance (debit positive, credit negative). You want one consolidated list by account, sorted, with a total row underneath that must be 0, so a missing or double line shows immediately.

The accounts of entity 1 are in A and B, those of entity 2 in C and D, and the two entities do not use exactly the same accounts.

Sheet excerpt: F2 holds the formula
Row	A	B	C	D	F	G
1	Account entity 1	Balance entity 1	Account entity 2	Balance entity 2
2	11001100	€25,000.00€ 25.000,00	11001100	€9,000.00€ 9.000,00	11001100=LET( accounts, SORT(UNIQUE(VSTACK(A2:A5, C2:C5))), balances, SUMIFS(B2:B5, A2:A5, accounts) + SUMIFS(D2:D5, C2:C5, accounts), lines, HSTACK(accounts, balances), VSTACK(lines, HSTACK("Total", SUM(balances))) )=LET( accounts; SORT(UNIQUE(VSTACK(A2:A5; C2:C5))); balances; SUMIFS(B2:B5; A2:A5; accounts) + SUMIFS(D2:D5; C2:C5; accounts); lines; HSTACK(accounts; balances); VSTACK(lines; HSTACK("Total"; SUM(balances))) )	€34,000.00€ 34.000,00
3	13001300	€18,000.00€ 18.000,00	16001600	-€6,000.00€ -6.000,00	13001300	€18,000.00€ 18.000,00
4	40004000	€12,000.00€ 12.000,00	45004500	€4,000.00€ 4.000,00	16001600	-€6,000.00€ -6.000,00
5	80008000	-€55,000.00€ -55.000,00	80008000	-€7,000.00€ -7.000,00	40004000	€12,000.00€ 12.000,00
6					45004500	€4,000.00€ 4.000,00
7					80008000	-€62,000.00€ -62.000,00
8					Total	€0.00€ 0,00
9

Formula cell (F2)Spilled result

Formula in F2

=LET(
  accounts, SORT(UNIQUE(VSTACK(A2:A5, C2:C5))),
  balances, SUMIFS(B2:B5, A2:A5, accounts) + SUMIFS(D2:D5, C2:C5, accounts),
  lines, HSTACK(accounts, balances),
  VSTACK(lines, HSTACK("Total", SUM(balances)))
)

=LET(
  accounts; SORT(UNIQUE(VSTACK(A2:A5; C2:C5)));
  balances; SUMIFS(B2:B5; A2:A5; accounts) + SUMIFS(D2:D5; C2:C5; accounts);
  lines; HSTACK(accounts; balances);
  VSTACK(lines; HSTACK("Total"; SUM(balances)))
)

Result: 1100 | €34,000.00 / 1300 | €18,000.00 / 1600 | -€6,000.00 / 4000 | €12,000.00 / 4500 | €4,000.00 / 8000 | -€62,000.00 / Total | €0.001100 | € 34.000,00 / 1300 | € 18.000,00 / 1600 | € -6.000,00 / 4000 | € 12.000,00 / 4500 | € 4.000,00 / 8000 | € -62.000,00 / Total | € 0,00

Step by step

LET evaluates the names in order. First accounts: VSTACK(A2:A5, C2:C5) stacks 1100, 1300, 4000, 8000 and 1100, 1600, 4500, 8000. UNIQUE keeps the first occurrence of each: 1100, 1300, 4000, 8000, 1600, 4500. SORT puts them in ascending order: 1100, 1300, 1600, 4000, 4500, 8000.
Next balances. The first SUMIFS asks entity 1 for each of the 6 accounts: 25,00025.000, 18,00018.000, 0, 12,00012.000, 0, -55,00055.000. The second SUMIFS asks entity 2: 9,0009.000, 0, -6,0006.000, 0, 4,0004.000, -7,0007.000. An account that one entity does not use gives 0.
Adding the two arrays position by position gives 34,00034.000, 18,00018.000, -6,0006.000, 12,00012.000, 4,0004.000, -62,00062.000. For account 1100 that is 25,00025.000 + 9,0009.000 = 34,00034.000 and for account 8000 it is -55,00055.000 + -7,0007.000 = -62,00062.000.
lines puts accounts and balances next to each other with HSTACK: a 6 by 2 block.
The final VSTACK puts lines on top and a one-row block below it: the text Total and SUM(balances) = 34,00034.000 + 18,00018.000 - 6,0006.000 + 12,00012.000 + 4,0004.000 - 62,00062.000 = 0. Both blocks are 2 columns wide, so no #N/A appears.
The result spills from F2 to G8 (7 rows). The total of 0 shows that both trial balances were complete and balanced. Change D5 to -6,5006.500 and the total becomes 500, which tells you something is off.

Watch out: The mirror-image mistake is stacking ranges that include each sheet's own total row. UNIQUE then adds a Total line to the account list, SUMIFS gives it 0, and SORT puts it at the bottom, so the result looks tidy while the group total you compare against is missing.

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

Why it works

VSTACK is used twice with a different job: first to build the combined list of accounts, then to attach a total row to a finished table. LET keeps accounts and balances as names, so each is calculated once and a reviewer can read the formula top to bottom.

Do not use this construction for more than a few entities, for balances that change in structure each period, or when the file must open in Excel 2021 or earlier: there, Power Query appends the trial balances once and refreshes with one click, at the cost of a refresh step.

More to watch out for

If one balance column is stored as text, SUMIFS silently ignores it and counts 0. The total then no longer equals 0, which is exactly why the check row is there; do not remove it.
Intercompany accounts are not eliminated here. The list adds balances only; the elimination of intercompany balances is a separate step.

Variations

=LET(
  accounts, SORT(UNIQUE(VSTACK(A2:A5, C2:C5))),
  SUMIFS(B2:B5, A2:A5, accounts) + SUMIFS(D2:D5, C2:C5, accounts)
)

=LET(
  accounts; SORT(UNIQUE(VSTACK(A2:A5; C2:C5)));
  SUMIFS(B2:B5; A2:A5; accounts) + SUMIFS(D2:D5; C2:C5; accounts)
)

Debugging tip: return an intermediate name as the last argument to see it on its own. Here only the balances come back (34,00034.000; 18,00018.000; -6,0006.000; 12,00012.000; 4,0004.000; -62,00062.000), without account numbers or total.

=SORT(UNIQUE(VSTACK(A2:A5, C2:C5)))

=SORT(UNIQUE(VSTACK(A2:A5; C2:C5)))

Alternative without LET when you only need the list of accounts: shorter, but each part is recalculated if you reuse it elsewhere.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#N/A in some cells of the result	Some cells of the result show #N/A and you cannot see why.	One of the arguments is narrower than the widest. Check the column counts; if the gap is intended, IFNA lets you choose a text or an empty text as the replacement.
A 0 or an empty line in the middle	The combined list contains a row with 0 or an empty-looking line in the middle.	A range that is taller than its data includes blank cells, and VSTACK stacks them too. Point each argument at the exact rows, or filter out blanks afterwards with FILTER.
Headers show up in the middle of the list	Each export carries its own header row, and the headers appear in the middle of the list.	Start every range below its header and add one header row yourself, for example VSTACK(A1:C1, A3:C9, A12:C20) with the header taken once.
#SPILL! although the formula is correct	The result is #SPILL! even though the formula is correct.	The cells the stacked block needs are not empty. Click the error, choose Select Obstructing Cells, and clear them; remember the block is as tall as all inputs together.

Used in combinations

Duplicate invoice numbers across two exportsStack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.Excel 2024 or later (This combination needs Excel 2024 or later)