Bank Reconciliation Template Excel & Google Sheets

What is The Bank Reconciliation Template?

Your bank statement and your books almost never agree the moment you check them, and that's normal, not a red flag.

A bank reconciliation template is the worksheet that explains why. It lines up two numbers that should theoretically match, the balance your bank reports and the cash balance your own ledger shows, and walks through every difference between them until both sides land on the same adjusted figure. Most of the gap comes down to timing. A deposit you record the day you receive it doesn't show up on the bank's side until the bank actually processes it. A check you write and record today might sit uncashed for a week.

Here is what a bank reconciliation is not. It's not a balance sheet reconciliation, and it's not an account reconciliation report. Those are broader documents that reconcile every general ledger account or roll up reconciliation status across many accounts at once. A bank reconciliation does one specific job: it proves your cash number is right. Get that wrong and every other number built on top of it, from your balance sheet to your cash flow statement, inherits the error.

What to Include in a Bank Reconciliation Template

Most free templates online give you two blank columns and a "does it match?" question. That's not enough once a business has more than one account, runs any real transaction volume, or needs a document that survives an audit.

Here is every field a complete bank reconciliation template needs:

The pair that matters most is deposits in transit and outstanding checks. Most free templates collapse everything into one generic "adjustments" line. Businesses reconciling more than a handful of transactions a month need both tracked separately, or the variance line becomes a guessing game instead of a checklist.

What the Enerpize Bank Reconciliation Template Includes

The Enerpize bank reconciliation template was built after reviewing publicly available bank reconciliation templates and identifying the gaps that cause real problems once a business is reconciling more than one account a month.

The formula is built in, not buried.
In the Excel version, the Adjusted Bank Balance and Adjusted Book Balance cells calculate automatically the moment you enter your deposits in transit, outstanding checks, fees, and interest. You don't rebuild the math every month.

Deposits in transit and outstanding checks stay separate.
Netting them into one number hides exactly the information you need when a reconciliation doesn't balance. Keeping them apart means you can see at a glance which side the variance is coming from.

A worked example is included, not just a blank grid.
Most free templates hand you an empty worksheet and assume you already know what a completed one looks like. This one shows a real reconciliation, start to finish, before you touch your own numbers.

Sign-off fields for preparer and reviewer.
The person who reconciles an account shouldn't be the only person who confirms it's correct. That separation is a real internal control, and most free templates skip it entirely.

Available in Excel with live formulas and Google Sheets.

Bank Reconciliation vs Balance Sheet Reconciliation vs Account Reconciliation Report

People use these terms interchangeably, and they shouldn't. Here is the difference in one table.

The rule is simple. A bank reconciliation proves one number is right. A balance sheet reconciliation proves every number on the balance sheet is right. An account reconciliation report tells you which of those reconciliations are actually done.

How to Use the Bank Reconciliation Template

Step 1: Gather your records

Pull the bank statement for the period, and your internal cash ledger for the same window. You need both in front of you before anything else makes sense.

Step 2: Enter the bank statement balance

This is the unadjusted ending balance straight off the statement, no corrections yet.

Step 3: Add deposits in transit

List anything your books show as received that the bank hasn't processed yet. Add the total to the bank-side balance.

Step 4: Subtract outstanding checks

List every check you've issued and recorded that hasn't cleared the bank. Subtract the total from the bank-side balance. What's left is your adjusted bank balance.

Step 5: Record bank fees, interest, and errors on the book side

Anything the bank recorded that you haven't yet, a service fee, interest earned, an NSF item, gets added or subtracted from your book balance, not the bank side.

Step 6: Calculate both adjusted balances

Adjusted Bank Balance equals Balance per Bank plus Deposits in Transit minus Outstanding Checks. Adjusted Book Balance equals Balance per Books plus or minus every book-side item you just recorded.

Step 7: Compare and investigate any variance

If both adjusted balances match, you're done. If they don't, the leftover difference tells you exactly how much is still unexplained, narrowing the search instead of starting from zero.

