Automation of Bank Reconciliation with Excel Macros: 3 Quick Steps

Method  1 – Create Dataset with Proper Parameters

Our dataset contains information about several Transactions, including the Cheque Book Number, Withdrawal Amount, corresponding Date, and so on. We will match the Cheque Book Number from the Bank data with Cheque Book data using VBA Macros, so our dataset becomes.


Method 2 – Open Visual Basic Window

  • Press Alt + F11 from your keyboard.

bank reconciliation in excel with automation macros

  • A window named Microsoft Visual Basic for Applications—Bank Reconciliation will instantly appear in front of you. Insert a module from that window to apply our VBA code.

Insert → Module

  • The Bank Reconciliation module pops up.

bank reconciliation in excel with automation macros

Method 3 – Run Macros to Create Bank Reconciliation

  • Write down the below VBA code in the Bank Reconciliation.
Sub Bank_Reconciliation()
Dim R, M As Integer
For R = 5 To 12
For M = 5 To 12
If Cells(R, 3) = Cells(M, 7) And Cells(R, 4) = Cells(M, 8) Then
Cells(R, 5) = Cells(M, 7)
End If
Next M
Next R
End Sub

bank reconciliation in excel with automation macros

  • Run the VBA.

Run → Run Sub/UserForm

  • Run the VBA Code to match the Cheque Book number from Bank data with Cheque Book Data in the screenshot below.

bank reconciliation in excel with automation macros


Things to Remember

➜ If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.

#DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

<< Go Back to Excel Reconciliation Formula | Excel for Accounting | Learn Excel








Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

  1. Thankyou for this very useful help. However I need to reconcile debit and credits transferred between differing bank accounts, where the date of transfer is not always the same as transfers do not take place in UK during weekends and public holidays.

    Many thanks


    • Hello Philip Earland,

      Thank you for your query. You can find the answer to your question in the Excel file linked to this message. The steps are described below.
      Bank Reconciliation.xlsx

      Consider the transactions from “Metro Bank” to “Lloyds Bank”. We can see the closing balances are not the same and need to be reconciled.

      Select the transaction dates in the B10:B19 cells >> click the Conditional Formatting drop-down >> go to New Rule.
      Select Use a formula to determine which cells to format option >> enter the formula given below >> select fill color, here we’ve chosen the color “Orange, Accent 2, Lighter 80%”.
      Using NETWORKDAYS function to format values
      Here, the highlighted transactions were processed by “Metro Bank” just before the weekends or holidays, so they were not recorded in the “Lloyd Bank” statement.
      Showing the missing transactions
      Next, move to the “Mismatch” worksheet and apply the MATCH function to find the discrepancies between the two statements. Here the #N/A errors are the mismatches.
      Using match function
      Then, use the Filter option in the Data tab to show only the mismatches.
      Applying Filter option to find mismatch
      Enter the mismatched account names and their amounts >> apply the expression below to reconcile the differences.
      Reconciling credit and debit accounts
      Hope this helps. Have a good day.


Leave a reply

Advanced Excel Exercises with Solutions PDF