Sowmik Chowdhury
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.
Read More: How to Make General Ledger in Excel (With Easy Steps)
Similar Readings
- How to Make Subsidiary Ledger in Excel (With Easy Steps)
- Create a Checkbook Ledger in Excel (2 Useful Examples)
- How to Export All Ledgers from Tally in Excel
- How to Create Sales and Purchase Ledger in Excel
⭐ 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.
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-H8Here, 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 (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.
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.
