To convert a bank statement PDF to Excel, pull the transaction table into a worksheet, keep one row per transaction with date, description, amount and running balance, then prove the sheet is complete. Beginning balance plus deposits minus withdrawals must equal the statement's ending balance. If it does not, a row was dropped or doubled.
What to gather before converting a bank statement PDF
Before converting a bank statement PDF, collect every statement in the run, the account list, and any passwords the files need. A conversion that starts with a missing month or an unopenable file stops halfway. Ten minutes of gathering saves a second round of requests to the client.
Start with the account list. A client with a checking account, a savings account and a business line of credit has three runs of statements, and each one needs its own workbook or its own sheet. Write down the bank name, the last four digits of each account and the months the engagement covers, such as "Example Community Bank checking XXXX-4821, January to December."
Next, confirm you have every month. Lay the statement periods side by side and look for a break. A missing March is far easier to spot now than after twelve sheets are built. The post on how to collect bank statements from clients covers asking for the right format in the first place.
Then open each file once. Some statement PDFs are protected with a password, and some are scans of paper. Telling the two kinds apart matters, because a scan has no text layer for Excel to read. The guide on telling a scanned bank statement PDF from a digital one shows the quick test.
Finally, ask whether a PDF is the right source at all. Many banks let account holders download recent activity as a spreadsheet. U.S. Bank's help page on downloading transactions lists CSV, QFX and QBO files covering up to 18 months, and says older transactions are found on the account statements. For anything older than the download window, the PDF statement is the record you convert.
Converting the bank statement PDF to Excel, step by step
Converting a bank statement PDF to Excel means moving its transaction table into rows and columns, then checking the result against the statement's own totals. Excel can read a digital PDF directly through Power Query. The steps below assume a digital statement and a current desktop version of Excel.
Several routes exist, from copy and paste to OCR. The comparison of four ways to convert a bank statement to Excel explains when each one fits. This walk-through uses Excel's built-in PDF import.
- Copy the PDF into the client's working folder. Keep the original untouched, so there is always a clean source to compare against.
- Open a new workbook and import the file. Microsoft's article on importing data from data sources gives the path as Data > Get Data > From File > From PDF. The article notes the PDF connector requires .NET Framework 4.5 or higher.
- Pick the transaction tables in the Navigator. The Navigator lists the tables and pages it found. Statements often show a summary box, the transaction list and a daily balance table as separate tables. Select only the transaction list.
- Choose Transform Data, not Load. Transform Data opens the Power Query Editor, where you can delete header rows, fix types and combine pages before anything lands in the sheet.
- Combine the pages. The Power Query PDF connector documentation says the MultiPageTables option, on by default, combines similar tables on consecutive pages into one. Check that page two's rows follow page one's in date order.
- Remove non-transaction rows. Filter out repeated column headings, "continued on next page" lines and subtotal rows.
- Set column types. Make the date column a date and the amount columns decimal numbers.
- Load the result to a sheet. Name the sheet for the account and month.
- Run the balance check. The tie-out section below shows how.
- Save the workbook. The last section covers naming and format.
If an amount column arrives as text after loading, the fixes for each cause are in bank statement import mistakes that turn amounts into text. Dates that arrive as text have their own fix, covered in the post on why bank statement dates arrive in Excel as text.
Which columns should a converted bank statement sheet keep?
A converted bank statement sheet should keep five columns from the statement: transaction date, description, check number, amount and running balance. Amount can be one signed column or separate debit and credit columns. Add one column of your own for the source page, so any row can be traced back to the PDF.
Here is an example layout for a fictional client, Juniper Hollow Bakery, at a fictional bank:
| Date | Description | Check no. | Debit | Credit | Balance | Page |
|---|---|---|---|---|---|---|
| 03/01 | Beginning balance | 12,480.15 | 1 | |||
| 03/02 | Card deposit, merchant services | 2,140.00 | 14,620.15 | 1 | ||
| 03/04 | Check | 1043 | 860.00 | 13,760.15 | 1 | |
| 03/05 | ACH payment, flour supplier | 1,212.40 | 12,547.75 | 1 |
Split debit and credit columns are easier to check by eye, because a withdrawal can never be mistaken for a deposit. A single signed column is easier to sum and to import into other tools. Pick one per client and keep it, so March and April line up. The post on columns in a bank statement CSV format covers sign conventions in more detail.
The running balance column is worth keeping even though it can be recalculated. It is the statement's own record of where the account stood after each line. When your recalculated balance and the statement's balance part ways, the first row where they differ is where the problem sits.
Leave out what the statement repeats on every page: the bank's address, the account holder's address, marketing messages and page numbers. Keep the header facts once, at the top of the sheet or in the file name: bank, last four digits of the account, statement period, beginning balance and ending balance.
How do you prove the converted bank statement is complete?
A converted bank statement is complete when its rows reproduce the statement's ending balance. Take the beginning balance, add the sum of the credit column, subtract the sum of the debit column, and compare the result with the ending balance printed on the statement. If the statement summary lists item counts, the row counts should match too.
IRS Publication 583 tells business owners that when the bank statement arrives, the statement, the checkbook and the books should agree, and that a checking account should be reconciled each month. The tie-out below applies the same idea to a converted sheet: the sheet has to agree with the statement it came from.
A worked one-month tie-out
This example uses the fictional Juniper Hollow Bakery checking account, XXXX-4821, for March. All figures are made up.
| Line | Statement | Converted sheet |
|---|---|---|
| Beginning balance | 12,480.15 | 12,480.15 |
| Deposits and credits | 18,920.00 (6 items) | 18,920.00 (6 rows) |
| Withdrawals and debits | 16,305.40 (21 items) | 15,893.40 (20 rows) |
| Ending balance | 15,094.75 | 15,506.75 |
The sheet's ending balance is 412.00 too high, and it has one debit row fewer than the statement's count. That points to one missing withdrawal of exactly 412.00. Searching the PDF for 412.00 finds check 1047, the last line on page 2, printed just above the page footer. The import treated it as part of the footer and dropped it.
Add the row back with its page number, and the debits total 16,305.40 across 21 rows. The ending balance becomes 15,094.75, matching the statement to the cent.
Three formulas make this check repeatable. One cell sums the credit column, one sums the debit column, and one computes beginning balance plus credits minus debits, minus the statement's ending balance. That last cell should read zero. Anything else means the sheet is not finished.
A single month tying out does not prove the run is complete. Each month's ending balance should also equal the next month's beginning balance. The post on tying out bank statements month to month shows that check across a full year.
Bank statement PDF conversion errors to catch before saving
Bank statement PDF conversion errors fall into a few patterns: page headers read as transactions, wrapped descriptions split into two rows, signs flipped between debit and credit, and rows lost at page breaks. Each one either changes the totals or hides inside them, so scan for every pattern before the workbook is saved.
Page headers read as rows. The column heading line repeats on every page, and so do lines such as "Account XXXX-4821, page 2 of 4." An import can bring these in as rows. They rarely carry amounts, so they do not change the totals, but they break sorting and filtering. Filter the description column for the heading text and delete the matches.
Wrapped descriptions split in two. A long description that wraps onto a second line can become two rows: one with the date and amount, one with only text. The Power Query PDF connector documentation names this problem and suggests cleaning up multi-line rows with operations such as Table.FillDown or Table.Group. In the sheet, a row with a description and no date or amount is the sign.
Signs flipped. A withdrawal that lands in the credit column throws the tie-out off twice: once for the missing debit and once for the extra credit. The difference between your computed ending balance and the statement's will be exactly twice the amount, which is a useful clue.
Rows lost or doubled at page breaks. The worked example above shows a dropped row. A doubled row happens when the last line of one page is repeated at the top of the next as a "balance forward." Delete it if it carries an amount the statement does not count as a transaction.
Amounts as text. A cell that looks like 1,212.40 but is stored as text will not add into a sum. The total looks low, and no row looks wrong. Convert the whole column to numbers before the tie-out, using the fix that matches the cause.
A single question catches most of these: does the tie-out cell read zero? If it does, check the row count as well. Two errors can cancel each other out in the totals, but they rarely cancel in the count.
Saving the bank statement workbook for its next use
A saved bank statement workbook should be named so anyone in the firm can find it by client, account and period, and saved as xlsx unless the next tool needs CSV. Keep the tie-out cells in the workbook, so the next reviewer can see that the sheet balanced without redoing the check.
A naming pattern such as JuniperHollow_ExampleCommunity_4821_2026-03.xlsx sorts by client, then bank, then account, then month. It reads the same in a file browser, an email attachment and a search box. Use the year and month in that order, so files sort by date without extra work.
Choose the format by what happens next. An xlsx workbook can hold one sheet per month, the tie-out formulas and notes on any correction. A CSV file holds plain values only. Microsoft's article on importing and exporting text and CSV files says that when you save as CSV, only the current worksheet is saved, and other sheets need their own Save As. If an accounting tool needs CSV, keep the xlsx as the working copy and export a CSV per account and month.
Keep the source PDF next to the workbook. The page column points back to it, and a reviewer who doubts a row can open the statement and look. Publication 583 also says records must be kept as long as they may be needed for the administration of any provision of the Internal Revenue Code, so the original statement should not be discarded once the sheet exists.
Doing this for one statement takes a while. Doing it for every account of every client is where the time goes. Schema CPA reads bank, savings and loan statements, ties each statement's balances out against the month before, and exports the rows to Excel or CSV; the workflow section of the homepage walks through those steps. Before trusting any tool's output, run it through the tests in what to check in a bank statement converter.