Bank statements

How to merge bank statements from four banks into one sheet (UK)

Stack four bank exports in Google Sheets with one formula, tag each with its account, spot own-account transfers and build monthly totals in pounds.

Editorial illustration for How to merge bank statements from four banks into one sheet (UK)

Four statements can be combined in Google Sheets without endless copying and pasting: put each export on its own tab with matching columns, then stack them with one formula that sorts everything by date. The exports still need cleaning first, because each bank lays its columns out differently.

Which tabs and columns do I need?

Make five tabs: Current, Savings, Card, Joint and Master. Paste each bank's export into its own tab, then reshape the first four to the same four columns, in the same order: Date, Account, Description, Amount. Keeping the order identical matters because the stacking formula joins ranges by position, not by header.

TabTypical raw shapeWhat you change
CurrentPaid out and paid in columnsAmount =E2-D2
SavingsSigned amountAdd the Account column only
CardPurchases as positive numbersAmount =-D2
JointDate in last columnMove the column so Date is first

What is the formula that stacks them?

On the Master tab, in A2, enter this single formula and the stacked result fills down by itself:

  • =SORT({Current!A2:D500; Savings!A2:D500; Card!A2:D500; Joint!A2:D500}, 1, TRUE)
  • The semicolons inside the curly brackets stack the ranges vertically; commas would place them side by side.
  • The 1 means sort by the first column, the date, and TRUE means oldest first.
  • If a range has blank rows, wrap the stack so they are removed: =FILTER({...}, INDEX({...},,1)<>"")

This is Google Sheets syntax. Excel 365 has a similar stacking function, but Excel 2019 and older do not, so there you would paste rows instead. Whichever you use, dates must be real dates, not text, or the sort will arrange them as words.

Will it still work when I add next month's rows?

Yes, as long as you paste new rows underneath the old ones on each account tab and the ranges reach far enough. 500 rows is generous for most people. If one tab grows beyond that, extend the end number in all four ranges at once.

How do I deal with money moved between my own accounts?

Say you transfer £400 from Current to Savings on 1 April. Current shows -400 and Savings shows 400. In a summary of spending these cancel out in reality but not in a naive total, because the minus line looks like an outgoing. Add a Type column to Master and label each as Transfer. Do the same when you pay off the card: a £725.60 payment from Current is not spending, and the purchases already sit on the Card tab.

Finding them: sort a copy of Master by amount, then look for equal and opposite values on nearby dates, such as -400 and 400 within two days. A formula can help flag candidates: =COUNTIFS($D$2:$D$500, -D2, $A$2:$A$500, ">="&A2-2, $A$2:$A$500, "<="&A2+2) returns one or more when an opposite amount sits within two days. It will also flag a few honest coincidences, so confirm each one by reading the descriptions.

How do I check I did not lose anything?

  1. Count the rows on each account tab with =COUNTA(D2:D500) and add them.
  2. Compare with =COUNTA(Master!D2:D2000).
  3. For one account, add the opening balance to the sum of its amounts; the result should equal the closing balance on the statement.
  4. Spot check three random lines against the bank's own screen.

A worked check, with hypothetical numbers: Current opens at £1,240.00, takes in £2,100.00 and pays out £1,875.50. Opening plus movement is 1240 + 2100 - 1875.50 = 1,464.50, and that should match the closing balance printed on the statement.

When should I move beyond a spreadsheet?

When the monthly routine eats an evening. SpendixAI lets you choose the account for each uploaded PDF, CSV or Excel statement and keeps all of it in one categorised record, with rows reviewed before saving. It is built around India and INR, so it only fits if you also hold Indian accounts; for pound accounts, the sheet method above is the one to use.

SPENDIXAI JOURNAL

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

Explore your reports