Free bank reconciliation template

A bank reconciliation compares the cash balance on a bank statement with the cash balance in your general ledger for the same date, then explains every difference between them. A template lays that comparison out on one page: the bank side, the book side, and a check that the two adjusted balances match.

The two balances rarely agree on the day the statement closes. Most of the gap is timing: a deposit you recorded on the 30th that the bank credited on the 1st, a check you wrote that the payee hasn’t cashed. The rest is information only one side has: a fee the bank charged, interest it paid, a customer check that bounced. Then there are plain mistakes, on either side.

The finished page is sometimes called a bank reconciliation statement. You prepare one every statement period, which for most business checking accounts means once a month.

A reconciliation can only be as good as the statement under it. Before you type a single number, confirm three things: the statement covers the whole period, every page is there, and its opening balance equals the bank statement ending balance on last month’s reconciliation. If any of the three fails, the sheet won’t balance, and no amount of ticking will fix it.

1. The period is complete. The start and end dates printed on the statement match the statement period you’re closing. A statement that runs from the 3rd to the 2nd is fine if that’s the bank’s cycle; February’s statement in place of March’s is not.
2. Every page is there. Page numbers like “3 of 5” should run without a gap. A missing page means missing transactions.
3. The opening balance matches. The statement’s beginning balance should equal the “ending balance per bank statement” line on last month’s reconciliation, not last month’s adjusted balance. The template shows the difference if it doesn’t.

Two free tools help here. The bank statement checker can check the statement’s period and account holder. The bank statement converter can turn the PDF statement into a CSV, so you can match it line by line against the ledger.

Missing a month? The missing bank statement tracker lists which account-months you still need and writes the request.

To do a bank reconciliation, put the statement’s ending balance on one side and your ledger’s ending balance on the other, then adjust each side for what it hasn’t recorded yet. Deposits in transit and outstanding checks adjust the bank side. Fees, interest, returned checks and your own errors adjust the books. When the two adjusted balances match, you’re done.

1. Gather the statement and the ledger for the same dates. Pull the bank statement and the general ledger detail for the cash account, both ending on the statement’s closing date.
2. Enter both ending balances. The ending balance per bank statement goes on the bank side; the ending balance per books goes on the book side.
3. Tick off what appears in both. Match each deposit and each withdrawal on the statement to the same entry in the ledger. A CSV of the statement makes this faster; the converter mentioned above produces one from the PDF.
4. List deposits in transit. Deposits in your ledger that the bank hasn’t credited by the statement date.
5. List outstanding checks and other payments not yet cleared. Checks you’ve written and payments you’ve sent that haven’t hit the account.
6. Record the bank-only items on the book side. Service fees, interest earned, NSF (returned) checks, and bank-initiated debits such as a loan auto-pay that the books missed.
7. Correct errors and compare. Fix any error on the side where it happened, compare the two adjusted balances, post the book-side journal entries, then sign and date the reconciliation.

Accounting software runs the same comparison on screen. In QuickBooks Online you enter the ending balance and ending date from your statement, then match transactions until the difference is 0.00 (Intuit, Reconcile an account in QuickBooks Online, read October 8, 2026). The template is useful when you reconcile outside the software, review someone else’s work, or need a signed page for the file.

Amounts in USD. Here is a full bank reconciliation example for an operating checking account, statement period September 1 to 30, 2026. Before any adjustment, the bank statement shows an ending balance of 12,480.00 and the books show 12,784.50: a gap of 304.50. Four book-side items and three bank-side items explain it.

Both sides land on 12,575.00, so the difference is 0.00. The arithmetic: 12,480.00 + 1,950.00 − 640.00 − 1,215.00 = 12,575.00, and 12,784.50 + 4.50 − 25.00 − 180.00 − 9.00 = 12,575.00. This two-sided layout is the standard bank reconciliation format.

The journal entries this example produces

Book side only:

• Dr Cash 4.50 / Cr Interest income
• Dr Bank fees 25.00 / Cr Cash
• Dr Accounts receivable 180.00 / Cr Cash, then re-bill the customer
• Dr the account check #1039 was charged to (an expense, or accounts payable) 9.00 / Cr Cash

The deposit in transit and the two outstanding checks need no entry. They’re already in the books, and they should clear on a later statement, most likely October’s. Next month’s reconciliation starts by ticking them off.

The 9.00 on check #1039 is a transposition: 312 typed for 321. It’s the kind of error the “divisible by 9” test below points at once everything else is listed. Press Load the example in the worksheet above to see these numbers balance.

Each reconciling item goes on the side that hasn’t recorded it yet. The bank hasn’t seen your deposit in transit or your uncashed check, so those adjust the bank balance. You haven’t seen the bank’s fee, its interest or the bounced check, so those adjust your books. Only the book-side items need a journal entry.

The placement follows standard practice. OpenStax’s Principles of Accounting, Volume 1 adds deposits in transit and subtracts outstanding checks on the bank side, adjusts the book side for income and charges the company hasn’t recorded, and notes that the transactions the bank knows about but the company doesn’t “must be journalized” (OpenStax, 8.6 Define the Purpose of a Bank Reconciliation, read October 8, 2026).

When the difference isn’t zero, its size usually tells you where to look. A difference that divides evenly by 9, read in cents, often means two digits were swapped. A difference equal to one item means that item was missed or entered twice. A difference equal to twice an item means it went in with the wrong sign.

The worksheet above runs the first three tests on the live difference and shows a hint when one fits. They’re hints, not proof: two errors can offset each other, and a difference divisible by 9 can be a coincidence.

The Excel file has six tabs. Reconciliation holds the header, the statement check and the two sides. Outstanding checks, Deposits in transit, Book adjustments and Bank errors are the schedules; each totals into the summary. Example is the worked example above, its schedules beside it, showing Reconciled.

You type into the white cells: the header, the two ending balances, the opening balance check and the schedule rows. The shaded cells are formulas: the totals, the adjusted balances, the difference and the status. They’re not password-protected, so you can change them, but you shouldn’t need to. On Book adjustments and Bank errors, amounts carry their sign: a service fee is -25.00, interest is 4.50. A column beside each book adjustment warns when a fee or a returned check is entered as a positive amount.

Keep one copy per month, or one Reconciliation tab per month in a single workbook. Last month’s outstanding checks that still haven’t cleared carry over to this month’s schedule.

To use it in Google Sheets, upload the .xlsx to Google Drive and open it with Google Sheets (Google, Work with Office files, read October 8, 2026). The formulas work there too.

In the browser, nothing is saved or sent anywhere. Export the CSV or print to PDF before you leave the page.

The template handles the arithmetic. What holds up a bookkeeper’s close is the step before it: getting every client’s statements in. You can’t reconcile a month you don’t have a statement for.

I built DokuTrak for that part. You list the statements once, your client uploads them from a link with no account, the follow-ups go out without you, and an optional AI first pass flags a wrong document or an unreadable scan before you open it. You still accept every file. Checking that a statement covers the right month, with every page, stays with you.

If a client has gone quiet, the follow-up email templates cover the second and third ask. The monthly bookkeeping document checklist lists what else usually comes with the statements. For the wider workflow, see AI tools for bookkeepers and how to stop chasing clients for documents.