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:

Sales Ledger: This records the sale of goods or services to customers. It helps us understand sales profit and income statements.
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.
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.

What Is a Trial Balance?

A 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

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

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

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)