How to Make a Bank Ledger in Excel (with Easy Steps)

A Bank Ledger is a very essential and important document to keep track of your financial situation. In Microsoft Excel, you can easily create a Bank Ledger and use it to keep track of your financial condition. This article demonstrates how to make a bank ledger in Excel with three easy steps.

What Is a Bank Ledger?

A Bank Ledger is the bank account balance computed by a bank for each account every business day. It includes all kinds of deposits and withdrawals. A Bank Ledger is a very important document to keep track of your financial situation. For example, if you want to know what was your balance at the end of a particular date, a Bank Ledger can easily provide you with that information.

Make a Bank Ledger in Excel: 3 Easy Steps

You can easily make a Bank Ledger in Microsoft Excel. Now, to do so, follow the 3 steps mentioned below.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

⭐ Step 01: Create an Outline for a Bank Ledger

In this step, we will create an outline for a Bank Ledger. The outline will consist of all the necessary elements that complete an efficient Bank Ledger. Now, to create an outline for a Bank Ledger in Excel follow the below steps:

First, create a space for Opening Balance in cell C4.
Then, give column headings for Date, Transaction Description, Cash Deposit, Cheque Deposit, Online Deposit, Cash Withdrawal, Cheque Return, and Balance like the below screenshot.

⭐ Step 02: Insert Necessary Data for a Bank Ledger

Now, we will insert the necessary data under their specific column to create a Bank Ledger. The data we input here are opening balance, date, transaction description, cash deposit, cheque deposit, online deposit, cash withdrawal, and cheque return. At this point, to do so follow the below steps.

First, insert the opening balance.
Next, insert the other data according to their date under the respective columns.

⭐ Step 03: Using Formula to Calculate Balance

In this step, we will use an Excel formula to find out the balance after all the transactions for each date. The formula is a very easy and efficient formula. Now, follow the below steps to insert the formula to make a Bank Ledger.

First, select cell I8 and insert the following formula.

=$C$4+D8+E8+F8-G8-H8

Here, cells I8, D8, E8, F8, G8, and H8 are the first cells of the columns Balance, Cash Deposit, Cheque Deposit, Online Deposit, Cash Withdrawal, and Cheque Return respectively. Also, cell C4 indicates the Opening Balance.

Next, select the second cell of the column Balance and insert the following formula. In this case, we select cell I9.

=I8+D9+E9+F9-G9-H9

Finally, drag the Fill Handle for the rest of the cells of the column.

Eventually, you will finish making. your Bank Ledger and get an output as shown in the below screenshot.

Read More: How to Make a Ledger in Excel

Template for a Bank Ledger

We have also provided a template for a Bank Ledger in a separate sheet. You can download the template for Bank Ledger and use it according to your convenience.

Download Practice Workbook

You can download the practice workbook from the link below.

Make a Bank Ledger.xlsx

Conclusion

In this article, we see how to make a bank ledger in Excel with 3 easy steps. Also, you can download a free template for a Bank Ledger. Last but not least, I hope you found what you were looking for in this article. If you have any queries, please drop a comment below.

<< Go Back to Ledger in Excel | Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!