Nasir Muhammad Munim
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.
- 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.
- 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.
- 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.
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.
- 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.
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.
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.
- 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.
- 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.
