Top 5 FREE Excel Ledger Template of 2024 (+How to create one)

Excel is the top spreadsheet application for accounting people. Easy-to-use interface and hundreds of functions make Excel an excellent option for bookkeeping. It can be used to record and report transactions for both personal and business purposes.

We understand that keeping track of transactions is frustrating without proper tools at your disposal. If you are looking for such a tool, then this is the right place.

Anyone can easily design a comprehensive accounting ledger for small businesses. It’s really easy to track expenses and profits with Excel.

In this article, we will discuss the top 5 FREE Excel Ledger Templates of 2024. We will also learn the step-by-step process of creating a ledger spreadsheet from scratch, so stay tuned till the end.

Top FREE Excel Ledger Spreadsheets

There are numerous ledger templates available online. Few of them are helpful, and the rest require expert knowledge to keep track of expenses.

We understand you can’t test every template to find the perfect fit, so we have done the heavy lifting for you. After careful testing, we have come up with a list of 5 FREE Excel Ledger templates on the internet.

1. General Ledger Template by SoftwareAccountant

Click Here To Use This Template!

This template by SoftwareAccountant is perfect for small business owners. It gives them the ability to track their monthly transactions easily.

You can categorize expenses and automatically calculate the remaining balance at the top of the spreadsheet. Start by entering the details such as date of transaction, account type, description, amount credited or debited, etc.

The template uses built-in functions to calculate the balance remaining.

Make sure to add your business name or logo to the template’s header. The template is pretty straightforward. Anyone can download and use it for free.

2. Financial Ledger Template by TemplateLab

Click Here To Use This Template!

This is a very simple yet valuable template for managing finances. It allows users to categorize expenses and automatically calculate the remaining balance below the header, similar to the previous template by SoftwareAccountant.

Make sure to include the bank details like the name and account number.

To keep a record of the transactions, enter the details such as the date, type, the amount credited or debited, and ending balance, etc.

The template uses built-in functions to calculate the end balance.

Note that the template allows you to customize the categories to suit your specific financial tracking needs. Whether you’re a small business owner or managing personal finances, this template offers a straightforward way to stay on top of your transactions.

3. Printable General Ledger Template by Smartsheet

Click Here To Use This Template!

Do you want to print your Ledger and keep a hard copy for your financial records?

This printable General Ledger template provides a structured format to record transactions. You can enter details such as transaction date and description, debit or credit, and end balance.

It is a perfect way of ensuring well-organized finances. The template is easy to use, customizable, and ideal for those who prefer manual bookkeeping. It offers a simple yet effective way of maintaining accuracy and clarity in your financial management.

4. Ledger Template by SpreadsheetPoint

Click Here To Use This Template!

Another fantastic template for bookkeeping. It is designed by SpreadsheetPoint for small businesses to track their transactions.

Refer to the example data to know how to use this template. Delete it once you understand the meaning of each column.

Similar to the previous templates, it automatically calculates the closing balance using a predefined formula. Make sure you add the opening balance in cell “B4”.

It is a simple template with a huge table spread across the screen. But don’t worry because you don’t need any expert knowledge to use this template.

5. General Ledger by FreshBooks

Click Here To Use This Template!

This is our last pick on this list. It is designed for small and medium-sized businesses.

The template is divided into various tables for recording things like owner investments, client sales, and general transactions.

For each transaction, you must start by adding the date, account type, account name, client/vendor name, and amount. You can add more rows easily to record your future transactions.

Note that the template uses formulas to calculate the total amount at the end of each table. When you add a row, the second task will be to expand the range within the respective formula.

How to create a Ledger Template using Excel

If you are not satisfied with the ready-made templates, then you can create one from scratch using built-in Excel tools and functions.

Excel boasts an intuitive and easy-to-use interface. One may hardly take a few minutes to learn to add or remove data.

Here is the template we are about to design from scratch:

The steps are pretty straightforward, but make sure you read till the end and do not miss any crucial steps that may mess up your finances at the end.

STEP #1 – Creating The Template Header

You can use the header to add personal or company information, such as the name and logo. It adds some personal touch and ensures clarity while collaborating with colleagues.

Let’s begin:

Open a new Excel workbook
Select the entire first row
Increase the height as shown in the following image
Select all the cells from “B1” to “I1”
Hover to the toolbar section and go to the “Home” tab
In the “Alignment” group, click on “Merge & Center”
Type “SoftwareAccountant – General Ledger“
(Make sure to replace the name “SoftwareAccountant” with your company name)
Increase the font size to 18
Change the font alignment to the middle
Change the font style to “Calibri“
Adjust the cell background color to green (HEX Code – #00ac46)
Change the font color to white

Here’s how our header looks,

STEP #2 – Creating The Summary Tables

We need the following three tables to summarize our account details and monthly finances,

Note that we are creating a Ledger template to track our monthly transactions. For a weekly or yearly basis, you need to make a few adjustments (more on this later in this article).

Let us start with the first table to enter duration and bank details,

Select the cell “B3“
Type “Month“
Next, select the cell “B4” and type “Bank“
Repeat the above 3 steps and create a table as shown below
Select all the cells from “B3” to “B6”
Change the cell background color to green (HEX Code – #00ac46)
Change the font color to white
Adjust the font alignment to the middle
Change the font type to “Calibri“

The table is ready, but let us apply borders using the following steps.

Select the range “B3:C6” as shown below
Go to the “Home” tab and click on the “Borders” dropdown in the “Font” group
Select “All Borders”
Change the border color to light gray by clicking on “More Borders”
A Format Cells dialog box will open. Make sure you are in the “Borders” section
In the color dropdown, select the light gray color

Our bank details table is done. Now, let’s create the opening balance table.

Select the cell “E3“
Type “Opening Balance“
Change the cell background color to green (HEX Code – #00ac46)
Change the font color to white
Adjust the font alignment to the middle
Change the font type of “Calibri“
Now, select the “E3” and “F3”

Apply the borders as discussed above and change the border color to light gray. We will repeat the same process for the Closing Balance table.

STEP #3 – Formatting The Summary Tables

We have already changed the cell background color, font styles, and borders.

Let’s validate the Date field so that it will accept only valid dates:

Select the cell “C3“
Go to the “Home” tab
Click on the “General” dropdown in the “Number” group
Click on “More Number Formats”
A Format Cells dialog box will appear on your screen. In the “Number” tab, click on the “Date” option
Select the date format from the available options shown below
Click on “OK” to apply the changes

Let us change the Opening and Closing Balance format as well.

We need to make sure whenever a value is entered within these cells, a dollar symbol appears at the beginning.

Select the cell “F3” and “I3”
Hover to the main menu and click on the “Home” tab
Click on the “General” dropdown in the “Number” group
Select “Accounting”

STEP #4 – Creating A Table to Record Transactions

This is the main step where we will create a table to record the transactions.

Let’s begin,

Select the cell “B8“
Type “Date“
Select the cell “C8” and type “Account“
Repeat the above steps and complete the table header as shown below
Now, select all the cells from “B8” to “I8”
Change the cell background color to green (HEX Code – #00ac46)
Change the font color to white
Adjust the font alignment to the middle
Change the font type to “Calibri“
Select the table range as “B8:I20“. Refer to the following image
Apply the borders by clicking on the “Borders” icon in the “Home” tab

Our table is ready.

STEP #5 – Formatting the Table that Tracks the Transactions

In this step, we will make sure the table created to track the transactions is correctly validated.

Let us start with the dates column.

Select all the cells from “B9” to “B20”
Right-click on the selected cells and select “Format Cells”
In the newly opened dialog box, select “Date” as shown below
Now, pick your preferred date format from the available options
Click on “OK”

Similarly, let’s make sure that whenever we type a number in the CREDIT, DEBIT, and Balance columns, a dollar symbol appears at the beginning.

Select the table range “F9:H20”
Go to the main menu and click on the “Home” tab
Click on the “General” dropdown in the “Number” group
Choose “Accounting” from the list

Finally, make sure to color code the CREDIT and DEBIT columns with light green and orange colors. Once done, our table is ready with all the formatting.

STEP #6 – Calculating the Remaining Balance after each transaction using Excel Functions

This is the crucial step that requires knowledge of built-in Excel functions. If you are new to Excel, then don’t worry and directly copy and paste the formulas discussed below.

Let us start with cell H9, where we need to calculate the end balance based on the Opening Balance and the amount credited or debited after the first transaction.

The formula is as follows,

=IF(AND(F9="",G9=""),"",F3+F9-G9)

Now, in the H10 column, the balance will be calculated based on the remaining amount after the first transaction and the amount credited or debited during the second transaction.

The formula will be as follows,

=IF(AND(F10="",G10=""),"",H9+F10-G10)

Let us copy this formula for the rest of the cells of the Balance column. All you need to do is select cell H10 and hover over the Fill Handle that appears on the lower-rightmost corner of the cell.