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

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. In this tutorial, we’ll demonstrate how to easily create a Bank Ledger in Excel. We have used Microsoft Excel 365 for this article, but you can use any other version at your convenience.

⭐ Step 1 – Create an Outline

  • Create a space for Opening Balance in cell C4.
  • Make column headings for Date, Transaction Description, Cash Deposit, Cheque Deposit, Online Deposit, Cash Withdrawal, Cheque Return, and Balance like in the below screenshot.

Create an Outline for a Bank Ledger

⭐ Step 02 – Insert Necessary Data

The data we input here are opening balance, date, transaction description, cash deposit, cheque deposit, online deposit, cash withdrawal, and cheque return.

  • Insert the opening balance.
  • Insert the other data according to their date in the respective columns.

Insert Necessary Data for a Bank Ledger

⭐ Step 03 – Using a Formula to Calculate Balance

Let’s use an Excel formula to calculate the balance after all the transactions for each date.

  • Select cell I8 and insert the following formula:

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. Cell C4 indicates the Opening Balance.

Insert Formula

  • Select the second cell of the column Balance (cell I9) and insert the following formula:
  • Drag the Fill Handle down to copy the formula to rest of the cells of the column.

Insert Formula

Your Bank Ledger is complete, and looks like 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. Download the template and use it at your convenience.

Template for a Bank Ledger

Download Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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