Step 8: Record adjusting journal entries and file the reconciliation

Every book-side adjustment needs a journal entry before the period closes. Save the completed reconciliation as your audit trail.

Bank Reconciliation Example

Here's what a clean reconciliation actually looks like, worked through with real numbers.

A small retail business is reconciling its checking account for the month. The bank statement shows an ending balance of $20,000. The company's own ledger shows $19,350.

Adjusted Bank Balance

Adjusted Book Balance

Both sides land on $19,400. That match is the whole point. The bank statement and the ledger started $650 apart, and every dollar of that gap has a specific, documented reason, not a shrug.

Common Bank Reconciliation Discrepancies (and What Causes Them)

Most differences aren't mistakes. They're timing.

Timing differences. Deposits in transit and outstanding checks account for the majority of what shows up as a "discrepancy" and isn't one. They resolve on their own once the transaction clears.

Unrecorded bank fees and interest. Banks apply these directly to the account. Unless someone checks the statement line by line, they sit unrecorded in the books for weeks.

NSF and returned items. A deposited check that bounces needs to be reversed on the books, or the cash balance overstates what's actually available.

Duplicate entries. Common when a transaction gets imported through a bank feed and also entered manually. A side-by-side comparison catches these fast.

Transposition errors. A number like $1,289 gets typed as $1,298, and the difference is always evenly divisible by nine, a useful check when a reconciliation won't balance and you're not sure why.

Bank errors. Rare, but they happen. If the bank made the mistake, the fix goes through the bank, not a journal entry on your side.

How Often Should You Reconcile Your Bank Account

Monthly is the baseline, timed to when the bank statement closes. That's frequent enough for most small businesses to catch errors before they compound, and it lines up naturally with a standard month-end close.

Businesses with higher transaction volume, retail, ecommerce, anything cash-sensitive, benefit from reconciling weekly. The math is simple: the longer the gap between a transaction and the reconciliation that catches it, the harder that transaction is to trace back to its source. Waiting until quarter-end to reconcile is the version of this mistake that costs the most time to unwind.

How Enerpize Connects Bank Reconciliation to Your Entire Operation

A spreadsheet answers one question: does the bank balance match the books this month? Enerpize answers the questions that come after.

Which transactions are still unmatched? Does a recorded fee need its own expense entry? Is this month's variance actually new, or is it the same uncleared check rolling forward again? A static worksheet can't answer any of that on its own, it just holds the numbers you already worked out by hand.

Enerpize Accounting Software

Enerpize accounting software posts every transaction to the general ledger the moment it happens, so the book-side balance you're reconciling against is never stale by the time you sit down to check it. Cheque cycle tracking lives inside the same module, so an outstanding check isn't a line on a separate spreadsheet, it's a status the system already knows.

Enerpize Chart of Accounts

Every adjustment a reconciliation surfaces, a bank fee, an NSF reversal, an error correction, needs to land in the right account. The Enerpize chart of accounts keeps that mapping consistent, so the journal entry from step 8 posts to the same place it did last month, without someone having to remember which account bank fees go to.

Enerpize Expenses

Bank fees and service charges are expenses the moment the bank applies them, not just a line item on a reconciliation sheet. Enerpize expense tracking picks those up automatically, so the adjustment you make during reconciliation is already reflected in the expense records that feed your profit and loss statement.

Bank Reconciliation Template for Businesses in the United States

Your bank statement and your books rarely land on the same number the moment you check them, and most of that gap is timing, not error.

A deposit you record the day you make it isn't necessarily a deposit your bank has processed yet. Under Regulation CC, the Federal Reserve's funds availability rule, banks must generally make a local check available for withdrawal by the second business day after deposit, and a nonlocal check by the fifth. That gap between when you recorded the deposit and when the bank actually cleared it is exactly why deposits in transit exists as a line item on every reconciliation.

