Every business should maintain a ledger to keep their financial records organized. Ledgers help businesses track profits, balance assets and expenses, and keep track of all their transactions.
Traditionally, companies maintained their ledgers in the form of registers. But with the advancement of technology and the availability of spreadsheet software, creating and maintaining ledgers has become a whole lot easier.
Using a pre-built ledger template in software like Google Sheets further speeds up the process, as you spend less time creating tables, performing computations and updating multiple accounts at the same time.
In this tutorial, we will explain ledgers and ledger systems in brief and then help you create your own Google Sheets ledger template with a few basic steps.
We’ve created a free Google Sheets ledger template that you can save and start using right away. Keep reading to learn how to use it.
NOTE: Click on File > Make a Copy to be able to edit your template. DO NOT request edit access!
Table of Contents
What is a Ledger?
A ledger is a financial document that is used to track your assets and expenses. It lists all your financial transactions in separate balance sheet accounts, so that you can easily review your transactions when needed. It also helps you keep track of finances and identify errors.
Every balance sheet account in a ledger contains information related to the business’s assets, liabilities, revenues and expenses.
Different Types of Ledgers
There are basically three main types of ledgers:
Sales Ledger
Also known as a Debtor’s ledger, a Sales ledger consists of all accounts related to customers who purchased goods or services on credit. Some of the accounts that a Sales ledger might have include Accounts Receivable,Trade Debtors or Sundry Debtors.
Purchase Ledger
A Purchase ledger consists of all accounts related to sellers from whom goods or services have been purchased on credit. Some of the accounts that this ledger might have include Accounts Payable, Trade Creditors or Sundry Creditors. One can see the total money owed in a Trial Balance.
General ledger
A general ledger is a compilation of all the ledger accounts owned by the business. This type of ledger consists of accounts such as assets, liabilities, revenues, expenses, etc.
Why Create a Ledger Template?
Creating a ledger from scratch can be helpful if you have very specific requirements for your business. However, most businesses need more or less the same types of records and fields. Using a ledger template can cut the amount of time needed to create a template in half.
Moreover, you get a structured document that you can simply copy and rename for each account. You can customize the template according to your requirements and then directly start filling in your records.
In this way it provides you with a standardized financial record-keeping system, from which you can easily retrieve whatever information you need to and perform subsequent analytics with the retrieved data.
What Does a Simple Ledger Template Consist Of?
A ledgertypically consists of different types of accounts like assets, liabilities, revenue and expenses. Each ledger account can have its own page, and some can even have their own sub-ledgers. All accounts have more or less the same fields, so you can use a simple ledger template that you can duplicate for each type of account.
Note: A ledger template may also be called a balance sheet template.
The main fields that a ledger template typically contains include:
- Date: This column tracks the date of each transaction
- Reference: This contains the page number or record number of a transaction in some other reference ledger. For example the same entry might be entered as a debit entry in one account ledger and a credit entry in another account ledger.
- Account: This is usually a dropdown that lets you select the account corresponding to a given entry.
- Explanation: This lets you write further details about the transaction.
- Credit Amount: This contains an amount transferred from the present account
- Debit Amount: This contains an amount transferred to the present account
- Balance Amount: This contains the amount remaining in the account at each transaction after adding the credited amounts and subtracting the debited amounts.
How to Create and Use a Google Sheets Business Ledger Template
Let us now see how you can create a ledger from a pre-built ledger template. You can use this as a General Ledger template, or can customize it into a Sales Ledger template.
Decide on the Design
The first step in creating a ledger template is the design phase. In this step, you need to make an outline or format for each page or ledger account. This design or structure is usually consistent for all accounts, so that they can easily be referenced and retrieved when needed.
Make sure you include the above elements, along with any other fields that might be relevant to your business.
Create your Ledger Template or Use a Pre-designed One
Once your design is ready, you can set up your ledger. Download a copy of the ledger template provided in the link below:
Spreadsheet Point’s Free Google Sheets Ledger Template
NOTE: Click on File > Make a Copy to be able to edit your template. DO NOT request edit access!
Here’s how the template looks:
After downloading the template, you can customize it according to your requirements. Change the color scheme, fonts, or sizes, and add, remove or rename columns as you require (according to your decided design).
Consider how many accounts you need and if you need multiple pages or subpages for each account. Once you’re done, you can make copies of the template, in the form of separate tabs (or sheets), one for each account.
Now you can start filling in the details. For each account page, make sure you put the appropriate date range in row 2 (shown in the image below).
Next, enter an amount for your account’s opening balance.
Finally, you may start adding details for each transaction as needed.
Getting Familiar with the Google Sheets Ledger Template
Before moving further, it is important first understand the structure of our Ledger template, so that it is easier for you to fill in the transaction details.
The top part of the sheet consists of the ledger title and date range.
After this, there’s a slot for you to enter the opening balance for the date range mentioned.
On the right side of the sheet, there is a small table consisting of the names of different accounts, along with initials stating whether the corresponding account is a debit or credit account. Let’s call this table the ‘Account list table’.
The main part of the template consists of a larger table with 7 columns:
- Date
- Reference
- Account
- Explanation
- Credit
- Debit
- Balance
We already discussed what each of these columns represent in an earlier section of this tutorial. Let’s call this table the ‘Main Table’.
The table has been activated with filters, allowing you to filter your transaction records according to your requirements.
The Balance field of this table has already been populated with the appropriate formulae to calculate the balance from the debit and credit amounts that you enter.
The Date field has been pre-formatted to convert your entered date into a uniform format, while the Account field has data validation activated in the form of a dropdown list to ensure that you only enter the predetermined account names.
You might obviously want to have other account names in your template design. The dropdown list can easily be adjusted as follows:
To change the name of an account, simply make the edit in the Account List table.
- If you need to remove an account name simply delete the entire row corresponding to the account name. Make sure you don’t end up deleting any existing entries in the main table, though.
- If you need to add newer accounts, then add the names of the accounts to the ‘Account list table’. Simply add the new account names at the end of the table, instead of trying to insert new rows (as this would affect the formulae and formats in the Main Table too.
- Once your accounts list contains only the names of your required accounts, the changes should get automatically updated in the dropdown lists of the Account field in your Main Table.
- If you don’t see the changes reflected, select all the cells of the Account field, navigate to Data->Data Validation and adjust the range specified in the input box next to ‘List from a range’ so that it contains the range of cells that make your account list in your Account List table. For example, if your accounts list now has account names from the range I10 to I16, then make sure the input box contains the range “=$I$10:$I$16”.
- Click Save to close the Data Validation dialog box.
The dropdowns of your Main Table’s Account field should now show your new list of accounts.
You can find out more about data validation and dependent dropdown lists here: How to Create a Dependent Drop Down List in Google Sheets
Note: If you prefer not to have a dropdown list in the Account field of your Main Table, then simply select the ‘Remove Validation’ button in your Data Validation dialog box instead of Step 5.
Finally, the headers of your Main table contain totals for the Debit, Credit, and Balance fields. These are also automatically computed as you enter newer transactions into the table.
Note that cell G7 computes the final balance left after removing the total debit and adding the total credit to your opening balance. It does not find the total of the Balance column.
This means this cell helps you identify errors in your entries. If you find the final balance in the Balance field and the total balance in cell G7, it means there must be an error in your transaction entries.
Another important thing to note is that the sheet has been protected to ensure that the user does not accidentally write over any of the cells containing a formula. If a user does attempt to enter something in any of these protected cells, they are shown a warning that asks the user if they are sure they want to edit the cell.
Maintain your Ledger
Once you have your ledger set up, you can start recording your transactions. If you find you’re out of rows to enter new records, simply press the ‘Add’ button at the bottom of the sheet to add 1000 more rows.
Note: You can read more about how to add multiple rows in this article: How to Quickly Insert Multiple Rows in Google Sheets
Every once in a while, make sure that you review your ledger for errors. This can help reduce potential mistakes that can affect your business.
Conclusion
In this tutorial we showed you how to create a Google Sheets Ledger template. Instead of trying to build one from scratch, it’s easier to just download the template that we provided and then customize it to your requirements.
Click here to download Spreadsheet Point’s free Google Sheets Ledger template.
Note: Click on File > Make a Copy to be able to edit your template. DO NOT request edit access!
We hope this tutorial was helpful.
Related Reading:
