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.


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.

Create an Outline for a Bank Ledger


⭐ 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


⭐ 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


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


Download Practice Workbook

You can download the practice workbook from the link below.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sowmik Chowdhury
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo