How to Combine Multiple Bank Statements Into One Excel Sheet

Banks hand out statements one month at a time, but most of the jobs you need them for span much longer: an annual budget review, a tax return, an audit, a mortgage application or a year of catch-up bookkeeping. Twelve separate PDFs, or twelve separate spreadsheets, make all of those harder than they need to be.

This guide shows how to get several statements into one clean Excel sheet: converting them in a batch, sorting them properly, removing duplicates where periods overlap, proving no statement is missing, and combining more than one account.

Why combine statements at all?

  • Annual review. A full year in one sheet lets you total spending by category, compare months and spot subscriptions you’d forgotten about.
  • Tax. Gathering a year’s income or deductible expenses is far easier from one filtered sheet than from twelve files. Check with a tax adviser what you actually need to report.
  • Audits and accountants. One file with a column showing which statement each row came from gives a clear audit trail.
  • Loans and mortgages. Lenders often ask about several months at once; see bank statements for a mortgage application.
  • Catch-up bookkeeping. Months of transactions can be imported into accounting software in one go; see our guide to importing a bank statement into QuickBooks.

Step 1: Convert all the statements in one batch

The quickest route is to convert every PDF at once. With pdf2statement, you drop all the statements onto the page, check the preview, and choose one of two Excel layouts:

  • One combined sheet with a Source file column. Every transaction from every statement in a single table, with the file name next to each row. This is the layout to use for analysis, pivot tables, filtering and de-duplication.
  • One sheet per statement. Each statement on its own tab, which suits a statement-by-statement audit trail or comparison with the original PDFs.

Either way, the workbook also gets a Summary sheet listing each file with its period, number of transactions, money in, money out, opening and closing balances, and whether every row agreed with the running balance. Rows that didn’t are highlighted in the preview before you export, so you can fix conversion problems before they reach the combined sheet. The bank statement to Excel page covers the export settings.

Two settings make merging easier:

  • One signed Amount column instead of separate Debit and Credit columns. Duplicate checks and totals then need only one amount field.
  • The same date format for every file. Mixing formats is a common cause of dates that won’t sort.

The examples below use this layout. If your file has extra columns, such as a Reference column, adjust the letters.

Column Contents
A Date
B Description
C Amount (negative for money out)
D Balance
E Source file
F Seq (added in step 2)

Step 2: Sort chronologically without scrambling same-day order

Within a single day, the order of transactions matters, because the running balance follows that order. Before sorting, record the original order:

  1. Type 1 in F2 and 2 in F3, select both cells and double-click the fill handle to number every row.
  2. Go to Data > Sort, sort by Date (oldest to newest), then add a level for Seq (smallest to largest).

Same-day transactions now stay in the order the statement printed them, wherever their file sat in the original list. In Microsoft 365 you can also produce a sorted copy with a formula on another sheet, for example =SORTBY(Sheet1!A2:F2000,Sheet1!A2:A2000,1,Sheet1!F2:F2000,1). Make sure the ranges cover only your data rows, since blank rows would sort to the top.

Step 3: Remove duplicates from overlapping periods

Overlaps happen more often than you’d expect: a statement downloaded for a custom date range, a year-end statement alongside monthly ones, an account that changed statement dates, or the same month downloaded twice.

The basic check

In G2, count how many times this date, description and amount have appeared so far:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)

Fill it down. A result of 1 means the first appearance; 2 or more means a repeat. The range grows as you go down, so the first copy of each transaction is never flagged.

The weakness is genuine repeats. Two identical 4.50 coffees bought at the same café on the same day are real transactions, and this formula flags the second one.

Better: include the balance

Two genuine identical transactions leave different running balances, whereas an overlap copy repeats the same balance. Adding the Balance column to the match separates them:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2)

This needs a printed balance on every row. Many statements only print a balance at the end of each day, and COUNTIFS doesn’t handle blank criteria the way you’d want, so for those use the next version.

Or: only count matches from another file

Flag a row when the same date, description and amount appears in a different source file:

=COUNTIFS($A$2:$A$2000,A2,$B$2:$B$2000,B2,$C$2:$C$2000,C2,$E$2:$E$2000,"<>"&E2)

Any result above 0 means another statement contains the same transaction. Both copies are flagged, so filter for flagged rows and delete the copies belonging to one of the two statements, keeping the other.

Deleting the duplicates

Filter the flag column, select the visible rows, delete them, and clear the filter. Work on a copy of the sheet the first time.

Two cautions:

  • COUNTIFS treats *, ? and ~ in a description as wildcards, and criteria longer than 255 characters cause an error. Both are rare in bank descriptions but not impossible.
  • Excel’s Data > Remove Duplicates is tempting, but if you tick only Date, Description and Amount it deletes genuine repeats too. If you use it, include Balance, and leave Source file and Seq unticked because they’re always different.

