The last step is the one most guides leave out, and it is the one that matters. A spreadsheet with a missing line looks exactly like a spreadsheet without one. Only the arithmetic against the printed closing balance tells them apart.
Which method should you use?
| Method | Best for | Main risk |
|---|---|---|
| Bank CSV download | Recent months on an open account | Older statements and closed accounts often have nothing to export |
| Copy and paste from the PDF | One short, simple statement | Columns merge and wrapped descriptions split into extra rows |
| Excel Get Data from PDF | Digital PDFs on Excel for Windows with Microsoft 365 | Each page arrives as its own table; scans are not read |
| Bank statement converter | Many statements, scans, catch-up work | You have to trust the tool, or check it |
Method 1: download a CSV from your bank
This is the cleanest route, because nothing has to be read off a PDF.
- Sign in to online banking and open the account.
- Look for Download, Export or Transactions → Export.
- Choose CSV, or Excel if offered, and the date range.
- Open the file in Excel.
Many banks only export a limited stretch of recent history this way, and closed accounts usually have nothing to export. That is when people end up with PDFs.
Method 2: copy and paste from the PDF
For a single page with a simple layout:
- Open the PDF, select the transaction table and copy.
- In Excel, right-click A1 and choose Paste Special → Text.
- If everything lands in one column, use Data → Text to Columns.
What usually goes wrong:
- Wrapped descriptions become their own rows with no date or amount.
- Debit and credit columns collapse into one, so money in and money out look the same.
- Page headers and “balance brought forward” lines land in the middle of the data.
- Amounts paste as text: left-aligned and they will not sum. Convert them with
=VALUE(A2).
Method 3: Excel's Get Data from PDF
With Excel for Windows and a Microsoft 365 subscription, Excel can read tables out of a digital PDF.
- Data → Get Data → From File → From PDF, then pick the statement.
- The Navigator lists every table and page it found. Tick the transaction tables.
- Click Transform Data to open Power Query.
- For a multi-page statement, use Append Queries to stack the page tables.
- Remove header rows repeated on each page, set the date and amount types, then Close & Load.
It only reads PDFs that carry a text layer. A scanned statement is a picture of text, and Excel will not see a table in it; see converting scanned bank statements for that case. Continuation pages without column headers are where rows go missing or shift columns, so look at the last rows of each page after appending.
Method 4: use a bank statement converter
A converter reads the statement layout, dates, descriptions, debits, credits and running balance, and writes one row per transaction. It is the practical option when you are catching up months of statements or dealing with scans. What separates converters is what happens when the reading goes wrong.
SheetParser runs the opening-to-closing check on every statement and shows the difference before you download. If it does not tie, the rows where the running balance breaks are flagged rather than adjusted, and for a PDF upload the original page sits beside the rows so you can check a flagged cell against the print. Start with the bank statement to Excel converter; if the statements are going into QuickBooks rather than a spreadsheet, the QuickBooks PDF converter writes a .QBO file with the same check.
How to check a converted bank statement in Excel
Whichever method you used, run these two checks before anyone relies on the sheet. Assume dates in column A, descriptions in B, debits in C, credits in D, the printed balance in E, the opening balance in E2 and transactions from row 3 down.
Check 1: the whole statement ties
In any empty cell, with the printed closing balance in place of the last figure:
=ROUND(E2 + SUM(D3:D500) - SUM(C3:C500) - 3715.20, 2)The answer should be 0. Anything else means a line is missing, duplicated or misread.
Check 2: find the row where it breaks
In F3, then fill down:
=IF(ROUND(E2 + D3 - C3 - E3, 2) = 0, "", "CHECK")
The first CHECK is where your data and the printed running balance part ways. If the statement has a
single amount column, negative for money out, use =ROUND(E2 + C3 - E3, 2) instead.
What the size of the difference tells you
| The difference is | Most likely cause |
|---|---|
| Exactly one transaction's amount | That row is missing, often the last line on a page |
| Exactly twice an amount | A debit was read as a credit, or the other way round |
| Off by a factor of 10, 100 or 1,000 | A decimal point or thousands separator was misread |
| Equal to a whole page's activity | A page was converted twice |
Do not plug the gap with a balancing entry. Find the row on the page. The balance-check guide goes through each pattern in more detail.
Step by step
- Ask the bank for a CSV first. Online banking often exports recent transactions as CSV, which needs no PDF reading at all.
- Pick a method for the PDF. Copy and paste for one short page, Excel's Get Data from PDF for digital PDFs on Windows, a converter for many statements or scans.
- Put the columns in order. Date, description, debit, credit and the printed running balance, with the opening balance above the first row.
- Check the whole statement ties. Opening balance plus credits minus debits must equal the printed closing balance, to the cent.
- Find the row where it breaks. Compare each row's calculated balance with the printed one. The first mismatch is where to look on the page.
Cleaning up after conversion
- Dates stored as text. Data → Text to Columns → Date, choosing the order the statement uses: MDY for US banks, DMY for UK and Australian banks.
- Amounts in brackets such as (45.20): strip the brackets and make the value negative.
- CR and DR suffixes. Split them into their own column, then flip the sign for DR.
- Several months in one sheet. Add a statement-month column first. Last month's closing balance should equal this month's opening balance; a gap means a missing statement.