Ledger is an essential document for any organization. It shows us the details of debit and credit and the current balance of that company after every transaction. In this article, we are going to demonstrate the step-by-step procedure to maintain a ledger book in Excel. If you are curious about it, download our practice workbook and follow us.
Download this practice workbook for practice while you are reading this article.
Overview of Ledger Book
An accounting ledger book records all transactions and financial statements of a company. Under the general ledger account, the balance sheet is organized into multiple accounts such as assets, accounts payable, accounts receivable, stockholders, equities, liabilities, taxes, revenues, expenses, funds, loans, profit, loss, and stocks, bonds, wages, etc.
Ledger books are usually three types:
Sales Ledger
A sales ledger is a record of the sale of goods or services to customers that are kept by the company. As a result of this ledger, we are able to get the idea of sales profit and income statement.
Purchase Ledger
The Purchase Ledger records the transactions of that company when purchasing goods, services, or products from other organizations. It provides us with visible information on how much the organization paid to other companies.
General Ledger
General Ledger is usually two types:
- Nominal Ledger: The nominal ledger provides us with information on earnings, expenses, insurance, depreciation, etc.
- Private Ledger: The private ledger keeps track of private information such as salaries, wages, capital, etc. A private ledger is usually not reachable to every person.
Step-by-Step Procedure to Maintain Ledger Book in Excel
To demonstrate the procedure, we will show you the creation procedure of a quarterly ledger book. The procedure is discussed below step-by-step:
Step 1: Create Layout of Ledger Book
In the first step, we will create an area where we can input all the essential information regarding our institution. Here, we are going to create the slot in the individual monthly ledger.
- First of all, in the Insert tab, click on the drop-down arrow of the Illustration > Shapes option.
- After that, select a shape according to your choice. We chose Ribbon: Tilted Down shape.
- You will notice that your mouse cursor icon will change. Then, drag your mouse where you want to place the shape.
- Now, write down the title of this sheet as LEDGER.
- Then, select cell F1, and in the Insert tab, click on the drop-down arrow of the Illustration > Pictures option.
- Choose the This Device option.
- As a result, a small dialog box called Insert Picture will appear.
- Select your organization’s logo and click the Insert button. We chose our website’s logo to demonstrate the process.
- Place the logo at a suitable location on the sheet.
- Now, in the range of cells B4:B5, B7:B8, and E7:E8, write down the following entities and format the corresponding cells as the input cells of these values.
- Our first job is finished.
Thus, we can say that we have completed the first task to maintain a ledger book in Excel.
Read More: Create General Ledger in Excel from General Journal Data
Step 2: Generate Ledger Book for Each Month
In this step, we are going to generate the monthly ledger account dataset to keep the records of our financial activities.
- First, in the range of cells B10:H10, write down the following titles of our table heading.
- We assume that we have 7 financial activities each month. As a result, we select the range of cells B10:H17 as our ledger book dataset and format the cells with the All Border option from the Font group located in the Home tab.
- We need a data validation option in column H. So that, when a person uses this ledger, can only input either Debit or Credit in the range of cells H11:H17.
- Now, select the range of cells H11:H17, and in the Data tab, click the drop-down arrow of the Data Validation > Data Validation option from the group Data Tools.
- As a result, the Data Validation dialog box will appear.
- Then, change the Allow field from Any value to List, and in the Source field, write down Debit,Credit.
- Finally, click OK.
- You will find the data validation drop-down at those cells.
- After that, to estimate the value of the balance after the first transaction, write down the following formula into cell H11.
=C8+F11-G11
- Press Enter.
- Our current value is changed. Thus, to evaluate the value of the balance after the second transaction, write down the following formula cell the H12.
=H11+F12-G12
- Again, press Enter.
- Now, drag on the Fill Handle icon to copy the formula up to cell H17.
- Afterward, to get the total value of the Income, we are going to write down the following formula into cell F18, using the SUM function.
=SUM(F11:F17)
- Press Enter.
- Similarly, write down a similar type of formula for the Expense column.
- Next, to check that our closing balance of cell H17 is correct, write down the following formula in cell H18.
=C8+F18-G18
- Similarly, press Enter.
- At last, to show the closing balance below the Closing Data, input the following formula in the merged cell F8.
=H18
- Press the Enter.
- Our job is completed.
- Let’s check with some sample data to ensure all of the formulas are working accurately in this sheet.
- You will see all the cells where we input formulas responded with our data entry.
- Similarly, create two more sheets for February and March.
So, we can say that we have finished the second step to maintain a ledger book in Excel.
Read More: How to Make a Ledger in Excel (with Easy Steps)
Step 3: Design Summary Report
Now, we will design the preliminary layout of our summary report.
- At first, select row 1:8 in the Jan sheet and press ‘Ctrl+C’ to copy.
- Then, go to the Summary sheet and press ‘Ctrl+V’ to paste.
- Now, change the sheet title from LEDGER to Summary.
- We will show the month’s name, income, expense, and balance in our Summary sheet. So, modify the information in four columns and delete all the unnecessary columns.
- After that, write down the four table title in the range of cells B10:E10.
- Next, write down the month’s name in the range of cells B11:B13 and insert all borders to all the cells.
- At last, denote row 14 as Total to show the total of all columns.
Finally, we can say that we have accomplished the third step, to maintain a ledger book in Excel.
Read More: How to Create a Checkbook Ledger in Excel (2 Useful Examples)
Step 4: Establish Relation Between Summary and Other Sheets
In this following step, we will establish relationships between the Summary sheet and with other three-month sheets.
- Firstly, to get the company name from the Summary sheet to the Jan sheet, write down the following formula into cell C4. For that, we will use the IF function.
=IF(Summary!C4<>0,Summary!C4,"")
- Press Enter.
- Then, drag the Fill Handle icon in cell C5 to import the address from the Summary sheet.
- We have to manually input the value of Opening Date, Opening Balance, and Closing Date in all sheets.
- Now, to get the total Income for January, write down the following formula into cell C11.
=Jan!F18
- Again, press Enter.
- Similarly, to import the total Expense for January, write down the following formula in cell D11.
=Jan!G18
- Press the Enter.
- Finally, to get the final Balance for January, write down the following formula into cell E11.
=Jan!H18
- Press Enter.
- You will get all the values for January. Similarly, write down the formulas to import the total values for February and March.
- At last, to get the quarterly total of Income, write down the following formula using the SUM function.
=SUM(C11:C13)
- Press Enter.
- Similarly, use the SUM function to evaluate the total of the Expense column.
- Now, to get the total closing balance, write down the following formula into cell E14.
=C8+C14-D14
- Again, press Enter.
- As this is a quarterly ledger book, the value of the Closing Balance will be the Closing Balance of March.
- To show the value in cell E8, write down the following formula for that cell.
=E14
- Press Enter for the last time.
- Our ledger book creation task is finished.
In the end, we can say that we have completed the final step, to maintain a ledger book in Excel.
Read More: How to Make Subsidiary Ledger in Excel (With Easy Steps)
Step 5: Verify Ledger Book with Sample Data
In this last step, we will input sample data in our all individual monthly sheets and verify our ledger book.
- First of all, input the name of the company and address in the Summary sheet.
- You will see both data exports in the other three sheets.
- Now, input all the required data in the sheet titled Jan.
- Similarly, input the sample data in the sheet entitled Feb.
- Finally, input the data in the sheet called Mar.
- At last, come back to the Summary sheet, and you will see all the data are imported here.
Hence, we can say that all of our formulas worked perfectly, and we were able to create and maintain the ledger book in Excel.
Read More: How to Export All Ledgers from Tally in Excel
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to maintain the ledger book in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- How to Create a Party Ledger Reconciliation Format in Excel
- Make a Vendor Ledger Reconciliation Format in Excel
- How to Make a Bank Ledger in Excel (with Easy Steps)