VSTACK in Excel: Combine Data From Multiple Sheets

Brantwood Garden Centres reported £63,208.10 of October sales. The shops took £75,773.35. Every branch tab balanced to its own till export, nothing errored, and the group tab came out £12,565.25 light.

That tab is one formula. =VSTACK(Alderwell:Thurlby!A2:F200) stacks nine branch sheets into a single list, and SUMIFS reads the list. It is the tidiest thing in the workbook.

A stack aligns by position, never by heading. Column F of one tab lands under column F of the next, whatever the two headings say, and nothing in Excel compares them.

Four faults made that gap, and two of them pull in opposite directions, so the total looked like a quiet month. A branch missing, a tab counted twice, two columns swapped, and a range that stops at row 200.

What you need. VSTACK and HSTACK need Microsoft 365 or Excel 2024. FILTER, SORT, UNIQUE, LET and spill references such as =H2# need Microsoft 365 or Excel 2021 and later.

SUMIFS, COUNTIFS, SUMPRODUCT, INDEX, COUNTA, MOD and 3-D references work in every version this century. Section 11 is for the build that has none of the new ones.

Garden centres are only the example. Depots, clinics, sites, funds, subsidiaries: one tab each, one summary tab, and the same silence.

01Nine Tabs, One Stack, £12,565.25 Short

The sheet above is not the stack. It is the reconciliation nobody had run against it: one row per branch tab, what the stack took from that tab, and what the tab itself totals.

Column F is the amount the stack carries. Column G is the amount the branch took. Six branches agree to the penny. Three do not, and one of the three is wrong in the group's favour.

Net, the group is £12,565.25 short. Add the four faults without their signs and £30,785.25 of October landed somewhere other than where it belongs.

02VSTACK Stacks by Position, Not by Heading

VSTACK takes arrays and puts each one under the last. Hand it a 3-D reference and it takes the same block from every tab between the two you name, in the order the tabs sit in.

Nothing in it reads a heading. The branch tabs came from one template in 2019, so for six years column F held the money on every one of them. That is a convention, and a convention is not a rule.

One cell says whether two tabs still agree:

=SUMPRODUCT(--(Alderwell!$A$1:$F$1=Oakmere!A1:F1))

It counts the headings that match, position by position. Six means aligned. Anything less is a tab the stack will scramble without a word, and on this workbook it returned 4.

03The Branch That Stacked Counts as Money

Oakmere changed till systems in September. The new export writes Amount in column E and Units in column F. Every other branch, and the template they all came from, does it the other way round.

So the stack filed Oakmere's £12,742.60 of sales as a count of units, and its 486 units as £486.00. The group total lost £12,256.60 and gained nothing that looked like an error.

The tell was sitting in a column nobody totals. October's unit count came out at 20,324.60. A count of bags of compost with pence in it is a column that has been swapped, and it is the cheapest check on this page.

=SUMPRODUCT(--(MOD(E2:E1593,1)<>0))

That counts the rows whose unit figure is not a whole number. It came back with 149: the Oakmere rows whose amount carries pence. The other eight of its 157 sales were round pounds.

04Every Tab Gives 199 Rows, Sales or Not

A 3-D reference takes the same block from every tab, so A2:F200 is 199 rows whether a branch sold 199 things or 96. The empty rows come through, and they do not come through empty.

A reference to an empty cell returns 0, not a blank. The stack holds 493 rows of zeros, with 00/01/1900 in the date column, and COUNTA counts every one of them as data.

That is why the average basket moved. £63,208.10 over 1,592 rows is £39.70. Over the 1,099 rows that are sales it is £57.51. Both are arithmetic; only one answers the question the report asked.

FILTER strips the padding, and the test is where people lose an afternoon: the blanks are zeros, so <>"" keeps every one of them.

=LET(s, VSTACK(Alderwell:Thurlby!A2:F200),
     FILTER(s, INDEX(s,0,1)<>0))

05The Branch That Was Never in the Stack

Ferncliffe opened in September. Its tab went where Excel puts a new sheet when you ask at the end: one place to the right of Thurlby. The formula says Alderwell:Thurlby.

A 3-D reference means the tabs physically between those two, in tab order. Not the branches, not the alphabet, and not whatever the workbook contains today. £7,480.20 sat one tab outside the span in a sheet that balanced perfectly on its own.

Bookend the span with two empty sheets. Name them Start and End, put one at each end of the branch tabs, and write =VSTACK(Start:End!A2:F200). Any tab dragged between them joins the stack.

That is the whole trick, and its price is the one in section 6: a span takes every tab inside it, including the ones you did not mean.

06The Copy That Was Counted Twice

Greenhall's manager wanted to try a different layout, right-clicked the tab and chose Move or Copy. Excel puts the copy immediately after the original, which is inside the span, so the stack took it.

£9,110.00 counted twice. On its own that would have shown: the group total would have been £9,110.00 too high and somebody would have asked. Against Ferncliffe and Oakmere it closed the gap and made four faults look like a slow October.

Two one-cell checks find it. =ROWS(s)-ROWS(UNIQUE(s)) returns 134, which is the number of rows the stack holds twice over. =COUNTA(UNIQUE(INDEX(s,0,1))) returns 8: seven branch names and the zero the blank rows contribute, for eight tabs stacked.

07The Range That Stops at Row 200

