Monthly Bank Reconciliation Statement Format in Excel

A bank reconciliation statement summarizes bank and business activity in a single sheet. In this article, we are going to demonstrate the step-by-step procedure to create a monthly bank reconciliation statement format in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


What Is Bank Reconciliation Statement?

A banking reconciliation statement is a sheet that summarizes bank and business activity using financial records and bank accounts.

📖 Purposes:

Typically, the balances on the statement and the business’s records differ. This is because of pending deposits, unpaid checks, bank fees, interest earned or paid, etc. The purposes of making a bank reconciliation statement are:

  • The primary purpose of a bank reconciliation statement is to recognize, resolve, and provide an actual report about the differences between deposits and withdrawals.
  • Due to the high volume of bank transactions, it becomes necessary to reconcile the bank’s records with the organization’s records. The reconciliation statement will help the authorities with the proper documentation of all the bank transactions.
  • The explanation and documentation of any discrepancies between the bank book and our accounting records are contained in an internal financial report of the company.

📖 Advantages:

The major advantages of a bank reconciliation statement are:

  • It is an effective way of spotting fraud activity. The dishonest behavior can be stopped, for instance, if a check is changed and the resulting payment is more than expected.
  • Rectifying discrepancies that affect tax reporting is made easier by reconciling bank statements. Without balancing, businesses risk paying either too high or too low taxes.
  • Statements of bank reconciliation also aid in finding mistakes that might harm financial reporting.

Step-by-Step Procedure to Create Monthly Bank Reconciliation Statement Format in Excel

In this article, we will show you the step-by-step procedure to create a monthly bank reconciliation statement format in Excel. After completing all the steps, the monthly bank reconciliation statement format will be like the image shown below:

Final View of Monthly Bank Reconciliation Statement Format in Excel

📚 Note:

All the operations in this article are accomplished by using the Microsoft Office 365 application.


Step 1: Input Basic Particulars

In the first step, we will input the essential particulars about the bank and balance. The steps are given below:

  • First of all, we will insert a shape to show the title of the sheet. For that, select cell B1.
  • Afterward, in the Insert tab, click on the drop-down arrow of the Illustration > Shapes and choose the Scroll: Horizontal shape.

Choosing a suitable shapes to create monthly bank reconciliation statement format in Excel

  • Now, write down the title of the sheet as Monthly Bank Reconciliation Statement.

  • Then, in the range of cells B5:F9, write down the following entities and allot the corresponding cells to input the values.

Considering cells for fundamental particulars to create a monthly bank reconciliation statement format in Excel

  • Modify the cell format according to your desire.

Modifying cells outlook

  • At last, insert the logo of your financial institution. To demonstrate the process, we insert the logo of our website.
  • In the Insert tab, click on the drop-down arrow of the Illustration > Pictures and choose the This Device option.

inserting logo to create a monthly bank reconciliation statement format in Excel

  • As a result, a small dialog box called Insert Picture will appear.

  • After that, select the picture titles ExcelDemy and click on Insert.
  • You will get the image, and our first task ends here.

Input Basic Particulars for creating a monthly bank reconciliation statement format in Excel

Thus, we can say that we have completed the first step to creating a monthly bank reconciliation statement format in Excel.


Step 2: Create Total Deposits List

In this step, we will create a deposit list. The steps of creating the deposit list are explained below:

  • At first, select the range of cells B11:G11, and in the Home tab, click on the drop-down arrow of the Merge & Center > Merger Cell command from the Alignment group.

Merging cells to write the table title

  • Now, write down the table title as Deposits.

  • Then, write down the following list heading in the range of cells B12:G12.

  • After that, we have to insert a data validation drop-down in the Type and Reconciled columns. The Type column means the types of transactions. Deposit transactions can do in two ways: Transfer from another account and Cash deposit.
  • To add them, select cell C13, and in the Data tab, click on the drop-down arrow of the Data Validation > Data Validation from the Data Tools group.

Adding a data validation option to create a monthly bank reconciliation statement format

  • As a result, the Data Validation will appear.
  • Next, in the validation criteria section located in the Settings tab, change the Allow field option from Any value to List.
  • Afterward, in the Source field, write down the two options, Transfer and Cash.
  • Finally, click OK.

Options of data validation list

  • You will get the data validation drop-down in cell C13.

Inserted data validation drop-down option in a monthly bank reconciliation statement format

  • Now, drag the Fill Handle icon up to cell C17 to copy the data validation.

  • Similarly, add the data validation drop-down in column G for Yes and No options.

Inserting data validation drop-down option for the reconciled field

  • At last, select the range of cell B13:G17 and press ‘Ctrl+T’ to convert the data range into a table.
  • Another small dialog box entitled Create Table will appear.
  • Then, check the option My table has headers and click OK.

Converting the data range into a table

  • In the Table Design tab, rename the table name according to your desire. We set the name of the table as Deposits.
  • Modify the table according to your desire from the Table Style Options.

Modify the data table of a monthly bank reconciliation statement format

  • Finally, we will add conditional formatting to visualize the amount properly.
  • Select the range of cells F13:F17.
  • In the Home tab, click on the drop-down arrow of the Conditional Formatting option and choose the Data Bars > Blue Data Bar from the Gradient Fill section.

Create Total Deposits List to Create Monthly Bank Reconciliation Statement Format

Hence, we can say that we have finished the second step of creating a monthly bank reconciliation statement format in Excel.

Read More: How to Create Account Statement in Excel (with Easy Steps)


Step 3: Design Total Withdrawals List

Now, we are going to design a similar type of table for the withdrawal data. The procedure of the table insertion is the same. Yet, in the Type field, there will be three fields. Because we can withdraw money in that three ways. They are Cash, Transfer, and Card. The complete sheet will be like the image.

Design Total Withdrawals List to Create Monthly Bank Reconciliation Statement Format

Therefore, we can say that we have accomplished the third step of creating a monthly bank reconciliation statement format in Excel.


Step 4: Estimate Final Balance

Here, we are going to estimate the final balance of our account. The steps are given below:

  • To complete this step, we have to input some sample data like the image shown below:

  • Now, to get the Total Deposit value, write down the following formula into cell G8.

=Deposits[[#Totals],[Amount]]

  • Press Enter.

Importing total; deposits value in a monthly bank reconciliation statement format

  • After that, importing the Total Withdrawals value, select cell G9, and write down the following formula.

=Withdrawal[[#Totals],[Amount]]

  • Similarly, press Enter.

Importing total withdrawal value in a monthly bank reconciliation statement format

  • Then, input an opening balance in cell C8. We write $456 as the opening balance,

  • In the end, to calculate the Ending Balance, write down the following formula into cell C9.

=C8+G8-G9

  • Again, press Enter.

Estimating final balance in a monthly bank reconciliation statement format

  • You will get the value.

At last, we can say that all of our formulas work perfectly, and we have finished the final step of creating a monthly bank reconciliation statement format in Excel.

Read More: How to Edit Bank Statement in Excel (with Easy Steps)


Step 5: Verify Reconciliation Statement with Sample Data

In the final step, we are going to add another sample dataset to check the accuracy of the statement.

  • Input the sample like the image given below:

  • You will get the complete bank reconciliation statement format.

Verify the Bank Reconciliation Statement Format with Sample Data

Finally, we can say that we have completed all the steps successfully, and we are able to create a monthly bank reconciliation statement format in Excel.

Read More: How to Organize Bank Statements in Excel (2 Handy Methods)


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to create a monthly bank reconciliation statement format in Excel. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo