A step-by-step guide to doing bank reconciliation in Excel

Bank reconciliation is a critical accounting process that ensures your business's financial records match your bank statements. This guide walks you through creating and maintaining an effective bank reconciliation system in Excel.

What is bank reconciliation?

Bank reconciliation is the process of comparing your company's internal financial records with your bank statement to identify and resolve any discrepancies. These differences can occur due to timing differences in recording transactions, bank fees, interest earned, or errors in your or the bank’s records.

The main goals of bank reconciliation are to:

• Detect and correct errors in your books or bank records
• Identify fraudulent activities or unauthorized transactions
• Ensure accurate cash flow reporting
• Maintain compliance with accounting standards
• Provide accurate financial data for decision-making

While bank reconciliation software can automate this process, many small businesses and startups prefer to use Excel for flexibility and lower costs (without accounting for the cost of labor).

How to set up your Excel bank reconciliation template?

Basic template structure

Start by creating a new document with the following sheets:

1. Bank statement data - Import or manually enter bank transactions
2. Book records - Your internal transaction records
3. Reconciliation summary - The main reconciliation worksheet
4. Outstanding items - Track uncleared transactions

Then set up the essential columns for each sheet:

Bank statement sheet:

• Date
• Description
• Reference/Check number
• Deposits/Credits
• Withdrawals/Debits
• Balance
• Cleared (Y/N checkbox)

Book records sheet:

• Date
• Description
• Reference number
• Income/Deposits
• Expenses/Payments
• Running balance
• Bank cleared (Y/N checkbox)

Reconciliation summary sheet:

• Bank statement ending balance
• Add: Outstanding deposits
• Subtract: Outstanding checks/Withdrawals
• Adjusted bank balance
• Book balance per records
• Add: Bank credits not in books
• Subtract: Bank charges not in books
• Adjusted book balance

Step-by-step bank reconciliation process

1. Prepare your data

Start by gathering your bank statement and internal accounting records for the reconciliation period. Input all bank statement transactions in the designated Excel sheet, and make sure the dates are properly formatted and amounts are accurate.

Import your internal transaction records into the book records sheet. Use Excel's data import features if you're pulling from an accounting software, or enter the data manually if you're dealing with smaller volumes.

2. Create matching formulas

Use Excel's VLOOKUP or XLOOKUP functions to automatically identify matching transactions between your bank statement and internal records. Create a formula that compares transaction amounts, dates, and reference numbers.

Example VLOOKUP formula for matching:

=IF(ISERROR(VLOOKUP(C2,BookRecords!A:F,6,FALSE)), "No Match", "Match Found")

3. Identify and mark cleared items

Create checkboxes or use TRUE/FALSE values to mark transactions that appear in both your bank statement and internal records.

For efficiency, create a macro that automatically marks obvious matches based on exact amount and date criteria, and then manually review the remaining items.

Tip: Use conditional formatting to highlight matched items in green and unmatched items in red.

4. Calculate outstanding items

In your reconciliation summary, use SUMIF formulas to calculate totals for outstanding deposits and checks:

Outstanding deposits:

=SUMIF(BookRecords!F:F,FALSE,BookRecords!D:D)

Outstanding checks:

=SUMIF(BookRecords!F:F,FALSE,BookRecords!E:E)

5. Account for bank-only items

Identify transactions that appear on the bank statement but not in your books, such as:

• Bank service charges
• Interest earned
• NSF (not sufficient funds) fees
• Automatic payments or deposits
• Wire transfer fees

Add these items to your book records and include them in your reconciliation calculation.

6. Complete the reconciliation

Your reconciliation is complete when the adjusted bank balance equals the adjusted book balance. The formula in your summary sheet should show:

Adjusted bank balance = Adjusted book balance

If these don't match, double-check for errors, missing transactions, or data entry mistakes.

The most common challenges in bank reconciliation

Timing differences

The are timing differences between when you record transactions and when the bank processes them. Outstanding checks and deposits in transit are normal, but you should track their age to ensure they clear within reasonable timeframes.

Bank errors

It doesn’t happen often, but even banks make errors. When you identify a bank error, document it thoroughly and contact your bank immediately. Keep detailed records of your communication and any corrections you need to make.

Data entry mistakes

Apply a double-entry verification process for critical transactions. Use Excel's data validation features to minimize input errors and create audit trails for all changes made to your reconciliation.

Multiple bank accounts

If you manage multiple accounts, create separate sheets for each account within the same Excel document. Use a master summary sheet to combine all account reconciliations and provide an overall cash position view.

When to consider bank reconciliation software?

You should consider upgrading from manual reconciliation to automated reconciliation software when:

• High transaction volumes make manual reconciliation time-consuming
• You need to do one-to-many or many-to-many matching
• Multiple bank accounts require frequent reconciliation
• Team collaboration requirements go beyond Excel's capabilities
• You need to integrate with accounting systems or financial institutions
• Compliance requirements demand more robust audit trails
• Growth plans are increasing financial complexity

Reconciliation tools like Reiterate cut out 90% of your manual work. They automatically import bank feeds, match transactions using AI, and provide a real-time reconciliation status. These tools often integrate seamlessly with popular accounting platforms and significantly reduce your time spent on monthly reconciliations.

Get in touch with us and see exactly how we can make your bank reconciliation effortless.