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

  1. Open the StatementLocal converter and drop the statement PDF on the page. The file is read inside your browser and is never uploaded.
  2. 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.
  3. 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:

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

  1. 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.
  2. 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.
  3. 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.
  4. 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:

DateEntry in the booksAmountOn the statement?
05/03Check 1041, supplies-420.00yes
05/09Deposit, invoice 881500.00yes
05/15Payroll-1650.00yes
05/29Check 1042, contractor-300.00no
05/31Deposit, invoice 91600.00no

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:

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

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

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.