Spreadsheets

VSTACK: combine monthly expense sheets into one table in Excel

Use VSTACK in Excel 365 to combine twelve monthly expense tabs into one table, with a header row and blank rows removed, instead of copy-pasting.

Editorial illustration for VSTACK: combine monthly expense sheets into one table in Excel

In Microsoft 365 Excel, =VSTACK(Apr!A2:D200,May!A2:D200,Jun!A2:D200) stacks the ranges from several tabs into one list, one on top of another. List all twelve tabs, add a header row and filter out the empty rows, and your whole year sits in one table that updates when a monthly tab changes.

What does VSTACK do?

VSTACK(array1, [array2], ...) places each array below the previous one. Each tab is one array. With a tab for every month of the financial year, each holding date, merchant, category and amount in A to D, the formula starts like this:

=VSTACK(Apr!A2:D200,May!A2:D200,Jun!A2:D200,Jul!A2:D200,Aug!A2:D200,Sep!A2:D200,Oct!A2:D200,Nov!A2:D200,Dec!A2:D200,Jan!A2:D200,Feb!A2:D200,Mar!A2:D200)

If a tab name has spaces, put it in single quotes: 'Apr 2026'!A2:D200. VSTACK is only in Microsoft 365; it is missing from Excel 2021 and older, and a plain 3D reference such as SUM across sheets will not do this job.

How do I add a header row?

The ranges above start at row 2, so no heading comes through. Either stack the first tab's heading on top, or type the headings as a row constant:

=VSTACK({"Date","Merchant","Category","Amount"},Apr!A2:D200,May!A2:D200, ...)

Commas inside the curly brackets mean across; the headings sit in one row above the data.

How do I get rid of the blank rows?

A range like A2:D200 includes empty rows below the real entries, and they come through as zeros. Name the stack with LET, then keep only rows with a date:

=LET(all,VSTACK(Apr!A2:D200,May!A2:D200,Jun!A2:D200),FILTER(all,INDEX(all,0,1)<>0))

INDEX(all,0,1) is the first column of the stacked data, the date. A real date is never zero, so rows with a zero in that column are dropped. This relies on empty cells arriving as 0 in the result, which is how current versions behave; if blank rows remain for you, change the test to match what you see in the unfiltered output.

StepFormula partPurpose
1LET(all, VSTACK(...))Stack the tabs and name the result all
2INDEX(all,0,1)<>0Test the date column, row by row
3FILTER(all, test)Keep only rows that pass
4Wrap in VSTACK with headingsPut the header row on top

What goes wrong?

  • #REF!: a tab named in the formula was renamed or deleted
  • #N/A in some cells: the ranges have different widths, and VSTACK pads the narrower ones with #N/A
  • #SPILL!: something occupies the cells below the formula
  • Wrong totals: dates stored as text on one tab, or amounts typed as text, so check each tab

A worked example

Say April holds 3 rows and May holds 2 in the first columns, so the stacked list has 5 data rows plus the header. A total of the amount column, taken from the spilled result, should equal the April total plus the May total. For instance, 4,500 for April and 3,100 for May gives 7,600. If it does not, a tab holds a hidden or text amount.

Is there an alternative?

For Excel versions without VSTACK, Power Query has an Append option under Get Data that does a similar job, and it refreshes with a click. Copy and paste works but breaks the moment a month is corrected. Keeping one long sheet with a Month column from the start avoids stacking altogether.

Setup checklist

  • Same columns on every tab
  • Range ends well past the busiest month
  • Blank-row filter in place
  • Spill area clear
  • Total cross-checked against two tabs

If twelve tabs feel like a lot to maintain, importing each month's statement into SpendixAI keeps every month in one list, where you can view reports across months and download CSV.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports