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

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.

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.

Read More: How to Make a Ledger in Excel

Maintain Ledger Book in Excel: Step-by-Step Procedure

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 of maintaining a ledger book in Excel.

Step 2: Generate a Ledger Book for Each Month

In this step, we are going to generate the monthly ledger account dataset to keep 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, and 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.

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 maintaining a ledger book in Excel.

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 the Opening Date, Opening Balance, and Closing Date in all sheets.
Now, to get the total Income for January, write down the following formula in 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 in 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.

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.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Maintain Ledger Book.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!