How to Do Bank Reconciliation in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you might need to do Bank Reconciliation to manage your financial condition. In Microsoft Excel, you can perform Bank Reconciliation in bulk and within seconds. This article demonstrates how to do bank reconciliation in Excel with easy steps.


What Is Bank Reconciliation?

Bank Reconciliation is a process to match your Cash Book closing balance with the Bank Statement closing balance for a definite period of time. In many cases, banks might miss out on data like outstanding cheque, deposits in transit, deposits in understating etc. in the Bank Statement. Also, you may miss data like bounced cheque, missing receipts, bank fees, interest received etc. in your Cash Book. Moreover, there may be some errors on either your part or the bank’s part. So, now we perform Bank Reconciliation to match the closing balances considering all these factors.


How to Do Bank Reconciliation in Excel: 5 Easy Steps

Let’s assume you have a Bank Statement and a Cash Book as shown below. Here, we can see that the closing balances don’t match. So, you want to do Bank Reconciliation. In Microsoft Excel, you can easily do Bank Reconciliation. Now, follow the steps mentioned below to do Bank Reconciliation in Excel.

5 Steps to Do Bank Reconciliation in Excel

5 Steps to Do Bank Reconciliation in Excel

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


⭐ Step 01: Find out Mismatches in Bank Statement and Cash Book

In this step, we will use the MATCH function first to find out which of the Transaction ID matches in the Bank Statement and the Cash Book. Then, we will use the Sort & Filter feature to find out the mismatches in both Bank Statement and Cash Book.

  • First, take the Transaction History from the Bank Statement and copy it to another blank sheet.
  • Then, select cell H5 and insert the following formula.
=MATCH(C5,'Cash Book'!C13:C20,0)

In this case, cells H5 and C5  are the first cell of the column Match and Transaction ID. Also, Cash Book is the worksheet name that contains the Cash Book.

  • Next, drag the Fill Handle for the rest of the cells.

Find Out Mismatches in Bank Statement and Cash Book

Right now, we will use Sort & Filter to find out the mismatches of the Bank Statement with the Cash Book.

  • At this point, go to the Data tab.
  • Then, click on Filter.

Find Out Mismatches in Bank Statement and Cash Book

  • Then, click on the arrow sign in the column heading Match.
  • After that, only select #N/A.
  • Consequently, click OK.

Find Out Mismatches in Bank Statement and Cash Book

  • Eventually, you will get the mismatches in the Bank Statement with the Cash Book.

Find Out Mismatches in Bank Statement and Cash Book

  • Right now, take the Transaction History from the Cash Book and copy it to another blank sheet.
  • Then, select cell H5 and insert the following formula.
=MATCH(C5,'Bank Statement'!C15:C24,0)

In this case, cells H5 and C5 are the first cells in the column Match and Transaction ID respectively. Also, Bank Statement is the worksheet name which contains the Cash Book.

  • Next, drag the Fill Handle for the rest of the cells.

Find Out Mismatches in Bank Statement and Cash Book to do bank reconciliation

  • Subsequently, Sort and filter the data as shown above to find out the mismatches in the Cash Book with the Bank Statement.

Find Out Mismatches in Bank Statement and Cash Book to do bank reconciliation

Read More: Automation of Bank Reconciliation with Excel Macros


⭐ Step 02: Make a Bank Reconciliation Template in Excel

In this step, we will make a Bank Reconciliation Template in Excel. You can make a template as shown in the below screenshot on your own or else you can download the practice workbook and get this template.

Make a Bank Reconciliation Template in Excel

Read More: How to Perform Bank Reconciliation Using VLOOKUP in Excel


⭐ Step 03: Compute Adjusted Bank Statement Balance

Now, we will compute the adjusted Bank Statement balance.

  • First, include the data like Deposit in Transit below Add.
  • Next, insert the data like Outstanding Cheques below Deduct.

Compute Adjusted Bank Statement Balance

  • Then, insert the following formula in cell E12.
=E5+E7-E10

In this case, cells E5, E7, E10, and E12 indicate the Unadjusted Closing Balance, Deposit in Transit, Outstanding Cheque, and Adjusted Closing Balance respectively.

Compute Adjusted Bank Statement Balance

Read More: How to Do Reconciliation in Excel


⭐ Step 04: Calculate Adjusted Cash Book Balance

At this point, we will calculate the adjusted Cash Book balance.

  • First, include the data like Missing Receipts and Interest Received below Add.
  • Then, insert the data like Bank Fees and Bounced Cheques below Deduct.

Calculate Adjusted Cash Book Balance to do bank reconciliation

  • Then, insert the following formula in cell J12.
=J5+J7+J8-J10-J11

In this case, cells J5, J7, J8, J10, J11, and J12 indicate the Unadjusted Closing Balance, Missing Receipts, Interest Received, Bank Fees, Bounced Cheques, and Adjusted Closing Balance respectively.

Calculate Adjusted Cash Book Balance in Excel


⭐ Step 05: Match the Adjusted Balances to Do Bank Reconciliation

Finally, in this step match the Adjusted Closing Balances to finish Bank Reconciliation. In the following screenshot, we can see both the balances for Bank Statement and Cash Book match.

Match the Adjusted Balances to Do Bank Reconciliation


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

In this article, we have seen the 5 steps of how to do bank reconciliation in Excel. Last but not least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Sowmik Chowdhury
Sowmik Chowdhury

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

2 Comments
  1. Would you add up the reasons on why adding or deducting transactions on balancing the unadjusted balance, so that the reader may have further understanding thereon and avoid doing things mecanically? I know that your article is not an introduction on what is a reconciliation as such, but a practical guideline for those who know it, however there might be readers for whom it may be first time to read about reconciliation. Reasons are the heart of the actions, otherwise people will act inadvertently.

    • Hello Tomás Limeme,

      Thank you for your feedback. The goal of a bank reconciliation is to identify and adjust any difference between the closing balances of our cashbook and bank statement over a specific period. To do this, we add or subtract any unrecorded transaction from our unadjusted closing balance.

      In short, adding and subtracting ensures the matching of the bank statement and cashbook balances. This is important for proper financial reporting and to avoid mistakes or fraud.

      Let’s review some of the transactions that are added, followed by the transactions that are subtracted.

      Examples of transactions that are added include:
      1. Deposits in transit: Funds transferred to the bank account but not yet entered into the accounting system.
      2. Bank errors: Mistakes made by the bank, such as incorrectly recorded deposits or credits.
      3. Earned interest: Interest on an account that hasn’t been entered into the accounting system.

      On the other hand, subtracted transactions are:
      1. Outstanding checks: Checks that have been written but have not yet been cashed by the bank.
      2. Bank fees: Charges made by banks that are not accounted for in the accounting system.
      3. Not Sufficient Fund checks: Checks that the bank returned due to insufficient money in the account of the issuer.

      Hope this helps, have a good day.

      Regards,
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo