Spreadsheet reconciliation
How Do I Reconcile PDF Bank Statements in Excel?
Reviewed 12 September 2026
Short answer
Short answer
Enter the printed opening and closing balances separately from the transaction table. Total debits and credits, then calculate the expected closing balance using the statement's own sign convention. Compare that result with the printed closing balance and investigate any difference. Where running balances are present, also check that each transaction amount matches the movement between consecutive balances.
Reconciliation is what turns extracted rows into data you can rely on. A zero difference is evidence that the numbers fit, although descriptions and dates still need review.
Use controls printed by the bank
For a normal transaction account, the common calculation is opening balance minus debits plus credits. Credit cards and overdrafts may use a different presentation, so confirm the statement convention before applying one formula everywhere.
If the difference is not zero, look for missing page-boundary rows, duplicated overlaps, reversed amounts and numbers stored as text. Do not insert an unexplained balancing line.
Step by step
- 01
Record the controls
Copy the account, statement period, opening balance and printed closing balance from the PDF.
- 02
Total each direction
Calculate debits and credits separately so a reversal cannot hide inside one net amount.
- 03
Calculate closing balance
Apply the convention shown by the statement and compare it with the printed figure.
- 04
Investigate the difference
Trace missing, duplicated, text-formatted or reversed values back to their source pages.
Checks before you rely on the spreadsheet
- The account and period match the source PDF.
- Debit and credit totals are separate.
- The statement convention is documented.
- The final difference is zero or explicitly explained.
Download the templates
Free CSV files. No email address or account required.
Questions people ask
What formula reconciles a bank statement?
For many transaction accounts it is opening balance minus debits plus credits equals closing balance. Verify the convention printed by the bank, especially for cards and overdrafts.
Does a zero difference prove every description is correct?
No. It shows the amount movement fits. Dates, descriptions and transaction ownership still need checking.
Is there a free reconciliation template?
Yes. The Excel guide hub includes a no-email CSV template for statement controls and differences.