Create General Ledger in Excel from General Journal Data

Get FREE Advanced Excel Exercises with Solutions!

For every businessman, it is common to use a journal for their daily transaction. These entities can be entered into Excel or any other preferable platform. By using this general journal data, you can create a specific account form which is known as a general ledger. In this article, we will show how to create a general ledger in Excel from general journal data. I hope you find this article informative and gain lots of knowledge regarding the general journal data and general ledger.


What Is General Journal?

The general journal is an accounting record of any company’s financial transaction. It is the master of all company entities. Each transaction that a company produces throughout the year is recorded in its general journal. A typical general journal consists of 5 different columns such as date, account, reference, debit, and credit columns. One of the main purposes of a general journal is to track assets and liabilities. This report also allocates costs and expenses.


What Is General Ledger?

A general ledger can be used to track specific kinds of income and expenses. This report contains the same type of information as general journal data. Because in most cases, the general ledger is created through the general journal data. In accounting, the general ledger is used to create some sub-group through which a company can create a trial balance or any type of balance sheet.


Create a General Ledger in Excel from General Journal Data: Step-by-Step Procedure

To create a general ledger in Excel from general journal data, we have found step-by-step procedures through which you can have a complete idea. At first, we create a general journal data table after that we shift it to a pivot table. By using the pivot table, we create the general ledger. All of these steps are really easy to use and it gives a proper solution. By using these steps, we can easily make a general ledger. To understand the process properly, you need to follow the steps.


Step 1: Create General Journal Data

First, we need to create general journal data. A general journal data consists of date, account, reference, debit, and credit. Using these headings, we will create general journal data.

  • First, create those five headings in Excel.

  • Then, select the range of cells B4 to F4.
  • After that, go to the Insert tab on the ribbon.
  • Then, select Table from the Tables group.

  • The Create Table dialog box will appear.
  • Make sure you check on My table has headers.
  • Finally, click on OK.

  • As a result, a table is created using the headings. See the screenshot.

  • Then, add your company’s transaction along with the specific date.
  • Finally, we will get the following general journal data.

Create a General Ledger in Excel from General Journal Data

  • Then, we need to add the Sum of the debit and credit columns.
  • First, select the table, it will open up the Table Design tab on the ribbon.
  • Select the Table Design tab on the ribbon.
  • Then, select Total Row from the Table Style Options group.

  • It will create the total of the last column. See the screenshot.

Create a General Ledger in Excel from General Journal Data

  • Then, in the Debit column, you will get a data validation button.
  • From there, you can select the Sum option.

  • As a result, it will create the total of the debit column. See the screenshot.

Create a General Ledger in Excel from General Journal Data


Step 2: Create Pivot Table

After creating the general journal data, we want to convert the table into the Pivot Table. In the pivot table, we will create the general ledger from the general journal data.

  • First, go to the Insert tab in the ribbon.
  • Then, select PivotTable from the Tables group.

  • Select the table or range.
  • Then, choose New Worksheet where you want to put your PivotTable.
  • Finally, click on OK to apply.

  • It will open the PivotTable Fields dialog box in the new worksheet.
  • Select all the available options.

Create a General Ledger in Excel from General Journal Data

  • It will create a pivot table from the general journal data.


Step 3: Modify Pivot Table

After creating the pivot table, we need to modify it to make it a better one. In this step, we will add some changes through which the pivot table will become more composed.

  • First, we need to change the report layouts.
  • To do this, select the pivot table, it will open up the Design tab on the ribbon.
  • Select the Design tab on the ribbon.
  • Then, select Show in Tabular Form from Report Layout drop-down option from the Layout group.

  • It will set the pivot table in a tabular form. See the screenshot.

  • Then, go to the PivotTable Fields dialog box.
  • Select the Account drop-down option.
  • From there, Select Field Settings.

  • As a result, it will open up the Field Settings dialog box.
  • Then, select the Layout & Print tab.
  • After that, check on Repeats item labels and Insert blank line after each item label.
  • Finally, click on OK.

