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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

You can download the practice workbook from the link below.


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.


3 Steps to Make a Bank Ledger in Excel

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

Create an Outline for a Bank Ledger

Read More: How to Make General Ledger in Excel (With Easy Steps)


Similar Readings


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

Insert Necessary Data for a Bank Ledger

Read More: Create General Ledger in Excel from General Journal Data


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

Insert Formula

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

Insert Formula

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

How to Make a Bank Ledger in Excel

Read More: How to Make a Ledger in Excel (with Easy Steps)


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.

Template for a Bank Ledger


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 from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Sowmik Chowdhury

Sowmik Chowdhury

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo