How to Create a Subsidiary Ledger in Excel (Easy Steps)

Introduction to Ledgers

A ledger serves as a summarized record of financial transactions related to various accounts. It contains information from the journal and plays a crucial role in preparing financial statements like trial balances and balance sheets. Professionals use the ledger to make journal entries, making it essentially the second book of entry.

Dataset Overview

We’ll use the below dataset to create Subsidiary Ledgers. We have a General Journal of the XYZ Company. We will make Subsidiary Ledgers for each account in the journal. 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 – Account Name and Number Placement

We need to associate the account name with its corresponding account number. For instance, let’s consider the Cash Account. Assume that the account number for Cash is 101 in the XYZ Company’s account book.

After placing the account name and account number, your result will be like this:

Step 2 – Date of Transactions

Include the transaction dates for each entry in the ledger. Since all transactions involve the Cash account in this example, ensure that you record the relevant dates.

Step 3 – Explanation Using Other Accounts

To explain the transactions, refer to the accounts associated with each Cash transaction. For instance, when $14,000 USD is invested in the company (increasing owner’s equity), the account against Cash is the Owner’s Equity account.

List similar accounts for all Cash transactions.

Step 4 – Referencing Journal Entries

Mention references for each ledger entry, using the journal as the source. For instance, the first entry corresponds to the 1st transaction (reference: J-1).

Step 5 – Calculating Account Balances

Calculate the balance for the Cash account. Be mindful of debit and credit accounts.

In the first transaction, Cash is debited by $14,000, resulting in the same initial balance.

The subsequent debit of $890 should be added to the previous balance using the following formula in cell G8:

=G7+E8

Press ENTER to calculate the balance.

For the credit transaction of $500, subtract it from the previous balance.
Go to G9 and insert the following formula:

=G8-F9

Press ENTER to calculate the balance.

For the credit transaction of $1,600, subtract it from the previous balance.
Go to G10 and insert the following formula:

=G9-F10

Press ENTER to calculate the balance.

For the debit transaction of $400, add it from the previous balance.
Go to G11 and insert the following formula:

=G10+F11

Press ENTER to calculate the balance.

The final balance for Cash will be $13,190.

Step 6 – Highlighting the Final Balance

Emphasize the final balance for each account. In our case, the Cash account’s final balance is $13,190.

Ledger for Other Accounts

You can create ledgers for other accounts in a similar way. For your convenience, I’ve provided the ledger for Owner’s Equity here.

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

Read More: How to Make a Ledger in Excel

Things to Remember

Debit and Credit Accounts:
- Exercise caution when dealing with debit and credit accounts. Understand the impact of each transaction on these accounts.
GAAP Compliance:
- The ledgers and journals adhere to the Generally Accepted Accounting Principles (GAAP). These principles guide financial reporting and ensure consistency and accuracy in accounting practices.