How to combine bank statements in Excel

Before you start: get every month as a file

You need one spreadsheet file per statement, with the same columns. Bank exports rarely reach back 12 months, so the PDFs are usually the source.

1. Download the 12 monthly PDFs. How to download bank statements covers where banks keep them.
2. Convert them with BankConv’s bank statement to Excel converter, or to CSV. Up to 10 files go in one batch, so a year takes two batches.
3. Save them in one folder named after the account, with file names like 2026-01.csv to 2026-12.csv.

Pick one format for all 12. Power Query combines a folder of CSVs or a folder of Excel files easily, but mixing the two adds steps.

Method 1: Power Query (best for 12 or more files)

Power Query reads every file in a folder and stacks them into one table. Add next year’s files to the folder and refresh, and they join the table.

1. In Excel, go to Data → Get Data → From File → From Folder.
2. Choose the folder and click Open. Excel lists the files.
3. Click Combine → Combine & Transform Data.
4. Pick the first file as the sample and click OK. The Power Query editor opens with all files stacked. A Source.Name column shows which file each row came from.
5. Set column types: Date for the date column, Decimal Number for amounts and balance. Check the date locale (see the table below).
6. Filter out any rows that aren’t transactions, such as “Opening balance” or “Total”.
7. Click Close & Load. The combined table lands on a new sheet.

Rename Source.Name to Statement and keep it. It tells you which month each row came from, which you need for the checks below.

Method 2: Copy and stack (fine for a few files)

For a handful of months, pasting is quicker than setting up a query.

1. Open January and keep its header row.
2. Add a column called Statement and fill it with 2026-01.
3. Open February, copy the rows without the header, and paste them under January’s last row. Fill Statement with 2026-02.
4. Repeat for each month, in date order.
5. Select the range and press Ctrl+T to make it a table.

Delete any repeated header rows, page totals or balance-forward lines you pasted by mistake. Sort by Date at the end, oldest first.

Fix dates and amounts before anything else

Combining fails quietly when dates or amounts are text. Check these before you analyze anything.

Problem	How to spot it	Fix
Dates in the wrong order (DD/MM vs MM/DD)	13 January shows as text, or dates jump months	In Power Query use Change Type → Using Locale and pick the statement’s country
Dates stored as text	Left-aligned dates; sorting goes 1, 10, 11, 2	Use Data → Text to Columns and choose the date order
Amounts as text	SUM returns 0	Remove currency symbols and spaces; convert to Number
Negatives in brackets	(84.50)	Replace ( with - and remove )
Separate money in and money out columns	Two amount columns	Add Amount = Credit - Debit for one signed column
Description split across two rows	A row with text but no date or amount	Merge it into the row above, then delete it

Remove duplicates without deleting real transactions

Duplicates come from overlapping statements, a month converted twice or an export pasted on top of a PDF. Real transactions can also look identical, so be careful.

Excel’s Data → Remove Duplicates removes rows whose selected columns all match. If you select only Date, Description and Amount, it will delete the second of two identical $4.50 coffees on the same day, which is a real transaction.

Safer options:

• Include Balance in the check. Two real transactions can’t share the same running balance, unless one of them is zero. Select Date, Description, Amount and Balance.
• Remove whole duplicate files first. If two Statement values show the same date range, one month was converted twice. Delete one file and refresh.
• Flag, then decide. Add a helper column such as =COUNTIFS([Date],[@Date],[Amount],[@Amount],[Description],[@Description]) and review rows where it’s above 1 against the PDF.

Statements that run mid-month to mid-month don’t overlap by design, but a statement plus a CSV export of the same weeks will.

Check the totals against the statements

The combined table is only right if it reconciles to the statements. This balance chain catches missing rows, duplicates and sign errors.

Build a small summary with one row per statement:

Statement	Opening balance (PDF)	Money in	Money out	Closing (calculated)	Closing balance (PDF)	Difference
2026-01	4,210.55	9,800.00	-8,945.20	5,065.35	5,065.35	0.00
2026-02	5,065.35	…	…	…	…	…

• Money in: =SUMIFS(Amount, Statement, "2026-01", Amount, ">0")
• Money out: =SUMIFS(Amount, Statement, "2026-01", Amount, "<0")
• Closing (calculated): opening plus money in plus money out.
• Difference: calculated minus the closing balance on the PDF. Every row should be zero.

Also check the links between months: each month’s opening balance should equal the previous month’s closing balance. A break means a missing statement. A difference inside one month means a missing, doubled or wrongly signed row. Compare that month with its PDF page by page.

What to do with the combined table

With a clean year in one table, the usual next steps take minutes.

• PivotTable by month and category for a profit and loss view or a budget review.
• Filter by payee to total rent, payroll or a supplier for the year.
• Share it with an accountant as one file, or send it to Google Sheets to work on together.
• Import it into accounting software one month at a time; see how to import a bank statement into Xero.

A year of statements of 5 pages each is 60 pages, within a Starter plan’s 500 pages a month; see pricing.