Template · Accounting
Bank reconciliation template
Reconcile a bank statement to the general ledger with reconciling items, adjusted balances and journal entries.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Bank reconciliation | ||||
| Example data — replace the blue input cells with your own. | ||||
| ADJUSTED BANK BALANCE | ADJUSTED BOOK BALANCE | DIFFERENCE | STATUS | |
| 51,681.60 | 51,681.60 | 0.00 | Reconciled | |
| Company and statement | ||||
| Company name | Example Manufacturing Co. | |||
| Bank account | Operating checking, ending 4417 | |||
| Statement date | Sep 30, 2026 | |||
| Balance per bank statement | 48,250.00 | Ending balance printed on the statement | ||
| Balance per general ledger | 50,538.48 | Cash account balance in the ledger at the statement date | ||
| Bank side | Amount | Open items | ||
| Balance per bank statement | 48,250.00 | |||
| Add: deposits in transit | 8,405.75 | 3 | Recorded in the books, not yet on the statement |
Showing the first 16 of 41 rows and 5 of 5 columns. Cells with formulas show the formula on hover.
| Bank reconciliation items | ||||||||||
| One row per reconciling item. Cleared? = Yes removes an item from the totals. | ||||||||||
| Date | Type | Reference or check no. | Description | Amount | Cleared? | Row check | Valid item type | Side | Effect on the reconciliation | |
| Sep 28, 2026 | Deposit in transit | DEP-0928 | Sales deposit, September 28 | 4,820.00 | No | OK | Deposit in transit | Bank | Adds to the bank side | |
| Sep 29, 2026 | Deposit in transit | DEP-0929 | Customer check, Northwind Example Ltd. | 1,275.50 | No | OK | Outstanding check | Bank | Subtracts from the bank side | |
| Sep 30, 2026 | Deposit in transit | DEP-0930 | Card settlement, September 30 | 2,310.25 | No | OK | Bank error | Bank | Signed: + raises the bank balance, - lowers it | |
| Sep 14, 2026 | Outstanding check | 1041 | Office supplies vendor | 386.40 | No | OK | Bank credit | Book | Adds to the book side: credited by the bank, not yet in the books | |
| Sep 22, 2026 | Outstanding check | 1047 | Equipment repair, Example Machine Works | 1,942.00 | No | OK | Bank charge | Book | Subtracts from the book side: charged by the bank, not yet in the books | |
| Sep 29, 2026 | Outstanding check | 1052 | Freight carrier | 615.75 | No | OK | NSF check | Book | Subtracts from the book side: a customer check returned unpaid | |
| Sep 30, 2026 | Outstanding check | 1055 | Insurance premium | 2,180.00 | No | OK | Book error | Book | Signed: + raises the book balance, - lowers it | |
| Sep 18, 2026 | Outstanding check | 1038 | Utilities, cleared the bank September 24 | 412.30 | Yes | OK | ||||
| Sep 30, 2026 | Bank credit | INT-0930 | Interest earned, not yet recorded in the books | 38.12 | No | OK | Rows needing attention | 0 | ||
| Sep 30, 2026 | Bank credit | LBX-0930 | Customer payment collected by the bank (lockbox) | 1,500.00 | No | OK | Blank rows are ignored. Amounts are positive except bank and book errors. | |||
| Sep 30, 2026 | Bank charge | SVC-0930 | Monthly account service fee | 25.00 | No | OK | ||||
| Sep 26, 2026 | NSF check | NSF-0926 | Returned customer check, Example Retail Ltd. | 640.00 | No | OK |
Showing the first 16 of 104 rows and 11 of 11 columns. Cells with formulas show the formula on hover.
| Bank reconciliation |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Reconciles a bank statement balance to the general ledger cash balance at a statement date. Reconciling items are listed once on the Items tab, and the Reconciliation tab sorts them into the bank side and the book side. Both sides arrive a… |
| How to use it |
| 1. Enter the company name, bank account, statement date, the balance per the bank statement and the balance per the general ledger on the Reconciliation tab. |
| 2. On the Items tab, list each reconciling item on its own row: date, type (choose from the valid types list), reference, description, amount and Cleared?. |
| 3. Set Cleared? to Yes once a deposit or check has cleared the bank, or once a bank or book item has been recorded in the books. Yes items drop out of the totals. |
| 4. Read the adjusted bank balance, the adjusted book balance and the difference. The status reads Reconciled when the difference is 0.00. |
| 5. Record the journal entries listed in the book-side section, then recheck the difference. |
| 6. Read the Row check column on the Items tab. Fix any row that reads Unknown type, Missing amount, Missing date or Negative amount. |
| Formulas and method |
| Bank side: adjusted bank balance = balance per bank statement + deposits in transit - outstanding checks +/- bank errors. |
Showing the first 16 of 33 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This workbook reconciles the balance on a bank statement with the cash balance in the general ledger at a statement date. Reconciling items are listed once on the Items tab, with a type, a reference, an amount and a Cleared? flag. The Reconciliation tab sorts them into the bank side and the book side, using SUMIFS on the item type, and arrives at an adjusted bank balance and an adjusted book balance.
The bank side adds deposits in transit and subtracts outstanding checks, with bank errors signed. The book side adds bank credits such as interest and lockbox collections, subtracts bank charges and NSF checks, and applies book errors. The difference between the two adjusted balances must come to 0.00, and a status cell reads Reconciled or Difference to find. A journal entry table lists the book-side items to record, with the count and the total for each type. A row check flags unknown types and missing or misplaced amounts.
The example is a fictional manufacturer's September 30 reconciliation with 14 items, one of them already cleared, and a balanced result. The Items tab holds 100 rows.
What’s inside
- Adjusted bank and book balances built with SUMIFS by item type
- Cleared? flag removes resolved items from the totals
- Difference cell that must read 0.00, with a Reconciled or Difference to find status
- Journal entry list for book-side items, with counts and totals
- Row check that flags unknown item types, missing amounts and wrong signs
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Reconciliation | Account inputs, the bank side, the book side, the difference and the journal entries. |
| Items | One row per reconciling item with type, amount and Cleared?, plus the valid types list. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 137 formulas in 346 cells across 3 tabs, so 40% of its cells calculate. They use 9 distinct functions; the longest formula is 233 characters and 15 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 603 | one result when a test is true, another when false |
AND | 100 | true when every test is true |
COUNTA | 100 | counts non-empty cells |
COUNTIF | 100 | counts cells meeting one condition |
COUNTIFS | 7 | counts cells meeting several conditions |
SUMIFS | 7 | adds values meeting several conditions |
SUM | 4 | adds numbers |
SUMPRODUCT | 3 | multiplies matching entries, then adds them |
ABS | 2 | absolute value |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- Enter the statement date, the bank statement balance and the general ledger balance on the Reconciliation tab.
- List each reconciling item on the Items tab, choosing its type from the valid types list.
- Set Cleared? to Yes for items that have cleared the bank or been recorded in the books.
- Confirm the difference is 0.00 and the status reads Reconciled.
- Record the journal entries listed in the book-side section.
What is it good for?
- Month-end cash reconciliation for a small business
- Preparing a reconciliation for a bank or lender review
- Tracking outstanding checks and deposits in transit between statements
- Finding why a ledger cash balance differs from the bank
Questions about this sheet
Why does an outstanding check reduce the bank side?
The check is recorded in the books but has not cleared the bank, so the bank balance still includes the money. Subtracting it gives the cash the company actually has.
How are bank errors entered?
Enter the correction as a signed amount on the Items tab. A positive amount raises the bank balance and a negative amount lowers it.
What does Cleared? = Yes do?
It removes the item from the totals. Use it once a check has cleared the bank or a bank item has been recorded in the books.
Does the workbook post the journal entries?
No. It lists the book-side items and their totals; the entries are posted in the general ledger by the user.