How to Make a Bank Reconciliation Statement in Excel Format

Step 1: Entering Particulars for Bank Reconciliation Statement in Excel Format

In the sample dataset below, it is evident that the depositor’s bank balance and the bank statement are not the same, so we need to identify the cause of any errors.

Step 2: Preparing the Primary Outline of the Bank Reconciliation Statement

To create a bank reconciliation statement we need to follow some specific rules. At first, we want to make a dataset. To do this we have to follow the following rules.

Enter a header, in this instance, ‘ABC Bank Ltd.’ in merged cells at the top of the spreadsheet using a larger font size.
Enter the date of this statement and input the account number of the person or business as below.

Step 3: Adjusting Bank Balance as per Depositor

Locate the error on the depositor books. In this instance the check to pay the creditor was recorded at $300 but written for only $30. The difference is $270.
Deduct the NSF check debited by the bank which is almost $2300.
Deduct the bank service charge of $75.

To calculate the adjusted balance as per depositor, enter the following formula.

=C9+C11-C13-C14

Press Enter.
The adjusted balance is returned as below.

Step 4: Adjust Bank Balance as per Bank Reconciliation Statement in Excel

Establish the, which are checks or cash deposits not yet credited because they have yet to be processed by the bank. In this instance they amount to $54,700.
Add a deposit understate of $10,000 for checks the bank has yet to receive.
Deduct outstanding checks of $129,478.