[Download the template (.xlsx)] · [Open in Google Sheets]
Works in Excel 2016+, Excel for Mac, Excel online and Google Sheets
What's in the template
| Tab | What it does |
|---|---|
| Instructions | The steps below, in the file |
| Bank_Raw | Paste your bank export here. Never edit it. |
| GL_Raw | Paste your GL cash detail here. Never edit it. |
| Match | Formulas flag lines with the same amount and date on both sides |
| Reconciliation | The bank rec format: adjusted bank vs adjusted book, with the difference |
| Items | Outstanding checks, deposits in transit, bank-only items, errors, each with date and owner |
| Example | A completed October reconciliation you can learn from |
The bank reconciliation format
The template uses the standard two-sided format accountants and auditors expect:
| Bank side | Amount | Book (ledger) side | Amount |
|---|---|---|---|
| Balance per bank statement | $191,035.40 | Balance per general ledger | $180,932.80 |
| Less: outstanding check 4476 | -$3,640.00 | Less: wire fee not recorded | -$25.00 |
| Add: deposits in transit | $0.00 | Less: ACH debit not recorded | -$1,312.40 |
| Add: check 4471 posted twice, reversed | $7,800.00 | ||
| Adjusted bank balance | $187,395.40 | Adjusted book balance | $187,395.40 |
| Difference | $0.00 |
Before adjustments, the ledger is $10,102.60 below the bank. The four items explain all of it. These figures match the Yoraito sample dataset used by the free Bank Rec Tie-Out skill.
How to do a bank reconciliation in Excel, step by step
Step 1: Get both balances for the same date
Download the bank statement for the month and run the GL cash detail for the same account, ending on the same date. Write both ending balances at the top of the Reconciliation tab.
Step 2: Paste the raw data
Paste the bank export into Bank_Raw and the GL detail into GL_Raw. Make sure deposits are positive and withdrawals negative on both sides. Some banks export withdrawals as positive numbers; flip them if so.
Step 3: Match the easy lines
The Match tab uses COUNTIFS to flag lines where the amount and date appear on both sides:
=IF(COUNTIFS(GL_Raw!$D:$D, Bank_Raw!D2, GL_Raw!$A:$A, Bank_Raw!A2)>0, "Matched", "Open")
Then check open lines manually for timing differences of a few days, and for check numbers.
Step 4: Classify what's left
Every open line is one of these:
- Outstanding check (in the ledger, not yet on the bank statement): subtract from the bank balance.
- Deposit in transit (in the ledger, not yet on the bank statement): add to the bank balance.
- Bank-only item (on the statement, not in the ledger: fees, interest, unrecorded debits or deposits): adjust the book balance and record an entry.
- Error (a duplicate, a transposed amount): adjust the book balance and correct it.
List each one on the Items tab with the date it first appeared and an owner.
Step 5: Check the difference is $0.00
The Reconciliation tab subtracts the adjusted book balance from the adjusted bank balance. The cell turns green at $0.00 and red otherwise. Never type a plug to make it zero. A remaining difference means an item is missing.
Step 6: Record the entries
Book the bank-only items and error corrections in your GL. Next month, they shouldn't appear again.
Step 7: Sign and file
Add the preparer's name and date, then have someone else review and sign. Save the file with the bank statement and the GL report.
Mistakes the template catches
- Different dates on the bank and ledger balances: a warning appears if the dates in the header differ.
- Plugs: the template has no "other" line on purpose.
- Old items: anything on the Items tab older than 60 days is highlighted.
- Sign errors: if the bank and GL totals differ by exactly double one line, the template points to it.
When Excel stops being enough
Excel works well for one or two accounts with a few hundred transactions. It gets harder when:
- You reconcile many accounts or many clients every month.
- Reviewers need to know why each line matched, not just that it did.
- Auditors ask you to reperform the reconciliation.
Options at that point:
- Free: the Bank Rec Tie-Out skill for Claude runs a four-pass matching method on your exports and writes the SQL behind every figure → /claude-skills/bank-rec-tie-out
- Software: Yoraito reconciles every account each close, with the SQL behind each match and total saved for review → /solutions/bank-reconciliation-software
- How-to: how to automate bank reconciliation → /blog/how-to-automate-bank-reconciliation