How to convert a PDF bank statement to Excel

A PDF bank statement looks like a table, but it isn’t one. A PDF is a set of instructions for printing a page: put this text at this position, draw a line here. The columns you see are just words that happen to line up. Nothing in the file says “this is the amount for that date”, which is why getting a statement into Excel is harder than it looks.

There are four ways to do it. Each works, but they fail in different ways, so below are the problems to watch for with each one, plus the check that tells you whether anything went missing.

1. Ask your bank for a CSV instead

Before converting anything, check whether you need to. Most online banking sites let you download account activity as a CSV file, and many also offer QFX or OFX for Quicken and QuickBooks. Look for “Download”, “Export” or a download icon on the transactions page, not on the statements page.

If that’s available, use it. The limits are the reason people end up with PDFs anyway:

• Only recent history. Many banks only offer downloads for the last few months, while statements go back years.
• Closed accounts. Once an account is closed, the statements are often all that’s left.
• Other people’s accounts. A bookkeeper, accountant, lender or landlord is usually handed statements, not a login.
• The export doesn’t match the statement. An activity export may include pending transactions, or cover a different date range, so it won’t reconcile to the statement you were asked to work from.

2. Copy and paste

For a one-page statement, selecting the transactions in a PDF viewer and pasting them into Excel can be enough. Usually each line lands in a single cell, so you then split it with Data → Text to Columns in Excel (or Data → Split text to columns in Google Sheets). This is where it gets tedious:

• Descriptions that wrap onto two lines in the PDF paste as two rows, and the second has no date or amount.
• Descriptions containing spaces split into several columns, which pushes the amounts out of line.
• Debits and credits printed in separate columns paste as one number with no sign, so you can’t tell money in from money out.
• Page headers, footers and “balance carried forward” lines come along too, and have to be deleted by hand.

It works, but it doesn’t scale past a page or two.

3. Excel’s built-in PDF import

Excel for Microsoft 365 on Windows can read PDFs directly: Data → Get Data → From File → From PDF. It uses Power Query, finds what it thinks are tables on each page, and lets you choose which to load. Not every version of Excel has it, and Google Sheets has no equivalent.

On a cleanly laid-out statement it can do well. The common problems are:

• One table per page. A twelve-page statement becomes twelve tables that you have to append together, and the header row repeats on each one.
• Wrapped descriptions still become extra rows.
• Dates and amounts come in as text when they include currency symbols, a trailing minus or “CR”, or a date format your regional settings don’t expect.
• Summary boxes are picked up as tables too, like the account summary or a list of daily balances, so you have to recognise which one holds the transactions.

4. A dedicated statement converter

Tools built for bank statements handle the problems above for you: they rebuild each transaction from its position on the page, join wrapped descriptions back onto their row, and skip headers, footers and summary lines. They are worth it once you have more than a few statements, or statements from several banks.

Two things are worth checking before choosing one. First, what it does with your file: many upload it to a server, and some pass it on to other companies. There is a separate guide on what to ask before uploading a statement. Second, whether it tells you when it got something wrong. A tidy spreadsheet with one row missing looks exactly like a correct one.

Whichever way you choose: check that it balances

Every statement prints an opening balance and a closing balance. Whatever happened in between has to account for the difference exactly:

opening balance + money in − money out = closing balance

With your debits in column C and credits in column D, that is one formula. Type the opening balance into an empty cell away from the data, say H1, then compare the result with the closing balance printed on the statement:

=ROUND(H1 + SUM(D:D) - SUM(C:C), 2)

If it matches to the cent, nothing was dropped, duplicated or put on the wrong side. If it doesn’t, the amount it’s off by usually tells you what went wrong, and the running balance column can find the exact row. How to track down the difference walks through both.

For a credit card statement the signs flip: the balance is what you owe, so purchases add to it and payments reduce it.

Cleaning up for Excel

Even once the rows are right, check two things before you use the numbers:

• Amounts are numbers, not text. Text-stored numbers are usually left-aligned, and SUM ignores them. Remove currency symbols and thousands separators, and turn (45.00), 45.00- or 45.00 CR into proper signed values.
• Dates are dates. A statement from a UK or Australian bank writes 03/04 for 3 April. Excel set to US settings reads it as 4 March, and a date like 25/04 doesn’t convert at all. Sort by date: if the order looks jumbled, this is why.

How statement2sheets does it

statement2sheets is a converter of the fourth kind, with the balance check built in. Drop in a PDF and it reads the statement in your browser: the file is never uploaded. It then shows every transaction and whether they reconcile to the statement’s closing balance, and by how much if not. You can correct any row before downloading an Excel workbook with real date and number cells, or CSV, JSON or OFX. Try it on a statement. The first page is free, with no account needed.