Thurlby is the biggest branch: 214 sales in October. A2:F200 takes 199 rows from every tab, so fifteen of them, £1,938.45, are in the workbook and not in the report.

A span cannot grow its rows. It has no idea how long each tab is, and stretching it to A2:F5000 multiplies the blank rows by twenty-five without ever being certain.

Tables grow by themselves, and a 3-D reference cannot hold one. =VSTACK(tAlderwell,tBexmoor,tFerncliffe) over the branch tables follows each one as it fills, and names every tab you meant to include. A tenth branch then has to be added by hand.

Pick which half you want automatic: new tabs for free, or new rows for free. Then write a check against the half you did not pick, because that is the half that will go wrong.

08Different Widths Pad the Stack With #N/A

List the arrays by hand instead of using a span and the widths stop being guaranteed. VSTACK pads the narrow ones with #N/A, so a five-column tab among six-column tabs gets a column of errors rather than a shift.

That is the kind behaviour. The errors are loud, they survive into every total that does not ignore them, and =IFERROR(VSTACK(...),"") turns them into blanks once you have understood why they are there.

HSTACK does the same thing sideways, padding short arrays with #N/A along the bottom. Either way the padding tells you the inputs disagree, which is more than the swapped column in section 3 ever did.

09Five Checks Above the Table

Each of these is one cell, and each one failed on this workbook. Put them above the stack where the report is read, not on a tab called Checks that nobody opens.

The sixth costs a column rather than a cell, and it is the one that would have caught all four faults: the per-branch reconciliation from section 1, stacked total against branch total, every month.

10Reading the Stack: SUMIFS Needs a Range

A stack held inside LET is an array in memory, and SUMIFS and COUNTIFS do not take arrays. They take ranges. Point them at one and the answer is #VALUE!, which at least is an answer.

So let the stack spill onto a sheet. If =VSTACK(...) sits in H2, then H2# is a real range that grows with it, and =SUMIFS(M2:M1593,H2:H1593,$A2) totals a branch the ordinary way.

Inside LET, use SUMPRODUCT instead: =SUMPRODUCT((INDEX(s,0,1)=$A2)*INDEX(s,0,6)) reads arrays happily and gives the same number. XLOOKUP and FILTER take arrays too.

11When Your Build Has None of This

Excel 2019 and earlier have no VSTACK, and Power Query's Append is older than all of it: Data ▸ Get Data ▸ Combine Queries ▸ Append, one query per tab, one output table.

Append matches on column names. Oakmere's swapped columns would have landed under the right headings, and a renamed column would have produced a new column of nulls instead of a silent mix.

It also reads each tab to its real last row, so there is no blank padding and no lost tail. What it does not do is recalculate: somebody has to press Refresh, and a stale refresh hides exactly the kind of gap this article is about.

Which to reach for. A stack is live, reads in one formula and needs Microsoft 365. Power Query is a refresh step, matches on headings and runs on any build since 2016.

If the tabs are really separate files in a folder, neither answer is the right one: point Power Query at the folder instead, and the thirteenth file joins without anybody editing a formula.

12Ten Traps

1. A stack aligns by position. Two tabs with the same headings in a different order stack into nonsense with no error.
2. A 3-D reference is tab order, not alphabet. Dragging a tab changes what the formula covers.
3. A tab added at the end is outside the span. It joins nothing and says nothing.
4. Move or Copy lands inside the span. The copy is counted in full.
5. Blank cells arrive as 0. <>"" does not remove them; <>0 does.
6. A blank date is 00/01/1900. Sort by it and the padding floats to the top.
7. The row bound is fixed. The tab that outgrows it loses its tail quietly.
8. Different widths pad with #N/A. Loud, and better than the alternative.
9. SUMIFS refuses an array. Spill the stack, or total it with SUMPRODUCT.
10. The stack is live. Delete a branch tab and the whole report changes while you watch.

13Mini Exercises

1. Break it on purpose. Copy a branch tab with Move or Copy, leave it where Excel put it, and watch the group total rise by exactly that branch.
2. Count the padding. Write =ROWS(H2#) and =SUMPRODUCT(--(INDEX(H2#,0,1)<>0)) over the example stack, and work out the average basket both ways.
3. Catch the swap. Point =SUMPRODUCT(--(Alderwell!$A$1:$F$1=Oakmere!A1:F1)) at each tab in turn and find the one that returns 4.
4. Fit the bookends. Add Start and End sheets, repoint the stack at Start:End!A2:F200, and drag Ferncliffe inside. Confirm the total rises by £7,480.20.
5. Reconcile the lot. Build the column from section 1 and account for all £30,785.25, not just the £12,565.25 the group total admits to.

What to Take Away

Nothing on this workbook was broken. Nine branch tabs balanced, one formula stacked them, SUMIFS added the stack up, and every number in the report was the honest output of the formula above it.

What nobody had written down is that a stack is an assumption: that every tab has the same columns in the same order, that the tabs inside the span are the tabs you mean, and that the rows you read reach the bottom of the longest one.

Those three assumptions are each one cell to test. =SUMPRODUCT(--(Alderwell!$A$1:$F$1=Oakmere!A1:F1)) tests the first, =ROWS(H2#)-ROWS(UNIQUE(H2#)) the second, and =COUNTA(Thurlby!A2:A10000) against the stacked count the third.

Three cells, against £12,565.25 that nobody could explain and £30,785.25 that was never in the right place to begin with.