Set up your data first
Reconciliation is only as good as the data you feed it. You need two datasets for the same date range: the bank's transactions (from your statement) and your own records (from your accounting software or a ledger you keep).
Put each dataset on its own worksheet or in two side-by-side blocks. Give each a consistent set of columns: date, description, amount, and a status column you'll use later. Keep amounts as numbers, not text. Decide on a sign convention and stick to it: for example, deposits positive and withdrawals negative.
If your bank statement is a PDF, you'll need to get the lines into rows and columns before you can work with them. You can retype short statements, copy and paste, or convert the PDF to a spreadsheet. Whatever method you use, spot-check a few rows against the original so a stray character doesn't throw off a total.
Match transactions between the two sets
The core of reconciliation is matching each bank line to a corresponding line in your books. Sort both datasets by date, then by amount, so like transactions line up.
For faster matching, add a helper column that combines date and amount into a single key, then use a lookup formula. A COUNTIF against the other sheet's amounts tells you whether a matching value exists. XLOOKUP or MATCH can flag which row it pairs with. Mark each matched pair in your status column so you can filter out what's already done.
Watch for one-to-many situations: a single bank deposit may cover several invoices, and one book entry might clear as two bank lines. When the amounts don't line up one to one, group them manually and note the grouping so the totals still tie out.
Identify and explain the differences
After matching, the unmatched items are what you investigate. Filter each dataset to show only rows still marked open. These usually fall into a few categories.
Timing differences are the most common: outstanding checks you've recorded but the bank hasn't cleared, and deposits in transit that you've booked but haven't posted yet. These are legitimate and simply carry forward. Bank items you haven't recorded yet, such as service fees, interest, or automatic charges, need to be added to your books.
Errors are the rest: a transposed amount, a duplicate entry, or a transaction posted to the wrong period. Correct these in your records, not on the statement. Keep a short note next to each adjustment so anyone reviewing the reconciliation later can follow your reasoning.
Prove the balance ties out
A reconciliation is complete when a simple arithmetic proof holds. Start from the bank statement ending balance. Add deposits in transit, then subtract outstanding checks. The result should equal your adjusted book balance.
Build this as a small block of cells: statement ending balance, plus and minus the outstanding items, equals the reconciled balance. In a separate block, take your book balance and apply the adjustments you identified, such as fees and interest. When both blocks land on the same number, you're reconciled.
Add a cell that subtracts one from the other and should read zero. Highlight it with conditional formatting so a non-zero difference stands out. Save the file with the period in the name and lock or protect the sheet if others will view it, so the finished work stays intact.