Monthly Bank Reconciliation Statement Format in Excel

What Is a Bank Reconciliation Statement?

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

Purposes:

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:

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 a Monthly Bank Reconciliation Statement Format in Excel: Step-by-Step Procedure

Our monthly bank reconciliation statement format will look like the image shown below:

Step 1 – Input the Basic Particulars

Select cell B1.
In the Insert tab, then select Illustration and choose Shapes, then choose the Scroll: Horizontal shape.

Write down the title of the sheet as Monthly Bank Reconciliation Statement.

In the range of cells B5:F9, insert the following entities and allot the corresponding cells to input the values.

Modify the cell format as needed.

Insert the logo of your financial institution. We’ll insert the logo of our website.
In the Insert tab, click on the drop-down arrow of Illustration > Pictures and choose the This Device option.

A small dialog box called Insert Picture will appear.

Select the picture and click on Insert.

Step 2 – Create the Total Deposits List

Select the range of cells B11:G11, and in the Home tab, click on Merge & Center and choose the Merge Cells command from the Alignment group.

Enter the table title as Deposits.

Insert the following list heading in the range of cells B12:G12.

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.
Select cell C13, and in the Data tab, click on Data Validation and select Data Validation from the Data Tools group.

The Data Validation will appear.
In the validation criteria section located in the Settings tab, change the Allow field option from Any value to List.
In the Source field, write down the two options, Transfer and Cash.
Click OK.

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

Drag the Fill Handle icon to cell C17 to copy the data validation.

Add the data validation drop-down in column G for Yes and No options.

Select the range of cell B13:G17 and press Ctrl + T to convert the data range into a table.
Another small dialog box named Create Table will appear.
Check the option My table has headers and click OK.

In the Table Design tab, rename the table name. We set the name of the table as Deposits.
Modify the table in Table Style Options.

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 Data Bars and select Blue Data Bar from the Gradient Fill section.

Step 3 – Design the Total Withdrawals List

The procedure of the table insertion is the same as before. We can withdraw money in three ways: Cash, Transfer, and Card. This affects the first data validation option. The complete sheet will be like the image.