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

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Excel makes our professional life much easier. It is as easy as pie to record accounting information with the help of Excel. We can easily prepare journals, ledgers, trial balances, balance sheets etc in Excel. In this article, I will show you how to make subsidiary ledger in Excel.

Introduction to Ledger

First, let’s understand what a ledger is. A ledger is a book that contains the summarized information (that is in the journal) of different accounts. It helps prepare financial statements like trial balances, balance sheets, etc.
A ledger contains all the accounts that are in the journal. Professionals make journal entries from the book of accounts. People often call a ledger the second book of entry.

Make Subsidiary Ledger in Excel: 6 Steps

This is the dataset for today’s article. We have a general journal of the XYZ company. We will make subsidiary ledgers for each account in the journal using Excel. The accounts are:

Cash,
Owner’s Capital,
Service Revenue,
Salaries Expense,
Accounts Payable, and
Accounts Receivable.

The journal is prepared from the transactions that took place in March. T-1, T-2, etc denote the transactions in the Ref column.

Step-1: Put Account Name with Account Number

The first task is to put the account name with the account number. In our case, the first ledger is for a Cash Account. The account number will be available in the book of account of XYZ company. For simplicity, let’s assume that the account number is 101 for the Cash Account.
After placing the account name and account number, your result will be like this.

Step- 2: Write the Date of the Transaction

The next step is to put the date for each transaction. All the transactions involve the Cash account in this journal.

So we must include all the corresponding dates in our ledger for the Cash account.

Step- 3: List Other Accounts That Are Against Cash as Explanation

Now, we have to explain the transactions in our ledger. For explanation, we will take the help of all the accounts against which the Cash transactions took place.
For instance, in the first transaction, cash of 14,000 USD was invested in the company. This increased the owner’s equity. So the account that is against the Cash account is the Owner’s Equity account.
I will list this account now.

In a similar fashion, list all other accounts against the Cash account.

Step-4: Mention References

The next step is to mention the references. The reference for these entries is the journal. So, I will list the journal entries as references.
The 1st entry in this ledger is from the 1st transaction. So the reference will be J-1 in this case. Similarly, the other references will be as follows;

Step- 5: Calculate the Balance for the Account

The next step is to calculate the balance for the specific account. This is simple. But you have to be cautious about debit or credit accounts.

In the first transaction, Cash is debited and the amount is $14,000. Since this is the 1st entry, the balance will be the same.

The next transaction is also a debit of $890. So we have to add it to the previous balance. To do so,

Go to G8 and write down the following formula.

=G7+E8

Now, press ENTER. Excel will calculate the balance.

Now, we will move to the 3rd journal entry. This time Cash is credited and the amount is $500. So we have to subtract this amount from the previous balance to get the new balance. To do so,

Go to G9 and write down the following formula.

=G8-F9

Now, press ENTER. Excel will return the balance.

In the 4th entry, Cash is also credit and the amount is $1,600. So we have to subtract it from the previous balance just like the 3rd entry. The result will be like this,

And finally, the 5th transaction has a Cash of $400 in debit. So we have to add it to the previous balance. The final balance will be $13,190.

Step-6: Highlight the Final Balance of the Account

The last step is to highlight the final balance of each account. In our case, after all the calculations, we have got the final balance for the Cash account to be $13,190. We have to now highlight it.

Ledger for Other Accounts

In the similar manner shown above, you can make a ledger for other accounts too. For your convenience, I have given the ledger for Owner’s Equity here.

You will find the ledger for all other accounts in the worksheet for this article.

Read More: How to Make a Ledger in Excel

Things to Remember

Be careful about debit and credit accounts.
The ledgers and journals are according to the Generally Accepted Accounting Principles (GAAP).

Download Practice Workbook

Download this workbook and practice while going through the article.

Subsidiary Ledger.xlsx

Conclusion

In this article, I have explained how to make subsidiary ledger in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

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

Get FREE Advanced Excel Exercises with Solutions!