BRS Format in Excel: Free Template, Example, and Step-by-Step Guide

A BRS Format in Excel is used to compare the balance in a company's cash book with its bank statement. It helps identify deposits in transit, outstanding cheques, bank charges, interest credits, and transaction errors. By using Excel formulas, businesses can calculate adjusted balances, track unmatched transactions, and check whether both balances agree.

What Is BRS Format in Excel?

A bank reconciliation statement (BRS) is a financial statement that explains the differences between a business's cash book balance and its bank statement balance on a particular date.

For example, a business may record a customer payment in its cash book today, while the bank reflects the deposit on the next working day. Similarly, a bank may deduct service charges before the accountant records them in the books.

A simple BRS format in Excel organizes these transactions into a structured worksheet. It allows accountants, business owners, and finance teams to compare records, calculate adjustments, and identify differences without repeatedly performing calculations manually.

Read More: Bank Reconciliation Statement (BRS)

What Is the Standard BRS Format in Excel?

A standard BRS format in Excel with formulas should include the business name, bank account, reconciliation date, opening or closing balances, reconciling items, adjustments, and the final difference.

You can create the following template in Microsoft Excel or Google Sheets.

Sample Bank Reconciliation Statement

Business Name: ABC Traders
Bank Account: Current Account
Reconciliation Date: 30 September 2026

In this example, the bank statement shows ₹47,500. A deposit of ₹5,000 has been recorded in the cash book but has not yet appeared in the bank statement. An outstanding cheque of ₹3,000 has also been recorded in the books but has not cleared the bank.

The adjusted bank balance is ₹49,500. The cash book balance of ₹50,000 is reduced by ₹500 in bank charges, giving an adjusted cash book balance of ₹49,500.

Result: Both adjusted balances agree, and the reconciliation difference is zero.

This example assumes that the deposit and cheque are valid timing differences and that the bank charges have not already been recorded in the cash book.

How Do You Prepare a BRS in Excel Step by Step?

Prepare a BRS in Excel by entering bank and cash book transactions, identifying mismatches, recording adjustments, and using formulas to ensure both adjusted balances match.

Step 1: Create the Excel Worksheet

Open Microsoft Excel and create a workbook with three worksheets:

• Bank Transactions
• Cash Book Transactions
• BRS Summary

This structure keeps the original records separate from the reconciliation calculations.

Step 2: Enter Bank Statement Transactions

Create the following columns in the Bank Transactions worksheet:

These are illustrative transactions, not actual bank records. Enter the transactions and balances from your own bank statement. If you use accounting software with automated bank feeds and reconciliation,.

Step 3: Enter Cash Book Transactions

Create a similar table in the Cash Book Transactions worksheet.

Use the same reference numbers in both records wherever possible. This makes it easier to identify matched and unmatched transactions.

Step 4: Identify Unmatched Transactions

Compare the bank statement with the cash book and mark each transaction as matched, unmatched, or requiring investigation.

For example:

The table illustrates how transactions can be classified. Verify each item's actual status before adjusting the reconciliation.

Step 5: Enter Adjustments

List deposits in transit and outstanding cheques separately. Update the cash book for verified bank charges, interest, direct debits, direct credits, and other transactions missing from the accounting records.

Do not record a timing difference as a new transaction if it is already correctly recorded in the cash book.

Step 6: Calculate the Final Balance

Use formulas to calculate the adjusted bank balance and adjusted cash book balance. Compare the two figures to determine whether any unexplained difference remains.

If the difference is not zero, check the transaction dates, amounts, reference numbers, duplicate entries, and adjustment signs.

Which Excel Formulas Are Used for BRS?

Excel formulas can automate calculations in a BRS format in Excel with formula.

1. Formula for Adjusted Bank Balance

Assume the following cells contain your amounts:

• B2: Bank statement balance
• B3: Deposits in transit
• B4: Outstanding cheques
• Enter this formula in B5:

=B2+B3-B4

Using the example:

₹47,500 + ₹5,000 − ₹3,000 = ₹49,500.

2. Formula for Adjusted Cash Book Balance

Assume:

• B7: Cash book balance
• B8: Bank charges not recorded in the cash book
• B9: Interest credited by the bank but not recorded in the cash book

Enter this formula in B10:

=B7-B8+B9

