Free bank reconciliation template for Excel
Download a free Excel bank reconciliation template that adjusts the statement balance for deposits in transit and outstanding checks, adjusts your book balance for interest, fees, NSF items, automatic payments and errors, and shows whether the difference is 0.00.
Real formulas, a filled fictional example and no macros. Opens in Excel, Google Sheets and LibreOffice Calc.
What is in the workbook
Each kind of reconciling item has its own list, so the statement stays one page and the detail behind every total is one sheet away. Yellow cells are for your entries.
| Sheet | What you do there | What the formulas do |
|---|---|---|
| Instructions | Read once. Covers signs, next month and adding rows. | None |
| Reconciliation | Enter the account details, the statement balance, any bank error and the ledger balance. | Adjusted balances, the difference, a status label and a count of unposted entries |
| Deposits in transit | List deposits in your books that the statement does not show yet. | Total of items not cleared by the statement end date |
| Outstanding checks | List checks and payments in your books that have not cleared. | Total of items not cleared by the statement end date |
| Book adjustments | List statement items missing from your books and pick a Type for each. | Totals by Type feed the book side |
| Example | Read a filled reconciliation for a fictional bakery. | The same formulas, reconciling to 0.00 |
- Carry-forward built in. An item counts while its Cleared on date is blank or later than the statement end date, so next month you fill in dates instead of retyping lists.
- Totals cover every row. Each list has 100 rows and its total spans all of them, so you never extend a formula.
- Ready to file. Prepared by and Reviewed by lines, frozen headers and a fit-to-width print setup.
- Values show on opening. Calculated results are saved with the file, and the only functions used are SUMIF, SUMPRODUCT, ROUND, IF and COUNT.
Preview: the reconciliation statement, filled in
Fictional example · Juniper Street Bakery, operating checking ending 4821, statement ending Sep 30, 2026
| Line | Amount |
|---|---|
| Bank side | |
| Balance per bank statement | 18,426.40 |
| Plus deposits in transit | 2,105.00 |
| Less outstanding checks | (2,148.95) |
| Plus or minus bank errors | 0.00 |
| Adjusted bank balance | 18,382.45 |
| Book side | |
| Balance per books | 19,106.60 |
| Plus interest and collections | 3.85 |
| Less bank fees | (15.00) |
| Less NSF (returned) items | (240.00) |
| Less unrecorded automatic payments | (410.00) |
| Plus or minus book errors | (63.00) |
| Adjusted book balance | 18,382.45 |
| Difference (adjusted bank minus adjusted book) | 0.00 |
Amounts in parentheses are subtracted. The bakery, the people and every amount are made up; the same example is on the Example sheet of the download.
Where the two list totals come from
| Item | Dated | Cleared on | Counts? | Amount |
|---|---|---|---|---|
| Deposit in transit: Mobile deposit, catering invoice 1107 | Sep 29, 2026 | Not yet | Yes | 840.00 |
| Deposit in transit: Night deposit, counter cash and checks | Sep 30, 2026 | Not yet | Yes | 1,265.00 |
| Check 2031: Harbor Packaging | Aug 27, 2026 | Sep 2, 2026 | No, cleared in the period | 228.40 |
| Check 2041: Valley Flour Supply | Sep 22, 2026 | Not yet | Yes | 1,312.75 |
| Check 2044: Brightline Electric | Sep 26, 2026 | Oct 3, 2026 | Yes | 486.20 |
| Check 2045: R. Okafor, contract cleaning | Sep 28, 2026 | Not yet | Yes | 350.00 |
The two deposits add up to 2,105.00. Check 2031 was carried over from August and cleared on Sep 2, inside the period, so it drops out. Check 2044 cleared on Oct 3, after the end date, so it still counts: 1,312.75 + 486.20 + 350.00 = 2,148.95.
How to use the template
- Download and open the workbook. Open the XLSX file in Excel, LibreOffice Calc or Google Sheets. Type only in the yellow cells; every other amount is a formula.
- Enter the account and statement end date. On the Reconciliation sheet, fill in the account name, the last 4 digits and the statement end date. The end date decides which listed items still count as outstanding.
- Enter both ending balances. Enter the ending balance printed on the bank statement, then the cash account balance from your ledger on the same date, before you post this month’s bank adjustments.
- List deposits in transit and outstanding checks. Tick each deposit and payment on the statement against your ledger. Ledger items with no match on the statement go on the Deposits in transit or Outstanding checks sheet.
- List the book adjustments. Statement items with no match in your ledger go on Book adjustments: interest, collections, bank fees, NSF items, automatic payments and corrections of your own errors. Choose a Type for each.
- Read the difference. A difference of 0.00 means the adjusted bank balance equals the adjusted book balance. Any other amount is a difference to investigate before you post anything.
- Post the entries and keep the file. Record a journal entry for every book adjustment, set Entry recorded to Yes, and save the workbook with the statement. Next month, save a copy, change the end date and fill in Cleared on dates.
Bank reconciliation format, line by line
A bank reconciliation format has two halves that must end at the same number. The bank side starts with the statement balance, adds deposits in transit, subtracts outstanding checks and corrects bank errors. The book side starts with your ledger balance, adds interest and collections, subtracts fees, NSF items and unrecorded payments, and corrects book errors.
The split matters because only one side changes your books. Items on the bank side are already in your ledger and are waiting for the bank. Items on the book side are things the bank recorded first, so each one needs a journal entry.
| Line | What goes there | Where you find it | Journal entry? |
|---|---|---|---|
| Balance per bank statement | The ending balance on the statement date. | The statement summary. | No |
| Plus deposits in transit | Deposits you recorded that the bank had not credited by the end date, such as a night deposit on the last day. | Ledger deposits with no matching statement credit. | No. They appear on the next statement. |
| Less outstanding checks | Checks and payments you recorded that the bank has not paid yet. | Ledger payments with no matching statement debit. | No. Follow up on any that stay open for months. |
| Plus or minus bank errors | Amounts the bank posted wrongly, such as another customer’s check charged to your account. | Statement lines that match nothing you did. | No. Ask the bank to correct it. |
| Adjusted bank balance | What the bank balance would be if every timing difference had cleared. | ||
| Balance per books | Your cash account balance on the same date, before this month’s adjustments. | The general ledger or cash book. | Not applicable |
| Plus interest and collections | Interest the bank paid and money it collected for you, such as a note receivable. | Statement credits missing from your ledger. | Yes. Debit cash. |
| Less bank fees | Service charges and other fees the bank deducted. | Statement debits missing from your ledger. | Yes. Credit cash. |
| Less NSF items | Customer checks you deposited that the bank returned unpaid for nonsufficient funds. | Returned item notices and statement debits. | Yes. Debit the customer’s receivable, credit cash. |
| Less unrecorded automatic payments | Autopays and transfers out that never reached your ledger. | Statement debits missing from your ledger. | Yes. Debit the expense, credit cash. |
| Plus or minus book errors | Your own recording mistakes, such as a check entered for the wrong amount. | Matched items whose amounts differ. | Yes. Correct the original account. |
| Adjusted book balance | Your corrected cash balance. It should equal the adjusted bank balance. | ||
| Difference | Adjusted bank balance minus adjusted book balance. 0.00 means reconciled. | ||
You will also see the same statement written as one column that starts from the cash book balance and works to the bank balance, or with separate columns for additions and deductions. The arithmetic is the same. The two-sided layout used here keeps the book side items together, which makes it a ready list of entries to post.
The journal entries from the example
| Item | Debit | Credit | Amount |
|---|---|---|---|
| Interest credited by the bank | Cash | Interest income | 3.85 |
| Monthly service fee | Bank service charges | Cash | 15.00 |
| Returned check, Lakeside Cafe | Accounts receivable, Lakeside Cafe | Cash | 240.00 |
| Equipment insurance autopay | Insurance expense | Cash | 410.00 |
| Check 2039 recorded 63.00 too low | Repairs and maintenance (the account check 2039 was coded to) | Cash | 63.00 |
Together the entries lower the ledger’s cash balance by 724.15: 3.85 in, then 15.00, 240.00, 410.00 and 63.00 out. That takes the books from 19,106.60 to 18,382.45, the adjusted balance on the statement.
When the difference is not 0.00
The size of the difference usually points to the cause. Check these before you change any balance.
| The difference | Likely cause | What to check |
|---|---|---|
| Equals one item’s amount | The item is missing from a list or listed twice. | Search the lists and the statement for that amount. |
| Equals twice one item’s amount | The item is on the wrong side or has the wrong sign. | A fee entered as interest, or a book error with the opposite sign. |
| Divides evenly by 9 | Two digits were swapped when an amount was typed. | In the example, 481.50 recorded as 418.50 leaves 63.00, which is 9 × 7. |
| Changes when you change the end date | A Cleared on date is wrong or missing. | Compare Cleared on dates with the statement that paid each item. |
| Same amount as last month | Last month did not fully reconcile, or its entries were never posted. | Reopen last month’s file and its Entry recorded column. |
A matching total is not proof that every row is right: two errors can cancel each other out. Before you sign off, check that the statement’s own totals add up with the bank statement balance checker.
Using the template outside Excel
- Google Sheets. Upload the file to Drive, open it and choose Open with Google Sheets. Google says changes made that way are saved in the original Excel file; use File, Save as Google Sheets if you want a Sheets copy instead.
- LibreOffice Calc. Choose File, Open and select the XLSX file. LibreOffice opens Excel workbooks in Calc and can save them back as XLSX.
- Any of the three. The template has no macros, and it uses only SUMIF, SUMPRODUCT, ROUND, IF and COUNT, which appear in the function lists for Google Sheets and LibreOffice Calc.
Bank reconciliation template FAQ
Is this bank reconciliation template free?
Yes. The XLSX download is free, needs no account and contains no macros. You can change the labels, add rows or copy the sheets into your own workbook.
Does the template work in Google Sheets and LibreOffice?
Yes. In Google Drive, open the file and choose Open with Google Sheets, or use File, Import inside Sheets. LibreOffice Calc opens XLSX files with File, Open. The formulas use only SUMIF, SUMPRODUCT, ROUND, IF and COUNT, which all three apps provide.
Which balance per books should I enter?
Enter the cash account balance in your ledger on the statement end date, before you post the interest, fees and other adjustments you find during the reconciliation. After you post them, your ledger should show the adjusted book balance.
Why does a check that has cleared still count as outstanding?
Because it cleared after the statement end date. The template counts an item when Cleared on is blank or later than the end date. In the example, check 2044 cleared on Oct 3, so it was still outstanding on Sep 30.
Do deposits in transit and outstanding checks need journal entries?
No. They are already in your books and only waiting for the bank. Bank errors are fixed by the bank. Only the book side items need journal entries, because your ledger does not have them yet.
How is this different from the bank statement balance checker?
The balance checker tests one statement’s own arithmetic: opening balance plus money in minus money out should equal the closing balance. A bank reconciliation compares that statement with your books and explains every difference between them.
What if I have more than 100 outstanding items?
Insert rows between the first and last rows of the list, not below it, then check that the total’s range includes the new rows. A list that long usually means old items need follow-up rather than more rows.