How to Make General Ledger in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to make General Ledger in Excel very easily and analyze it. Ledger is used to keeping track of things like business, banking, loan, payments, etc. The process may seem complex but it’s very easy and linear once you understand the method.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Make General Ledger in Excel

To make a general ledger in Excel we will follow these steps serially. There are 4 main parts of the process. We will show you all of these sequentially.

Step-1: Input Fields and Choosing Range

In this step, we need to select what kind of data and how much data we should input into our General Ledger. A typical ledger has 5 basic fields such as – Serial no., Date, Description, Debit, and Credit. So we will put these fields’ names in adjacent columns like in the image below.

We will bold these names and increase the font size a bit because they are going to be heading for our ledger.

Read More: How to Make a Ledger in Excel (with Easy Steps)


Step-2: Creating Pivot Table

This step is the structure of the ledger. First, we need to learn how much data we want to insert. For example, in our case, we want to insert 13 rows of data into our ledger. We will follow these sequence of instructions to complete this phase:

  • First, we will select cells ranging from B4 to F17. Here row no. 4 is for the headers and the rest of the 13 rows are for data.

Procedures to Make General Ledger in Excel

  • Second, we will go to the Home tab in the Ribbon and select Format as Table. Select any kind of table you want from the options.

Procedures to Make General Ledger in Excel

  • Third, we will see a small confirmation box like below. We will select My table has headers and press OK.

  • We will get a table like this.

Procedures to Make General Ledger in Excel

  • Again we will select Borders and go to Line Style. Select thick and highlighting line styles for drawing lines. In our case, we will use a thick line border like the last option.

Procedures to Make General Ledger in Excel

  • Then we will draw lines to all the sides and separators to highlight our table.

  • Now we will select our entire table and go to the Table Design tab in the Ribbon. Here we will select Total Row and we will see a row appear at the end of the table named Total. We will border this row with Line Border as well. We will get a table like this.

Procedures to Make General Ledger in Excel

Read More: How to Maintain Ledger Book in Excel (with Easy Steps)


Step-3: Inserting Calculation Functions in Table

In this step, we will add calculating options in the ledger. For example, we want to see the total Debit, Credit, and days for inserted data. But we can see the total option for Credit is already added. So we will enable it for the other two fields. To do so we will follow these steps:

  • Click on the last cell in the Debit In our case it is E18. A small arrow will appear on the right. Click on that.
  • Then we will select Sum from the options.

Procedures to Make General Ledger in Excel

  • For total days we will select C18 and in the options, we will select Count.

  • Finally, we will get a table like the below image.

Procedures to Make General Ledger in Excel

  • Since there is no data in the table, the total field is showing 0.

Read More: How to Create a Checkbook Ledger in Excel (2 Useful Examples)


Step-4: Analyzing General Ledger

In this phase, we will insert data and check whether our table and calculations are working or not. So we have entered demo data. Here is the table with the data.

Procedures to Make General Ledger in Excel

Here the Debit and Credit data type is selected as Accounting. Part of analyzing the table is to see how much Credited or Debited for each day or similar description. Here we want to see how much Debit or Credit we had for each description. Make a table like this, we will follow these steps:

  • Firstly select the entire table and go to Insert and select From Table/Range in the Pivot Table option.

Procedures to Make General Ledger in Excel

  • Secondly, a dialog box will appear. In the box select New worksheet and press OK. It will open a new sheet containing the Pivot Table field panel on the right.

  • Thirdly, from the panel select the fields according to your need from the panel.

  • Here we will select Description first as we want to categorize based on Description Then we will add Debit, Credit, and other fields. This will create a table like this on the new worksheet.

Procedures to Make General Ledger in Excel

  • Here we can see how much Credit or Debit depends on each description.

Read More: How to Make a Bank Ledger in Excel (with Easy Steps)


Things to Remember

  • Creating a Pivot Table is a part of the analysis. It is not mandatory if you are not interested in analyzing the table.
  • The Total row is not required if you are working with a table containing only text data.

Conclusion

Hope this article helped you to make a general ledger in Excel. If you’re still having trouble with any of these steps, let us know in the comments. Our team is ready to answer all of your questions. For any excel related problems, you can visit our website Exceldemy for all types of Excel-related problem solutions.


Related Articles

Nasir Muhammad Munim

Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo