How to reconcile a bank statement in Excel when you only have the PDF
Reconciling a bank statement means two checks. First, the statement's own rows must add up: the opening balance plus every transaction equals the closing balance. Second, those rows must match your own records, such as a check register, a cash book or a list of invoices, so you can see what the bank has that you do not, and the other way round. Both checks are quick in Excel once the statement is rows instead of a PDF.
Step 1: turn the PDF into rows
- Open the StatementLocal converter and drop the statement PDF on the page. The file is read inside your browser and is never uploaded.
- StatementLocal does the first check for you. It adds the rows to the printed opening balance and compares the result with the printed closing balance, and where the statement prints a running balance it checks every row's balance too. Reconciled means everything matches to the cent.
- Download Excel (.xlsx). The sheet is named Transactions, with Date in column A, Description in B, Amount in C and Balance in D.
Step 2: see the arithmetic in Excel
If you want to see the sums yourself, or the verdict said Cannot check because the statement prints no balances, put the totals beside the rows. With amounts in C2 to C31:
- Money in:
=SUMIF(C2:C31,">0") - Money out:
=SUMIF(C2:C31,"<0") - Expected closing balance: the printed opening balance plus
=SUM(C2:C31)
The expected closing balance should equal the closing balance printed on the statement. Many statements also print total deposits and total withdrawals, and your money in and money out should match those too.
Step 3: match the statement against your records
- Put your own records for the same period on a second sheet named Books, with the amount in column C, using the same signs: money in positive, money out negative.
- On the Transactions sheet, head column E In books, enter
=COUNTIFS(Books!C:C,C2)in E2 and fill down. A 0 means the bank has a transaction your records do not, such as a fee or interest. - On the Books sheet, do the reverse in column E:
=COUNTIFS(Transactions!C:C,C2). A 0 there means your records have something the bank has not cleared yet, such as an outstanding check or a deposit in transit. - Where the same amount appears more than once, the count is 2 or more. Check those rows by date and description.
A worked example
A small business's statement for May prints an opening balance of $5,000.00 and a closing balance of $4,310.00. The books started May at the same $5,000.00 and show these five entries:
| Date | Entry in the books | Amount | On the statement? |
|---|---|---|---|
| 05/03 | Check 1041, supplies | -420.00 | yes |
| 05/09 | Deposit, invoice 88 | 1500.00 | yes |
| 05/15 | Payroll | -1650.00 | yes |
| 05/29 | Check 1042, contractor | -300.00 | no |
| 05/31 | Deposit, invoice 91 | 600.00 | no |
The statement holds the first three entries plus a bank fee of -120.00 that the books do not have: $5,000.00 + $1,500.00 - $420.00 - $1,650.00 - $120.00 = $4,310.00, the printed closing balance. Then:
- Bank balance $4,310.00, plus the deposit in transit $600.00, less the outstanding check 1042 of $300.00, gives an adjusted bank balance of $4,610.00.
- The books read $5,000.00 + $1,500.00 + $600.00 - $420.00 - $1,650.00 - $300.00 = $4,730.00. Record the $120.00 fee and they read $4,610.00.
The two adjusted figures agree, so the account is reconciled for May. Check 1042 and the invoice 91 deposit should appear on June's statement.
Limits of this method
- COUNTIFS on the amount alone can pair the wrong rows when two transactions share an amount. Check repeats by date and description.
- StatementLocal's balance check proves amounts, not words. A Reconciled statement can still have a misread date or description.
- StatementLocal reads text-based PDFs only (no scans, photos or password-protected files), in US dollars and common US layouts.
- This page shows the arithmetic of a reconciliation. If you keep books for a business, your accountant's process comes first.
Try it on one of your statements
Open the StatementLocal converter and drop one statement PDF on the page. Converting and previewing are always free, and you can download 3 statements a day free, as CSV, Excel or QuickBooks CSV. The file is read inside your browser and is never uploaded.
What has and has not been tested
We tested StatementLocal on 200 synthetic (made-up) statements across 10 common US statement layouts, built by our own generator. Every statement it could not verify was flagged with Off by or Cannot check, never shown as Reconciled. We have not tested it on every bank's real statements, and we quote no accuracy figure for them.
Related guides
- What to do when the verdict says Off by
- Categorize bank transactions in Excel
- Import a PDF bank statement into QuickBooks Online
- Organize a year of statements for taxes
StatementLocal is not affiliated with any bank, card issuer or software company named on this page. Names are used only to say what the steps are for.
Need more than 3 statements a day? Buy the 30-day pass for $9 or Buy Lifetime for $39. One-time payments, taken by Stripe. Sold to US customers only.
Use Chrome? The same converter is also a Chrome extension: Add to Chrome from the Chrome Web Store.