How to Create a Bank Reconciliation Statement Format in Excel

Bank reconciliation is an essential accounting process that ensures a company’s financial records match its bank statement. This helps in identifying discrepancies, tracking missing transactions, and preventing fraud. One of the easiest ways to create a Bank Reconciliation Statement (BRS) is by using Microsoft Excel.

In this article, we will guide you through the step-by-step process of creating a Bank Reconciliation Statement format in Excel, making it easier for businesses to compare their records with bank transactions.

What is a Bank Reconciliation Statement?

A Bank Reconciliation Statement (BRS) is a report that compares a company’s cash book (internal financial records) with its bank statement to ensure both balances are aligned. Any differences due to uncleared cheques, deposits in transit, bank charges, or errors are identified and adjusted. Nowadays you can use AI based agents to automate bank reconciliation process.

Steps to Create a Bank Reconciliation Statement in Excel

Step 1: Open Excel and Create Column Headers

1. Open Microsoft Excel and create a new worksheet.
2. Enter the following headers in Row 1:

• Date(Column A)
• Particulars (Description)(Column B)
• Cheque No./Reference(Column C)
• Deposits (Credits)(Column D)
• Withdrawals (Debits)(Column E)
• Balance as per Bank(Column F)
• Balance as per Cash Book(Column G)
• Difference(Column H)

Step 2: Enter Bank Statement Data

• In Column A (Date), enter the transaction date.
• In Column B (Particulars), enter the details of each transaction (e.g., cheque issued, deposit made, bank charges, etc.).
• In Column C (Cheque No./Reference), enter cheque numbers or reference IDs (if applicable).
• In Column D (Deposits), record the deposit amount credited by the bank.
• In Column E (Withdrawals), record the withdrawal amount debited by the bank.
• In Column F (Balance as per Bank), calculate the running balance based on deposits and withdrawals.

Step 3: Enter Cash Book Data

• In Column G (Balance as per Cash Book), enter the balance recorded in the company’s cash book.
• Compare the balances in Column F (Bank Statement)and Column G (Cash Book).

Step 4: Identify Discrepancies and Adjust

• If the balances don’t match, list the reasons for the differences in Column H (Difference).
• Common discrepancies include:

• Cheques issued but not cleared by the bank
• Deposits made but not reflected in the bank statement
• Bank charges not recorded in cash book
• Interest earned not recorded in cash book
• Errors in data entry (either in bank statement or cash book)

Step 5: Create a Reconciliation Summary

Below the transaction table, add a summary reconciliation format:

• Use SUM formulasto calculate total reconciled balance.
• Ensure the adjusted Balance as per Cash Bookmatches the Bank Statement balance.

Finalizing the Excel Bank Reconciliation Format

1. Apply bordersand shading to improve readability.
2. Use conditional formattingto highlight discrepancies.
3. Apply Excel formulasfor automatic calculations:

• Use =SUM(D2:D100)for total deposits.
• Use =SUM(E2:E100)for total withdrawals.
• Use =F2+D3-E3to calculate running bank balance.

1. Save the file and update it regularly.

Conclusion

Creating a Bank Reconciliation Statement in Excel is an efficient way to track discrepancies between your company’s financial records and the bank statement. With structured data entry, automatic calculations, and a reconciliation summary, businesses can streamline their accounting process, detect errors, and ensure financial accuracy.

By following the steps outlined above, you can easily prepare a BRS format in Excel and keep your financial records up-to-date. Regular bank reconciliation helps prevent fraud, ensures accurate financial reporting, and keeps cash flow under control.

Would you like a pre-designed Excel template for Bank Reconciliation? Let us know!