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.

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.

Create a Template Layout for Ledger Balance Sheet in Excel

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.

Create a Layout for Trial Balance in Excel

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.

Insert Account Names in Trial Balance

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


Similar Readings


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.

Insert Report Date of Ledger

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

Insert Data Validation option

  • 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.

Editing Data Validation window in Excel

  • Thus, you can select any of the accounts from the list of available accounts by clicking on the dropdown

Insert Drop-down Menu for Account Type Column

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


Similar Readings


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 matched the cell value of B5

Using SUMIF function to calculate total debit

  • 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)

Using SUMIF function to calculate total credit

  • 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.
=SUM(F7:F16)
  • Also, use similar formula for calculating total credit in cell G5.

Using SUM function to calculate total debit

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.

Insert Journal Activities in the Ledger Sheet

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

Trial Balance Data created from ledger balance sheet

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

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo