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.

Download Practice Workbook

You can download the practice workbook from here:

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.

Read More: How to Create Sales and Purchase Ledger in Excel

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.

Read More: How to Prepare Balance Sheet from Trial Balance in Excel

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.

Read More: Debit Credit Balance Sheet with Excel Formula (3 Suitable Examples)

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.

Read More: How to Make Balance Sheet in Excel (2 Useful Examples)

Similar Readings

• Balance Sheet Format in Excel for Proprietorship Business
• Make Profit and Loss Account and Balance Sheet in Excel
• How to Make Projected Balance Sheet in Excel (with Quick Steps)
• Income and Expenditure Account and Balance Sheet Format in Excel
• Net Worth Formula Balance Sheet in Excel (2 Suitable Examples)

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.

Read More: Balance Sheet Format in Excel with Formulas (Create with Easy Steps)

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

Read More: Balance Sheet Format of a Company in Excel (Download Free Template)

Similar Readings

• How to Create a Balance Sheet for Small Business in Excel
• Revised Schedule 3 Balance Sheet Format in Excel with Formula
• Balance Sheet Format for Construction Company in Excel
• How to Make Balance Sheet Format in Excel for Individual
• Schedule 6 Balance Sheet Format in Excel

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.

• Then, insert the following formula in cell D5 to calculate the total Credit amount for each account.

• Now, drag the Fill Handle icon to paste the used formula respectively 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.

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

Read More: How to Create Vertical Balance Sheet Format in Excel

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.

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

Conclusion

In this article, you have found how to create a ledger balance sheet in Excel and create trial balance from balance sheet. I hope you found this article helpful. You can visit our website, ExcelDemy, to learn more about Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Petty Cash Balance Sheet in Excel – Download Free Template
• Rental Property Balance Sheet in Excel (Free Template)
• Create Consolidated Balance Sheet Format in Excel
• How to Create School Balance Sheet Format in Excel (with Steps)
• Create a Balance Sheet Format for Trading Company in Excel
• How to Create NGO Balance Sheet Format in Excel (4 Easy Steps)
• Make a Forecasting Balance Sheet in Excel (With 3 Steps)