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

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

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

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.

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

Next, 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.

Then, 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.

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.

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.

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.

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.

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

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.

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.

Next, 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.

Then, 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.

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

Read More: How to Make a Bank Ledger in Excel

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.

Download Practice Workbook

You can download the practice workbook from here.

General Ledger.xlsx

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.

<< Go Back to Ledger in Excel | Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!