ExcelInsider Team
Bank reconciliation is a process that is used to compare the financial records of the company with the bank statement. In accounting, there are possibilities of mistakes happening, which might cause the bank balance to differ from the balance in the cash book. By doing a reconciliation, it is possible to identify the mistakes and fix them.
In this article, we will take the cash book from the company and the bank statement to reconcile. We will use some Excel functions to accomplish the task, and by the end of the article, you will be equipped with the knowledge of how to do bank reconciliation in Excel.
➤ Use the following formula to find the mismatches:
=IF(D3=’Bank Statement’!E7, “Match”, “Mismatch”)
➤ Replace D3 with the cash amount in your cash book, and E7 with the amount in the bank statement. Replace ‘Bank Statement’ with the sheet name of your bank statement.
➤ Select all the cells where you classified the matched and mismatched transactions, and go to Conditional Formatting > Highlight Cell Rules > Equal To from the Styles group of the Home tab to mark the mismatched cells.
➤ Reconcile the bank statement in another sheet by checking the mismatched transactions individually, and sum up the amount using the following formula:
=SUM(B5:B14)
➤ Replace B5:B14 with the range in your sheet.
Bank reconciliations are not always needed, but when it is, it takes time and resources to do so. In this tutorial, we have tried to make the process simpler by providing a step-by-step guide. After reading this, you will be able to make your own reconciliation template and follow the process by yourself. Therefore, grab your favourite beverage and read the full tutorial.
Steps to Do Bank Reconciliation in Excel
For this example, we have the statement of August 2025 from the bank, and the entries of the cash book. Banks don’t usually make mistakes. If there are discrepancies, the possibility is that there were mistakes in our cash book. We have to check the entries from our cash book and match them with the entries provided by the bank to do the reconciliation. Let’s begin:
The Cash Book:
The Bank Statement:
Step 1: Classify the Discrepancies
We are not going to make changes to the bank statement. Therefore, we have to do the check using our cash book. Here is how to do that:
➤ Create two columns in the cash book called Debit Discrepancy and Credit Discrepancy.
➤ In the G3 cell, write the following formula:
=IF(D3='Bank Statement'!E7, "Match", "Mismatch")
➤ Autofill the column till G12.
➤ In the H3 cell, put the following formula:
=IF(E3='Bank Statement'!D7, "Match", "Mismatch")
➤ Autofill the rows till H12.
Step 2: Identify the Mismatches
Our table is small, and it is relatively easy to find the mismatches. However, in a company setting, there might be hundreds of transactions. It is required to pinpoint the transactions that have issues. Here is how you can do that:
➤ Select G3 to H12 cells.
➤ From the Home tab, go to the Styles section, and find Conditional Formatting.
➤ Select Highlight Cell Rules > Equal To.
➤ In the Equal To window, write Mismatch in the left editbox, and make sure Light Red Fill with Dark Red Text is selected on the right. Press OK afterwards.
➤ Now we can see that the mismatched transactions are marked in red.
Step 3: Reconcile the Statement
Now we need to create another table to commit the adjustments for the discrepancies and complete the reconciliation.
➤ In another sheet, create a table with two columns: Particulars and Amount ($). In this table, we will post the adjustments to reconcile. The first entry should be the Balance as per Cash Book (as on 31-Aug-2025).
➤ The first adjustment is the Office Supplies Expense. We overrecorded this in the cash book, so this amount needs to be added to the cash. Use the following formula in the B8 cell to find the difference amount.
='Cash Book'!E4-'Bank Statement'!D8
➤ After doing this for all the transactions, we calculate the balance in the B15 cell using the following formula:
=SUM(B5:B14)
Frequently Asked Questions
What is the format of a bank reconciliation statement?
There is no proper format for a bank reconciliation statement. Different people and companies do it differently. You need to add and subtract the differences to the balance from your cash book to calculate the balance from the bank statement.
Is bank reconciliation easy?
It depends. If you have the receipts and references in your hand, you can do the reconciliation at ease. Otherwise, it might take some time to actually find out what went wrong with your cash book entries.
How to do the bank reconciliation formula?
There is no Excel formula to do the reconciliation automatically. You will have to check the individual transactions. However, if there were a formula, it would be like the following:
=Cash balance + Adjustment additions – Adjustment subtractions
What are the three types of bank reconciliation?
The three types of bank reconciliation are automatic reconciliation, manual reconciliation, and partial reconciliation. In this article, we focused on manual reconciliation. The automatic reconciliation is done by specialized custom software, and partial reconciliation is when you do reconciliation for a single transaction.
Does a bank reconciliation always balance?
It is supposed to match, but if it does not, there might be some error in the recording or the calculation process. It is important to address the error as soon as possible so that it does not affect other accounts.
Wrapping Up
In this article, we have learned how to do bank reconciliation in Excel. We have gone through a three-step process to find out the discrepancies and fix them. We hope that you will be able to follow the tutorial to reconcile your bank statement as well. Leave your questions in the box below if you have any, and we will get back to you soon.
