Bank reconciliation template

How to do a bank reconciliation in Excel with this template

1. Download the workbook. Replace the sample rows: paste the statement into the first sheet and your ledger into the second. Keep the reference and the amount each in one column.
2. Match the two sheets. Drop the statement and the ledger into the matcher on this page. Rows are paired by reference; amounts are compared within the tolerance you set.
3. Fill the reconciliation statement. Closing balance per bank, plus deposits in transit, minus uncleared payments; balance per ledger, plus interest, minus fees. The two adjusted figures should be equal.
4. Carry the leftovers forward. Anything still unmatched goes into the last block of the reconciliation sheet, so next month starts with the list of what to look for.

What is in the workbook

• Bank statement — Date, Reference, Description, Money in, Money out, Balance. Paste your export over the sample rows; the column order does not matter to the matcher, which reads headers.
• Ledger — Date, Invoice / Reference, Customer, Amount, Status. Your cash book, or the invoices and payments you recorded.
• Reconciliation — the statement itself: balance per bank, adjustments, adjusted balance; balance per ledger, adjustments, adjusted balance; the difference line that has to come to zero; and a block for items carried to next period.
• How to use — the steps above, inside the file, for whoever inherits the spreadsheet.

There are no macros and no external links in the file. It opens in Excel, LibreOffice, Numbers and Google Sheets. The totals in the reconciliation sheet are typed values in the sample; replace them with your own figures or with SUM formulas over the two data sheets.

The part a template leaves to you

Every bank reconciliation template on the first page of results is the same object: a blank statement layout with lines for deposits in transit and outstanding cheques. The slow part of the job is not that page. It is finding which of the 300 statement lines correspond to which of the 280 ledger lines, which is where the matcher above comes in — the same engine as reconcile two tables, pointed at this specific pair of sheets. Leading zeros in references are ignored, amounts are compared within a tolerance, a payment split into instalments can be summed, and the leftovers on each side come out as two lists you can paste straight into the reconciliation sheet.

Where the differences usually come from

• Timing. A cheque issued on the 30th clears on the 2nd; a customer transfer sent on the last day lands next month. These are the "in transit" and "uncleared" lines and they reverse themselves next period.
• Fees and interest. On the statement, not in the ledger until you book them. The matcher shows them as "only in the statement".
• Short payments. Same reference, smaller amount — a card fee deducted, a discount taken, a currency conversion. These are the "amount mismatches", each with the difference next to it.
• Reference drift. The customer wrote the invoice number with a typo or paid two invoices in one transfer. Those rows stay unmatched and are the ones worth a phone call.

Statements that come as PDF

Most banks still hand out PDFs. The bank statement converter turns a text-layer PDF into a CSV in the browser and checks that the running balance adds up along the way, so what you drop into the matcher is already verified.

Nothing leaves this tab

The workbook is generated by JavaScript in your browser; the matching runs in your browser. No statement, ledger or template is uploaded, stored or sent anywhere — open the network tab and watch: nothing carries your rows.