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

You have come to the right place if you are looking for the answer or some unique tips to create a ledger balance sheet in Excel There are some quick steps to create a ledger balance sheet in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.

What Is a Ledger Balance Sheet?

Any organization needs a ledger as a necessary document. After each transaction, it displays the debit and credit details as well as the company’s current balance. There are three types of ledger books:

1. Sales Ledger: A Sales ledger is a company’s record of the sale of goods or services to customers. We can derive a sense of sales profit and income statement from this ledger.

2. Purchase Ledger: The Purchase Ledger documents transactions involving the acquisition of goods, services, or products by a business from other entities. It provides us with a transparent accounting of how much the organization paid to other organizations.

3. General Ledger: A general ledger usually has two types:

Nominal Ledger: The nominal ledger provides information on earnings, expenditures, insurance, depreciation, etc.
Private Ledger: The private ledger records confidential data such as salaries, wages, capital, etc. A private ledger is typically inaccessible to the general public.

What Is a Trial Balance?

A trial balance is an alternative type of financial report that displays the closing balances of all accounts at a given time. Typically, the balance of every ledger is organized into debit and credit columns. The total value of these credits and debits should be equal at the end. It is used to ensure the accuracy of a company’s bookkeeping system. A trial balance- as the name suggests- should be considered balanced. However, this is not always the case due to various types of errors in a business. A trial balance, on the other hand, helps to correct those errors and make financial records more balanced.

Steps to Create Ledger Balance Sheet in Excel

In this section, I will show you the quick and easy steps to create a ledger balance sheet in Excel on the Windows operating system. Also, I will show you how you can create a trial balance sheet from the ledger balance in Excel. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

Step 1: Create a Template Layout for Ledger Sheet

First, you have to create a layout for the ledger with the necessary cell allocation for items. In a ledger sheet, we need to insert some important items like date, activity description, client, account type, debit, and credit amount. I have created a separate column for each of them. You also have to assign two cells to insert the start and end dates of the period.

Step 2: Create a Layout for Trial Balance

After that, you will create a new worksheet to create trial balance data linked with the ledger balance sheet. Here, you have to create 3 columns for accounts, debit, and credit.

Step 3: Insert Account Names in Trial Balance

Now, insert the possible account names, which you will use in the ledger sheet, in the Accounts column of Trial Balance. This list will be used in the cells of the accounts type column as a drop-down menu.

Step 4: Insert Report Date of Ledger

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

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

After that, I will create a drop-down menu for the cells of the column Account Type. For this, follow the below procedures:

First, select all the cells of the column Account Type.
Then, go to the Home tab in the top ribbon.
Then, click on the Data Tools menu and select the Data Validation

In the Data Validation window, select List in the Allow criteria option.
Then, select cells of range B5:B23 in the “Trial Balance” worksheet in the Source box.
Finally, press the OK button.

Thus, you can select any of the accounts from the list of available accounts by clicking on the drop–down

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

Now, move to the Trial Balance worksheet and insert the following formula in cell C5 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 calculates the sum of debit column cells in the General Ledger worksheet when the column type column cell matches the cell value of B5

Then, insert the following formula in cell D5 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)

Now, drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
After that, return to the ledger sheet and insert the following formula in cell F5 to calculate the total debit of that period using the SUM Function.

=SUM(F7:F16)

Also, use a similar formula for calculating total credit in cell G5.

Final Step: Insert Journal Activities in the Ledger Sheet

Now, your ledger balance sheet is ready to use. Insert the data of your regular activities and you will observe the final output in the trial balance accordingly.

After inserting data in the ledger balance worksheet, the trial balance becomes ready automatically.

Download Practice Workbook

You can download the practice workbook from here:

Ledger Balance Sheet.xlsx

Conclusion

In this article, you have found how to create a ledger balance sheet in Excel and create a trial balance from a balance sheet. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.

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

Get FREE Advanced Excel Exercises with Solutions!