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.

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.

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

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:

📚 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.

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.

Modify the cell format according to your desire.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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

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

Similarly, press Enter.

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.

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.

Step 5: Verify the 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.

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.

Download Practice Workbook

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

Monthly Bank Reconciliation Statement.xlsx

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.

Keep learning new methods and keep growing!

<< Go Back to Excel Reconciliation Templates | Accounting Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!