Recordkeeping matters here too. The IRS generally expects businesses to keep records supporting a filed return, reconciliations included, for at least three years from the date you file. If income was underreported by more than 25 percent, that window extends to six years.

What to keep on file for a US bank reconciliation:

• The reconciliation date, bank statement balance, and book balance, saved as dated documentation
• Outstanding checks, tracked by check number until they clear
• Deposits in transit, checked against your bank's stated availability schedule so nothing gets flagged twice
• Bank fees, NSF items, and interest the bank posts directly, since these rarely show up in your books until reconciliation catches them

Practical note for US SMBs. Keep three years of reconciliations on hand as a baseline, and treat any reconciliation tied to a return you're still inside the six-year underreporting window on as a document you don't get to shred early.

Bank Reconciliation Template for Businesses in India

Cheque clearing in India just changed underneath every business that reconciles by hand.

For years, the Cheque Truncation System cleared cheques on a T+1 cycle, meaning a cheque deposited Monday typically cleared Tuesday. The Reserve Bank of India moved the system to continuous, same-day clearing starting October 2025, with Phase 2 from January 2026 requiring banks to confirm or reject a cheque within three hours of deposit. That shrinks the outstanding-cheque window most Indian reconciliations were built around, and any template still assuming a multi-day float is already out of date.

Retention runs longer here than in most markets. Under Section 128(5) of the Companies Act, 2013, companies must preserve their books of account, and the vouchers supporting every entry in them, for a minimum of eight financial years.

What changes on an Indian bank reconciliation:

• Outstanding cheques should clear same-day or within hours, not the multi-day window older templates assume
• Reconciliation records and supporting vouchers need an eight-year retention plan, not a three-year one
• GST-registered businesses should cross-check bank credits against GSTR data during reconciliation, since a mismatch here is a common audit trigger
• Branch-level books, if the company has offices outside its registered address, still roll up to the same eight-year retention requirement

Practical note for Indian SMBs. Build your reconciliation cadence around same-day cheque clearing now, not the T+1 assumption that held until late 2025, and keep every reconciliation and its supporting vouchers filed for the full eight years the Companies Act requires.

Bank Reconciliation Template for Businesses in the United Kingdom

A UK company's bank reconciliation isn't just good practice, it's part of a legal recordkeeping obligation with a fine attached.

Section 388 of the Companies Act 2006 requires accounting records, which includes the documents supporting your bank reconciliation, to be preserved for three years from the date they're made for a private company, and six years for a public one. Section 386 defines what counts as adequate accounting records: documents that show every transaction and disclose the company's financial position with reasonable accuracy at any time. Miss the retention requirement and Section 389 sets a real penalty, a fine of up to £3,000 and up to two years' imprisonment for the officer responsible.

What to include on a UK bank reconciliation:

• Bank statement balance and book balance, both dated to the reconciliation period
• Deposits in transit and outstanding cheques, itemized rather than netted into one adjustment figure
• Bank charges, interest, and any credit or debit memos the bank posted directly
• A clear retention date noted on the file itself, three years out for a private company, six for a public one

Practical note for UK SMBs. Private company or not, treat six years as the safer default if the business could ever go public, get acquired, or face an HMRC enquiry, since VAT records already carry a separate six-year requirement that's easy to align your bank reconciliation retention with.

Bank Reconciliation Template for Businesses in Indonesia

Indonesian bookkeeping rules ask for more than most businesses expect, in language, currency, and retention all at once.

Under the Law on General Provisions and Tax Procedures (UU KUP), as amended by the 2021 Harmonization of Tax Regulations Law, businesses must keep their books, records, and supporting documents, bank reconciliations included, for ten years. That's longer than the US, UK, or most of Southeast Asia. Bookkeeping also has to be maintained in Bahasa Indonesia and Rupiah, with Latin letters and Arabic numerals, unless a business has separately obtained approval to report in English and US dollars.

What an Indonesian bank reconciliation needs:

• A ten-year filing plan for the reconciliation and every supporting document behind it, not the three-to-six-year window common elsewhere
• Figures recorded in Rupiah as the base currency, even if the business also tracks a USD or other foreign-currency view internally
• Entries kept chronological and systematic by transaction date, a specific requirement under the same regulation, not just good practice
• Electronic records, if that's the format used, kept with data integrity and retrieval intact for the full ten-year window

Practical note for Indonesian SMBs. Ten years is a long time to guarantee a spreadsheet survives untouched, formulas breaking or a file going missing is a real risk over that horizon, which is a stronger case for a system of record than most other markets on this page.

Key Takeaways

• A bank reconciliation matches your books against your bank statement using one formula: Adjusted Bank Balance equals Balance per Bank plus Deposits in Transit minus Outstanding Checks.
• Most differences are timing, not errors. Deposits in transit and outstanding checks explain the majority of what looks like a discrepancy.
• The pair of fields most free templates collapse into one line, deposits in transit and outstanding checks, are the two you actually need separated to investigate a variance.
• Reconcile monthly at minimum. High-volume or cash-sensitive businesses should move to weekly.
• In India, cheque clearing shifted from a T+1 cycle to same-day clearing starting October 2025, changing how fast outstanding cheques actually resolve.
• Retention requirements vary sharply by market: three years in the US as a baseline, three to six years in the UK, eight years in India, and ten years in Indonesia.
• A bank reconciliation, a balance sheet reconciliation, and an account reconciliation report are three different documents. Confusing them is a common and avoidable mistake.

Frequently Asked Questions

What is the formula for a bank reconciliation statement?

Adjusted Bank Balance equals Balance per Bank plus Deposits in Transit minus Outstanding Checks. On the book side, Adjusted Book Balance equals Balance per Books plus or minus any interest, fees, or errors the bank has recorded that the company hasn't yet. Both adjusted balances should match once every item is accounted for.

What is a good example of a completed bank reconciliation?

A bank statement showing $20,000, with a $1,200 deposit in transit and $1,800 in outstanding checks, adjusts to $19,400. A book balance of $19,350, with $60 in interest earned and a $10 bank fee, also adjusts to $19,400. The match confirms the account is reconciled.

How often should you reconcile a bank account?

Monthly at minimum, timed to your bank statement cycle. Businesses with high transaction volume or cash-sensitive operations, retail and ecommerce especially, benefit from reconciling weekly instead.

What is the difference between a bank reconciliation and a balance sheet reconciliation?

A bank reconciliation checks one account, cash, against the bank statement. A balance sheet reconciliation checks every account on the balance sheet against its own supporting schedule. A bank reconciliation is one input into the larger balance sheet reconciliation process, not a substitute for it.

What is the difference between a bank reconciliation and an account reconciliation report?

A bank reconciliation is a single document reconciling one account. An account reconciliation report is a rollup showing which reconciliations, across many accounts or entities, are actually complete. One is the work, the other is the status of the work.

How do you calculate outstanding checks in a bank reconciliation?

List every check the company has issued and recorded in its books that hasn't yet cleared the bank, then total the amounts. That total gets subtracted from the bank statement balance to arrive at the adjusted bank balance.

What are the most common causes of a bank reconciliation discrepancy?

Timing differences account for most of them: deposits in transit and outstanding checks that simply haven't cleared yet. Beyond timing, the usual causes are unrecorded bank fees, unrecorded interest, duplicate entries, and transposition errors.

Can I do a bank reconciliation in Google Sheets instead of Excel?

Yes. The core formula and structure are identical in both. The main difference is that Excel supports more advanced formula functions natively, while Google Sheets makes real-time collaboration with a bookkeeper or accountant easier.

About the Author

Omar El Bahr is Senior Digital Growth Specialist at Enerpize, a cloud ERP platform serving 40,000+ SMBs across Australia, Kenya, and global markets. He covers organic growth strategy, construction invoicing systems, and SMB finance workflows. Forbes Communications Council contributor.