How to reconcile a bank statement converted from PDF
The most important minute in any statement conversion is the one after it. This guide shows the simple balance test that proves every transaction was captured with the right sign, and how to track down the cause when the numbers disagree.
By Muhammad Sharif · October 2026 · 8 min read
Whatever tool produced your spreadsheet, whether a converter, Excel's PDF import or hand typing, the risk is the same: a row can be skipped, repeated, or given the wrong sign without anything looking broken. A single missing $2,400 payment still gives you a tidy-looking table. The fix is not to trust your eyes but to let arithmetic do the checking, using figures printed on the statement itself.
The one equation that proves your conversion
Every bank statement is an accounting identity. Over the statement period:
Opening balance + all deposits − all withdrawals = closing balance
Your spreadsheet holds the deposits and withdrawals. The statement's summary box holds the opening and closing balances. If the sheet, run through the equation, reproduces the printed closing balance exactly, then the transactions you captured add up to the transactions the bank processed. It is not a proof that each description is perfect, but it is a very strong proof that no money is missing or double counted.
Step by step in Excel or Google Sheets
- Find the summary box. Near the top of the first page, the statement shows "Beginning balance" (or "Opening balance", "Previous balance") and "Ending balance" (or "Closing balance"). Write both down.
- Put the amounts in one signed column. Deposits positive, withdrawals negative. If your sheet has separate debit and credit columns, create a helper column with
=Credit-Debit. - Sum the column. In an empty cell, enter
=SUM(C2:C500)adjusting the range to your data. - Compute the expected closing balance. In another cell:
=OpeningBalance + SumCell. - Compare. The result should equal the statement's closing balance to the cent. Use
=ROUND(Expected-Closing,2); the answer should be 0.
A second check using the running balance
If the statement prints a balance after each transaction (most checking accounts do), you get a stronger test. For every row, the balance should equal the previous row's balance plus this row's amount. In a spare column, enter for row 3: =ROUND(D2+C3-D3,2). Every cell in that column should be 0. Any row that returns a number is the exact place where the data stops making sense: a missing row immediately above, a sign error on that row, or two rows swapped.
The running-balance check also catches the case where the totals happen to cancel out. Two errors of the same size and opposite sign will fool the summary check but not the row-by-row one.
When the numbers don't match
The size of the difference tells you what went wrong. Work through these in order.
| Difference | Likely cause | What to do |
|---|---|---|
| Exactly equals one transaction | A row was dropped or duplicated | Search the sheet for that amount; if absent, find it on the statement and add it; if present twice, delete one |
| Exactly twice a transaction | A sign was flipped on one row | Find that amount; check whether it is a deposit or withdrawal on the statement |
| Equals the sum of a few small items | Fees or interest lines at the bottom were treated as summary text | Look at the last page and the "fees" section |
| A round figure (e.g. 100.00, 1,000.00) | A digit was misread, for example in OCR output | Compare large rows against the PDF visually |
| Small odd cents | Amounts were rounded, or a thousands separator was misparsed | Check that no amounts were typed or pasted with extra formatting |
| Totally unrelated | Wrong statement period, or two accounts in one file | Confirm the dates and account in the PDF header; combined statements hold several accounts |
Pitfalls specific to PDFs
- Pending transactions. Some banks list pending items below the posted transactions. They are not part of the statement balance, so including them throws the check off.
- Combined statements. A statement that covers checking, savings and a card prints one set of balances per account. Reconcile each section separately.
- Credit cards. A card's balance rises with purchases. Your sign convention flips: purchases increase what you owe. Decide your convention first, then check against "New balance".
- Interest and fee summaries. Year-to-date totals shown on the statement are not transactions and must not be included in the sum.
- Page boundaries. The first or last row on each page is the most likely to be lost or repeated.
Reconciling again inside accounting software
After you import into QuickBooks or Xero, run the software's own reconciliation as well, comparing the statement's ending balance to the ledger. The pre-import check above is faster to repair, because you are fixing a spreadsheet rather than deleting imported bank feed lines. The sequence that saves the most time is: convert, verify here, import, then categorise.
Frequently asked questions
What if the statement has no running balance column?
Use the summary check only. Many credit card statements and some savings statements omit per-row balances. The equation still works with the previous and new balance, as long as you handle the sign convention for cards.
My difference is only a few cents. Is that acceptable?
No, treat any non-zero difference as a real error. Bank statements are exact to the cent, so a few cents usually means a mis-read digit or a decimal problem that could also be hiding a larger issue elsewhere.
Do I need to do this for every statement?
For anything that feeds financial records, yes. The check takes about a minute once you have a template sheet with the formulas in place, and it removes the need to review every row by eye.
