How to Create a Ledger Balance Sheet in Excel (Easy Steps)

What Is a Ledger Balance Sheet?

A ledger is a crucial document for any organization, displaying debit and credit details along with the current balance after each transaction. There are three main types of ledger books:

  1. Sales Ledger: This records the sale of goods or services to customers. It helps us understand sales profit and income statements.
  2. Purchase Ledger: It documents transactions related to acquiring goods, services, or products from other entities. It provides transparency regarding how much the organization paid to other companies.
  3. General Ledger: The general ledger has two subtypes:
    • Nominal Ledger: Provides information on earnings, expenses, insurance, depreciation, etc.
    • Private Ledger: Contains confidential data like salaries, wages, and capital, typically inaccessible to the general public.

Read More: How to Make a Forecasting Balance Sheet in Excel


What Is a Trial Balance?

trial balance is a financial report that summarizes the closing balances of all accounts at a specific point in time. Typically, each ledger account is organized into debit and credit columns. The total value of debits should match the total value of credits, ensuring that the accounting records are accurate. However, discrepancies can occur due to errors in recording transactions. The trial balance helps identify these errors and ensures that financial records are balanced.


Step 1 – Create a Template Layout for Ledger Sheet

  • Set up a layout for the ledger, allocating cells for essential items such as date, activity description, client, account type, debit, and credit amounts.
  • Create separate columns for each item and designate cells for start and end dates of the period.

Create a Template Layout for Ledger Balance Sheet in Excel

Read More: How to Create Common Size Balance Sheet in Excel


Step 2 – Create a Layout for Trial Balance

  • Create a new worksheet to link trial balance data with the ledger balance sheet.
  • Set up three columns for accounts, debit, and credit.

Create a Layout for Trial Balance in Excel


Step 3 – Insert Account Names in Trial Balance

  • Populate the Accounts column in the trial balance with possible account names that you’ll use in the ledger sheet.
  • These account names will serve as a drop-down menu in the Account Type column.

Insert Account Names in Trial Balance

Read More: How to Make Automatic Balance Sheet in Excel


Step 4 – Insert Report Date of Ledger

  • Enter the end date and start date of the ledger balance sheet in cells C4 and E4, respectively.

Insert Report Date of Ledger


Step 5 – Insert Drop-down Menu for Account Type Column

Create a drop-down menu for the cells of the column Account Type. For this, follow the below procedures:

  • Select all cells in the Account Type column.
  • Go to the Home tab, click on Data Tools, and choose Data Validation.

Insert Data Validation option

  • In the Data Validation window, select List in the Allow criteria option.
  • Specify the range B5:B23 from the Trial Balance worksheet as the source.
  • Click OK.

Editing Data Validation window in Excel

You can select any account from the drop-down menu.

Insert Drop-down Menu for Account Type Column

Read More: Rental Property Balance Sheet in Excel


Step 6 – Insert Formula to Create Trial Balance from Ledger Sheet

  • Debit Amount Calculation:
    • Go to the Trial Balance worksheet.
    • In cell C5, insert the following formula to calculate the total debit amount for each account:
=SUMIF('General Ledger'!$E$7:$E$16,'Trial Balance'!B5,'General Ledger'!$F$7:$F$16)

Here, the SUMIF function adds up the debit column cells in the “General Ledger” worksheet where the account type matches the value in cell B5.

Using SUMIF function to calculate total debit

  • Credit Amount Calculation:
    • In cell D5 of the Trial Balance worksheet, insert this formula to calculate the total credit amount for each account:
=SUMIF('General Ledger'!$E$7:$E$16,'Trial Balance'!B5,'General Ledger'!$G$7:$G$16)

Using SUMIF function to calculate total credit

  • Fill Handle or Copy-Paste:
    • Drag the Fill Handle icon (the small square at the bottom-right corner of the cell) to copy the formulas down to other cells in the column.
    • Alternatively, you can use Excel keyboard shortcuts: Ctrl+C (copy) and Ctrl+V (paste).
  • Calculate Total Debit and Credit:
    • Return to the ledger sheet.
    • In cell F5, calculate the total debit for that period using the SUM function:
=SUM(F7:F16)
    • Similarly, calculate the total credit in cell G5.

Using SUM function to calculate total debit


Step 7 – Insert Journal Activities

  • Now that your ledger balance sheet is ready, start inserting data for your regular activities.

Insert Journal Activities in the Ledger Sheet

The trial balance will automatically update based on the ledger entries.

Trial Balance Data created from ledger balance sheet


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Balance Sheet | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo