How to convert a PDF bank statement to Excel

A PDF has no idea it contains a table. It stores instructions for drawing a page, such as which text goes where and where the lines are. The columns on a bank statement are words that happen to line up, and nothing in the file connects an amount to the date beside it. That's why a statement that looks perfectly tidy turns into a mess when you try to get it into Excel.

There are four ways to do it. They all work, and they each go wrong in their own way. Below is how to do each one, what to watch for, and a check at the end that tells you whether any transactions went missing.

1. Ask your bank for a CSV instead

Before converting anything, see whether you need to. Most online banking sites will download your account activity as a CSV file, and many also offer QFX or OFX for Quicken and QuickBooks. The option is usually labelled Download or Export, or shown as a download icon, and it lives on the transactions page rather than the statements page.

If your bank offers it, use it. People end up with PDFs anyway for a few reasons. Many banks only export the last few months, while statements go back years. Closed accounts often leave nothing but the statements. Bookkeepers, accountants, lenders and landlords are handed statements, not logins. And an activity export can include pending transactions or cover different dates, so it won't reconcile with the statement you were asked to work from.

2. Copy and paste

For a one-page statement, you can select the transactions in a PDF viewer and paste them into Excel. Each line usually lands in a single cell, so you split it afterwards with Data → Text to Columns in Excel or Data → Split text to columns in Google Sheets.

The trouble starts with anything longer. A description that wraps onto two lines in the PDF pastes as two rows, and the second one has no date or amount. Spaces inside descriptions split them across several columns and push the amounts out of line. When debits and credits are printed in separate columns, they paste as a single number with no sign, so money in and money out look the same. Page headers, footers and "balance carried forward" lines come along as well and have to be deleted by hand. For a page or two that's manageable. For a year of statements it isn't.

3. Excel's built-in PDF import

Excel for Microsoft 365 on Windows can open a PDF directly from Data → Get Data → From File → From PDF. It uses Power Query to find what look like tables on each page and lets you choose which ones to load. Not every version of Excel has this, and Google Sheets has nothing like it.

On a cleanly laid-out statement it does a decent job. Expect to fix a few things, though. Each page usually comes in as its own table, so a twelve-page statement means appending twelve tables and deleting the repeated header rows. Wrapped descriptions still turn into extra rows. Dates and amounts arrive as text if they carry currency symbols, a trailing minus or "CR", or a date format your regional settings don't expect. And the account summary and daily balance lists get picked up as tables too, so you have to work out which one holds the transactions.

4. A dedicated statement converter

Tools made for bank statements take care of most of the above. They rebuild each transaction from where its pieces sit on the page, join wrapped descriptions back onto the right row, and leave out headers, footers and summary lines. Once you're dealing with more than a few statements, or statements from several banks, one of these will save you real time.

When you pick one, find out what it does with your file. Many upload it to a server, and some pass it on to other companies. There's a separate guide on what to ask before uploading a statement. Also check whether it tells you when it has made a mistake, because a spreadsheet with one row missing looks just like a complete one.

Check that it balances

However you convert, finish with this check. Every statement prints an opening balance and a closing balance, and the transactions in between have to account for the difference exactly.

opening balance + money in − money out = closing balance

If your debits are in column C and your credits in column D, this takes one formula. Type the opening balance into an empty cell away from the data, such as H1, and compare the result with the closing balance on the statement.

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

If the two match to the cent, nothing was dropped, duplicated or put on the wrong side. If they don't, the size of the gap usually tells you what went wrong, and the running balance column can point to the exact row. How to track down the difference explains both.

Credit card statements work the other way round. The balance is what you owe, so purchases add to it and payments take it down.

Cleaning up for Excel

Once the rows are right, make sure Excel sees the amounts as numbers. Numbers stored as text are usually left-aligned, and SUM skips them. Take out currency symbols and thousands separators, and turn (45.00), 45.00- and 45.00 CR into ordinary negative or positive values.

Then check the dates. A UK or Australian statement writes 3 April as 03/04, which Excel on US settings reads as 4 March, and it won't convert 25/04 at all. Sorting by date is a quick test. If the order comes out jumbled, this is usually the reason.

How statement2sheets does it

statement2sheets is a converter of the fourth kind with the balance check built in. You drop in a PDF and it reads the statement in your browser, so the file is never uploaded. It shows you every transaction, tells you whether they reconcile to the closing balance (and by how much if they don't), and lets you correct any row. Then you download an Excel workbook with real date and number cells, or CSV, JSON or OFX. Try it on a statement. Your first page is free and you don't need an account.