Check Register Template: Bank Reconciliation in Excel
Keep a running balance of every transaction with a check register template in Excel. Reconcile against bank statements quickly and accurately.
The Register That Keeps Your Balance Honest
A check register is a running record of every transaction in an account, with a balance that recalculates after each entry. It sounds old-fashioned in an era of real-time banking apps, but it does something the bank app cannot: it includes the transactions you have committed to but the bank has not processed yet. A cheque you wrote yesterday, a scheduled payment for next week, a card charge that has not settled. The bank balance is what the bank knows; the register balance is what you know.
That distinction is the whole point of reconciliation, and a register in Excel makes it mechanical. This article builds one from scratch with formulas that do the arithmetic, then walks through the reconciliation process that keeps the two balances agreeing.
For the broader discipline of matching records to external statements, see our guide to ATO compliance and reconciliation.
The Template Layout
| Column | What it holds |
|---|---|
| Date | Transaction date |
| Reference | Cheque number, receipt number, or reference |
| Description | What the transaction was |
| Category | Income, expense, transfer, fee |
| Payment | Amount out (debit) |
| Deposit | Amount in (credit) |
| Cleared | Y when it appears on the bank statement |
| Balance | Running balance after this transaction |
The Balance column is the only formula you need on entry rows:
=F2 - D2 + E2
Where F is the previous balance, D is the payment amount, and E is the deposit amount. Row 2 starts from the opening balance in its own cell above the table.
Building It Properly
Step 1: opening balance
Put an opening balance cell above the table (say $G$1). The first transaction's balance references it:
=$G$1 - D2 + E2
Every row after that references the row above:
=F2 - D3 + E3
Step 2: totals that check themselves
Add a totals row that cross-checks the running balance arithmetic:
Total payments: =SUM(D2:D1000)
Total deposits: =SUM(E2:E1000)
Expected balance: =G1 + SUM(E2:E1000) - SUM(D2:D1000)
The expected balance must equal the last balance in column F. If they disagree, there is a formula error somewhere, and the totals row finds it immediately.
Step 3: cleared and uncleared views
The Cleared column (Y/N) powers the reconciliation. Two SUMIFS give you the state at any moment:
Cleared payments: =SUMIFS(D2:D1000, G2:G1000, "Y")
Uncleared payments: =SUMIFS(D2:D1000, G2:G1000, "N")
And the balance excluding uncleared items, which is the number you compare to the bank statement:
=G1 + SUMIFS(E2:E1000, G2:G1000, "Y") - SUMIFS(D2:D1000, G2:G1000, "Y")
This is the reconciliation balance. When it equals the statement balance, you are reconciled.
Step 4: data validation on the flags
Restrict the Cleared column to Y or N with a data validation list, and the Category column to your standard set (Income, Expense, Transfer, Fee, Interest). Free-text entries are where registers go wrong.
The Reconciliation Process
- Get the statement. Export the bank statement for the period as a CSV or PDF.
- Mark cleared items. Work through the statement and mark each matching register row as Y. Match on amount and date; when amounts match but dates differ by a day or two, the bank's processing date wins.
- Compare balances. Check the reconciliation balance (step 3 above) against the statement balance.
- Add missing items. Bank fees, interest, and direct debits you had not recorded go into the register now, marked Y.
- Investigate the remainder. If the balances still differ, the difference is usually one of: an uncleared item you missed, a transposed number, or a debit recorded as a credit. Compare the difference amount against your uncleared list first; that catches most cases.
A register that reconciles cleanly every month is also the fastest way to answer "where did the money go" when you need it for a monthly profit and loss review, because every transaction is already categorised.
Common Reconciliation Problems and Fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Balance off by a small amount | Bank fee or interest not recorded | Add the missing entry from the statement |
| Balance off by a round number | A payment entered as a deposit or vice versa | Check the sign on the mismatched row |
| Balance off by a transposed amount (e.g. $54 vs $45) | Digit transposition in an entry | Compare amounts digit by digit against the statement |
| Everything clears but balances still differ | Opening balance wrong | Recheck the starting balance against the statement |
| Difference equals an uncleared cheque | Cheque not yet presented | Confirm it is still outstanding and leave it marked N |
From Reconciliation to Margins
A reconciled register is a complete record of what actually happened to your money, which makes it the foundation for profitability analysis. Once your records agree with the bank, feed the categorised income and expenses into our profitability calculator to see real margins rather than the cash balance alone. Reconciliation is the control; profitability is the payoff.
Frequently asked questions
What is a check register and why do I still need one?
A check register is a running record of every transaction in an account, with a balance that updates after each entry. Even in a digital banking world it is useful because it shows transactions you have committed to but the bank has not yet processed: cheques issued, scheduled payments, and pending card charges. It is also the working document for reconciling your records against the bank statement.
How do I reconcile my register with the bank statement?
Mark every transaction in your register that appears on the bank statement as cleared, then compare the register balance excluding uncleared items with the statement balance. The difference should be exactly the sum of uncleared transactions. Investigate anything else: bank fees, interest, or an entry you missed.
What causes a register balance to disagree with the bank balance?
The usual causes are uncleared cheques or payments (recorded by you but not yet processed by the bank), bank fees and interest not yet recorded in the register, timing differences on deposits, and data entry errors such as transposed numbers or a debit recorded as a credit. Work through those four categories and the discrepancy usually resolves itself.
How often should I reconcile?
Monthly as a minimum, matching your bank statement cycle. Weekly or daily for businesses with high transaction volumes or tight cash flow. Small discrepancies found monthly are easy to trace; the same discrepancies found quarterly take hours to unpick.