Create a General Ledger in Excel from General Journal Data

  • It will repeat the item labels and insert a blank line after each item label. See the screenshot.

Create a General Ledger in Excel from General Journal Data

  • Then, we want to change the format of the debit and credit columns.
  • To do this, go to the PivotTable Fields dialog box.
  • From there, select the Sum of Debit drop-down option.
  • Then, select Value Field Settings option.

  • It will open up the Value Field Settings dialog box.
  • Then, select the Number Format.

Create a General Ledger in Excel from General Journal Data

  • As a result, the Format Cells dialog box will appear.
  • Then, select Currency from the Category section.
  • Set the Decimal places as zero.
  • Then, set your preferred negative numbers format.
  • Finally, click on OK.

  • Then, click on OK in the Value Field Settings dialog box to apply the changes.

  • It will change the format of the debit column. See the screenshot.

  • Then, do the same procedures to change the number format of the credit column.
  • Finally, you will get the following result. See the screenshot.


Step 4: Enable PivotTable Analyze Tab on Ribbon

Before doing any further calculations, you need to enable the PivotTable Analyze tab by customizing the ribbon.

  • First, right-click on the ribbon.
  • Then, select Customize the Ribbon.

  • As a result, it will open up the Excel options dialog box.
  • Then, select Customize Ribbon.
  • After that, select All Tabs from the Choose commands from drop-down menu.

  • Then, scroll down and select the PivotTable Analyze tab.
  • After that, click on Add.

  • Then, click on OK to apply the change.


Step 5: Create General Ledger

After completing all the previous steps, we will create the general ledger from the general journal data using the PivotTable Analyze tab.

  • First, select the pivot table.
  • Then, go to the PivotTable Analyze tab on the ribbon.
  • Select the Fields, Items & Sets drop-down option from the Calculations group.

Create a General Ledger in Excel from General Journal Data

  • Then, select Insert Calculated Field after clicking on Calculated Field.

Create a General Ledger in Excel from General Journal Data

  • As a result, it will open the Insert Calculated Field dialog box.
  • Then, set the name and formula.
  • After that, click on Add.

Create a General Ledger in Excel from General Journal Data

  • Finally, click on OK to apply the change.

  • As a result, it will create a new column using your given formula and name. See the screenshot.

Create a General Ledger in Excel from General Journal Data

  • Then, go to the PivotTable Analyze tab on the ribbon again.
  • From the Filter group, select Insert Slicer.

Create a General Ledger in Excel from General Journal Data

  • Then, select Account in the Insert Slicers dialog box.
  • Finally, click on OK.

  • It will create a specific sub-group where you can only see a certain group of transactions when you click on any item.

Create a General Ledger in Excel from General Journal Data

  • Then, if we select cash in the slicer, then it will show the details of cash from the general journal data.

Create a General Ledger in Excel from General Journal Data

  • Now, if you change the transaction from cash to purchase, you will get only the purchase transaction. See the screenshot.

Create a General Ledger in Excel from General Journal Data

  • After that, change the transaction from purchase to sales. See the screenshot.

Create a General Ledger in Excel from General Journal Data

Read More: How to Make Subsidiary Ledger in Excel


Things to Remember

  • While creating a general ledger, you need to use the slicer from the PivotTable Analyze tab.
  • After creating a pivot table, we convert it into a tabular form to make it better to understand.

Download Practice Workbook

Download the practice workbook below.


Conclusion

To create a general ledger in Excel from general journal data, we have shown a step-by-step procedure through which you can do the job. All of these steps are user-friendly. In this article, we utilize the pivot table and find out the required solution. I hope we covered all possible areas. If you have any further questions, feel free to ask in the comment box.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

2 Comments
  1. You have really helped me with your analysis. Thanks for being of help.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo