How to combine monthly bank statements into one spreadsheet

What goes wrong with statement exports

Bank and card portals export one period at a time, and each export has quirks. Dates arrive as text (“03/04/2026”), money in and money out are separate columns, the same transaction appears in two files when the download ranges overlapped, and fuel card statements list one row per fill-up with a vehicle registration. Stacking the files is easy; making them consistent is the real work.

Step 1: stack the exports

Download CSV rather than PDF where you can; PDF statements need converting first and lose precision. Put the files in one folder and use Power Query's From Folder (see combining files from a folder), or DuckDB:

SELECT *, filename AS source_file
FROM read_csv('statements/*.csv', union_by_name = true, filename = true,
              dateformat = '%d/%m/%Y');

Setting the date format explicitly matters: 03/04/2026 is 3 April in the UK and 4 March in the US, and auto-detection can pick the wrong one when no day exceeds 12. Add an Account column (current account, savings, fuel card) from the file name or folder.

Step 2: one amount column with consistent signs

Some banks give Paid in and Paid out; others give one Amount with negatives; card statements often show spending as positive. Pick one convention — money out negative is the usual choice — and convert everything:

=N(C2) - N(D2)

where C is Paid in and D is Paid out; N() turns blanks into 0. For a card statement where spending is positive, multiply by -1. Strip currency symbols and thousands separators if the export includes them (=VALUE(SUBSTITUTE(SUBSTITUTE(E2, "£", ""), ",", ""))).

Step 3: remove overlap duplicates

If March's export ran to 2 April and April's started on 1 April, the transactions on 1–2 April appear twice. Exact-duplicate removal works if both exports are identical rows, but genuine repeats exist too — two £3.20 coffees on the same day are both real. The safest key includes the running balance, which differs between genuine repeats:

SELECT * FROM (
  SELECT *, row_number() OVER (
           PARTITION BY account, date, description, amount, balance
           ORDER BY source_file) AS rn
  FROM statements)
WHERE rn = 1;

If the export has no balance column, a better approach is to avoid overlaps at the source: cut each file to whole calendar months before stacking (filter on date), so no transaction can come from two files. In Excel, Remove Duplicates across Date, Description, Amount and Balance does the same job as the query.

Check the closing balance

The combined table proves itself when opening balance plus the sum of amounts equals the closing balance on the last statement:

A difference means a missing file, a remaining duplicate, or a sign flipped on one export. Matching the result against your ledger is a separate step, covered in reconciling a bank statement with the general ledger.

Fuel card statements

Fleet fuel card exports stack the same way, with extra columns: vehicle registration, driver, litres, site and sometimes odometer. Standardise registrations (upper case, no spaces: “AB12 CDE” becomes “AB12CDE”) so a vehicle isn't split in two, and keep litres and price per litre so you can spot outliers. With all months in one table, cost per vehicle per month is a pivot with Registration in rows and Month in columns.

Analistable loads statement CSVs into DuckDB in your browser, so bank data isn't uploaded or stored on a server, and you can ask “total fuel spend per vehicle per month” or “which transactions appear in two files?” and see the SQL used. It reads the files without changing them. For other consolidation methods, see the consolidate data in Excel guide.

Categorising once the table is combined

Most people combine statements to answer questions such as spend by supplier or by category. Add a Category column using a rules table of description keywords ("TESCO" to Groceries, "SHELL" to Fuel) and a lookup that searches each description for the keywords. In Microsoft 365, =XLOOKUP(TRUE, ISNUMBER(SEARCH(Rules!A2:A50, B2)), Rules!B2:B50, "Uncategorised") returns the first rule that matches.

Order the rules from specific to general so "AMAZON PRIME" is found before "AMAZON". Review the Uncategorised rows each month and add rules for the frequent ones; after two or three months most transactions categorise themselves.

Frequently asked questions

Can I combine statements from two different banks?

Yes. Map each bank's columns to the same names, set each file's date format, fix signs, and add an Account column before stacking.

How do I combine bank statements from different months in Excel?

Export each month as CSV into one folder, use Data → Get Data → From Folder to stack them, then fix dates and signs and remove overlap duplicates.

Why are some transactions duplicated after combining statements?

The export date ranges overlapped. Remove duplicates on date, description, amount and running balance, or trim each file to whole months first.

Why are my statement dates in the wrong month?

The day and month were read the wrong way round. Import with the bank's date format (DD/MM/YYYY for UK banks) instead of relying on auto-detection.

Should money out be negative?

Use one convention across every file. Money out as negative in a single Amount column is the simplest to sum and check against balances.

Related guides

• How to do a bank reconciliation in Excel
• How to combine files from a folder with Power Query
• How to consolidate data in Excel
• How to combine monthly expense claims into one spreadsheet
• How to combine project status trackers into one spreadsheet