Combine several months of bank statements into one spreadsheet

A year of statements usually means twelve separate files. To see trends, total a year's spending, or hand everything to an accountant, you want them in one table. Here are three ways to combine them, and the checks that make sure nothing went missing at the joins.

Before you combine: check the statements join up

Each statement's opening balance should equal the previous statement's closing balance. Line the statements up by date and check that first. A gap means a missing statement, and it's much easier to spot now than after everything is in one long table.

Option 1: copy and paste (any spreadsheet)

For a handful of months, this is quickest. Make sure every file has the same columns in the same order, then paste each one under the last, keeping only the first header row. Add a Statement column and fill in which month each row came from. You'll want it later when something needs tracing back.

Option 2: let the spreadsheet stack them

If each month is on its own sheet with the same columns, a formula can stack them into one list that updates when the sheets change.

• Excel (Microsoft 365): =VSTACK(Jan!A2:E200, Feb!A2:E200, Mar!A2:E200)
• Google Sheets: ={Jan!A2:E200; Feb!A2:E200; Mar!A2:E200}, where the semicolon stacks the ranges vertically.

Empty rows inside the ranges come along too, so either size the ranges exactly or filter the result to remove blanks.

Option 3: Power Query in Excel for Windows

If the months are separate CSV files, put them all in one folder and use Data → Get Data → From File → From Folder. Choose Combine & Load. Excel stacks every file into one table and adds a column with each file's name. When next month's file arrives, drop it in the folder and click Refresh.

All the files need the same columns and the same date format. Check the dates came through as real dates; see Excel shows bank statement dates as text if not.

After combining: check the totals

• Count the rows: the combined table should have exactly as many transactions as all the statements together.
• Check the money: the first statement's opening balance, plus all money in, minus all money out, should equal the last statement's closing balance. The reconciliation template does this for you.
• Look for duplicates at the joins. If two downloads overlapped by a few days, the same transactions appear twice around the boundary. Sort by date and check either side of each month end.

From PDF statements, in one step

The statement converter takes up to 20 PDF statements at once and can download them as one combined file, with Source and Account columns that show which statement each row came from. Each statement is checked against its own opening and closing balances before you download, and it all runs in your browser.