For example, if the cash book balance is ₹50,000, bank charges are ₹500, and interest credited is ₹200, the adjusted balance is ₹49,700.

Include other adjustments according to their accounting treatment. A bank debit normally reduces the book balance, while an unrecorded bank credit normally increases it.

3. Formula to Calculate the Difference

If B5 contains the adjusted bank balance and B10 contains the adjusted cash book balance, enter:

=B5-B10

A result of zero means the adjusted balances agree.

4. Formula to Display Reconciliation Status

If B11 contains the difference, use the IF function: 

=IF(B11=0,"Reconciled","Check Difference")

Excel will display “Reconciled” when the difference is zero. Otherwise, it will display “Check Difference.”

5. Formula to Match Transactions

If column A contains transaction reference numbers and column D contains deposits in the Bank Transactions worksheet, use the SUMIF function: 

=SUMIF('Bank Transactions'!A:A,A2,'Bank Transactions'!D:D)

This formula totals deposits matching the reference number in A2. For reliable matching, use unique transaction references and check the transaction amount and date as well.

What Are the Common Reasons for BRS Differences?

A bank reconciliation may show a difference for several reasons:

• Outstanding cheques: Payments recorded in the cash book but not yet cleared by the bank.
• Deposits in transit: Deposits recorded in the cash book but not yet reflected in the bank statement.
• Bank charges: Fees deducted by the bank but not entered in the cash book.
• Interest credits: Interest received but not yet recorded in the books.
• Direct debits or credits: Transactions processed by the bank without a corresponding entry in the cash book.
• Duplicate entries: The same transaction recorded more than once.
• Incorrect amounts: Data-entry errors in either record.
• Bank errors: Transactions that require clarification or correction by the bank.

Investigate each difference using the bank statement, payment records, receipts, and accounting entries. Do not change the figures simply to make the balances agree.

Where Can You Download a Free BRS Format in Excel?

You can use these resources to find bank reconciliation worksheets and templates:

Check the current download options and terms on each website. Alternatively, copy the tables in this article into Excel and add the formulas provided above to build a basic reconciliation worksheet.

What Is the Difference Between Manual BRS and BRS in Excel?

Excel reduces repetitive calculations, but it cannot guarantee that every transaction is correct. The accuracy of the result depends on complete records, appropriate formulas, and proper review.

As transaction volumes grow, many finance teams move from spreadsheets to cloud accounting software that imports bank statements and matches entries automatically, which cuts repetitive reconciliation work. 

Frequently Asked Questions About BRS Format in Excel

What is the full form of BRS?

BRS stands for Bank Reconciliation Statement. It explains the differences between the cash book balance and the bank statement balance.

How do I create a simple BRS format in Excel?

Create columns or sections for the bank statement balance, cash book balance, deposits in transit, outstanding cheques, bank adjustments, and adjusted balances. Use Excel formulas to calculate the final difference.

Which formula is used to calculate BRS in Excel?

A basic formula is =B2+B3-B4, where B2 is the bank statement balance, B3 is deposits in transit, and B4 is outstanding checks. The formula must match your chosen starting balance and adjustment method.

Can I use the same BRS format every month?

Yes. You can reuse the same workbook structure each month, but update the statement period, opening balances, transaction records, and outstanding items. Carry forward only verified outstanding items.

Why is my BRS difference not zero?

A non-zero difference may result from missing transactions, bank charges, duplicate entries, incorrect amounts, or adjustments entered with the wrong sign. Review the unmatched transactions and supporting records.

How often should a business prepare a BRS?

Many businesses prepare a BRS monthly. Businesses with high transaction volumes or stricter cash-control needs may reconcile their accounts weekly or more frequently.

Conclusion

A BRS Format in Excel helps businesses compare cash book records with bank statements, identify timing differences, and investigate accounting errors. A structured template, consistent transaction references, and formulas such as SUMIF and IF can make the process easier to maintain.

Start with the sample format, enter your actual transactions, and verify that the adjusted bank balance agrees with the adjusted cash book balance. Keep supporting records and investigate every unexplained difference before finalizing the reconciliation.

Read More

The Ultimate Guide to Managing Your Business with ZYNO Books

How to Control Document Versions and Revisions Without Excel?

Ultimate Cloud Accounting Software Guide

Top 11 Best Accounting Automation Software (2026)

What Is Petty Cash Management?