Step 4: Check continuity

Sorting and de-duplicating can’t tell you whether a whole statement, or a page of one, is missing. Two checks can.

Closing balance equals the next opening balance

On the Summary sheet, or a small table of your own, list each statement in date order with its opening and closing balances. If opening balances are in column G and closing balances in column H, with the first statement in row 4, put this next to the second statement and fill down:

=IF(ROUND(G5-H4,2)=0,"OK","Gap?")

Each statement should open exactly where the previous one closed. A “Gap?” means a missing statement or missing transactions between the two.

Running balance across the joins

On the combined, sorted sheet, add a check in H3 and fill down:

=ROUND(D2+C3-D3,2)

That’s the previous balance plus this row’s amount minus this row’s balance, and it should be 0 on every row. A non-zero result on the first row of a new source file means something is missing between two statements. A non-zero result in the middle of a file means a missed or misread transaction, or a duplicate you didn’t remove, or a genuine repeat you removed by mistake. It’s the best final test of steps 2 and 3. This check also needs a balance on every row; for statements that print one balance a day, reconciling a bank statement in Excel has a version that skips the blanks.

Step 5: Combine several accounts

You can merge current, savings and card accounts on one sheet if you add an Account column (column I in this layout).

  1. On a separate sheet called Files, list each source file name in column A and its account in column B.
  2. In I2, look up the account: =XLOOKUP(E2,Files!$A$2:$A$50,Files!$B$2:$B$50,"?"). In older Excel, =VLOOKUP(E2,Files!$A$2:$B$50,2,FALSE) does the same.
  3. Sort by Account, then Date, then Seq.
  4. Make the running balance check account-aware, so it doesn’t compare balances from two different accounts: =IF(I3<>I2,"",ROUND(D2+C3-D3,2)).

Things to watch:

  • Transfers between your own accounts appear twice, out of one account and into another. Label them so they don’t inflate income and spending totals; our guide to categorising bank transactions in Excel shows how.
  • Credit card balances move the other way. Purchases increase what you owe, so check the signs and run continuity checks on the card separately. See credit card statement to Excel.
  • Different currencies should stay in separate columns or sheets. Adding euros to dollars gives a meaningless total.

Alternative: Power Query append

If your statements come from several tools as CSV files, or you add a new month every few weeks, Power Query can combine a folder of files and refresh on demand. In Excel for Windows:

  1. Save every CSV file into one folder. A CSV export works well for this.
  2. Go to Data > Get Data > From File > From Folder and choose the folder.
  3. Click Combine & Transform Data and pick a sample file. Power Query applies the same steps to every file and adds a column with each file’s name.
  4. Set the Date column’s type (use Change Type > Using Locale if the dates are day-first and your computer expects month-first) and set Amount to a decimal number.
  5. Click Close & Load. Next month, drop the new file into the folder and choose Data > Refresh All.

To stack tables that are already in your workbook, use Append Queries in the Power Query editor instead. Power Query also has its own Remove Duplicates command, with the same caution about genuine repeats. Power Query support in Excel for Mac is more limited than on Windows, so check which connectors your version offers.

A final checklist

  • Every statement converted, with every row agreeing with its running balance
  • One combined sheet, sorted by date with same-day order preserved
  • Overlap duplicates removed, genuine repeats kept
  • Opening and closing balances continuous from one statement to the next
  • An Account column if more than one account is involved

With that done, the sheet is ready for whatever comes next: categorising, a pivot table, an import into accounting software or a lender’s questions. Whichever bank issued the statements, the steps are the same; for layout notes on particular banks, see our bank pages.

FAQ

Frequently asked questions

How do I merge 12 monthly bank statements into one spreadsheet?

Convert all twelve PDFs in one batch and export them into a single sheet with a column showing which file each row came from. Then sort by date, remove any duplicates from overlapping periods, and check that each statement's closing balance equals the next one's opening balance.

How do I find duplicate transactions after combining statements?

Use COUNTIFS to count how often the same date, description and amount appear. Because genuine repeats happen, such as two identical coffees on one day, include the running balance in the match, or count only matches that come from a different source file. Those are almost always overlap duplicates.

How can I tell if a statement is missing?

Line the statements up in date order and compare each closing balance with the next opening balance. They should be identical. A running balance check across the whole combined sheet does the same job row by row: a difference at the first row of a new file points to a gap between statements.

Should I put several bank accounts on one sheet?

You can, as long as you add an Account column. Sort by account before running balance checks, and remember that transfers between your own accounts appear twice: once going out of one account and once coming into the other.