How to Convert a Bank Statement PDF to Excel, and Check Every Row
Getting a bank statement into Excel is easy. Getting it in right is the part that takes the afternoon: a description split over two rows, an amount that landed in the wrong column, a page that quietly went missing. The spreadsheet looks finished either way.
Here are the four ways people do it, what each one gets wrong, and a single formula that checks the result, whichever way you used.
Option 1: copy and paste
Select the transactions in the PDF, copy, and paste into Excel. For one page of a simple statement this can be enough.
More often the whole row lands in a single cell, amounts paste as text that will not add up, and a description that wraps onto a second line becomes a row of its own. Data > Text to Columns can split the cells again, and pasting page after page by hand is where rows get skipped.
Option 2: Excel’s own PDF import
Excel for Microsoft 365 on Windows can read a PDF directly: Data > Get Data > From File > From PDF. It finds the tables on each page and loads the one you pick into a sheet.
It is a real improvement on pasting, with three catches. Each page usually comes in as a separate table, so a twelve-page statement needs appending in Power Query. The column headings repeat on every page and have to be filtered out. And it reads only the text in the file, so a scanned statement gives it nothing. It is also not available in Excel for Mac.
Option 3: ask the bank for a CSV
Most online banking can export transactions as CSV, which opens in Excel with no conversion at all. When it covers the dates you need, it is the cleanest source there is.
The catch is history. Many banks only export a limited window, often a few months, and older periods exist only as PDF statements. We wrote up how far back the main banks let you export. The columns also differ from bank to bank, so two accounts rarely line up in one sheet.
Option 4: a converter
A converter reads the whole PDF and writes the spreadsheet for you. PivotBank gives you a Transactions sheet with Date, Description, Debit, Credit and Balance columns, a Page column so any figure can be found on the paper in seconds, and a Check column that says OK, GAP or UNCHECKED for every row. A Summary sheet says whether the statement balances, and by how much it is out if it does not.
Scanned statements are read too, page by page. The preview of the first five rows is free; the full file comes with a one-off unlock or a Pro plan.
Check it in Excel, whichever way you used
Every method above can drop or double a row without telling you. The statement itself can catch it: each running balance is the one before it, plus the money in, minus the money out.
Put the rows in date order, oldest first, with Debit in column C, Credit in column D and Balance in column E, starting on row 2. In F3 type =ROUND(E2+D3-C3-E3,2) and fill it down. Every row should show 0. The first row that does not is where a transaction is missing, doubled, or on the wrong side, and that is the only row you need to look at on the PDF.
Two things make the formula misfire. Amounts stored as text count as nothing, so convert them to numbers first. And a credit card statement prints no running balance at all; compare your totals with its summary box instead, which is how we prove card statements.
Which one to use
A rough guide:
- One page, once: copy and paste, then the check formula.
- Recent months your bank still exports: the bank’s CSV.
- Digital PDFs on Windows, and time to tidy: Excel’s PDF import.
- Older statements, scans, many pages or many clients: a converter that checks the balance for you.
Try it on a statement
Drop a statement into the PDF to Excel converter and the preview shows which rows the running balance vouches for before you download anything. To check a file you already have, verify it against its own balances. If the next step is accounting software rather than a spreadsheet, CSV or OFX will save you the mapping.