Ledger in Excel (Complete Guideline)

A ledger is a book or a digital system used to record and track financial transactions. It provides a comprehensive overview of a business’s financial activities. It shows us the details of debit and credit and the current balance of that company or a business after every transaction.

Ledger in Excel


Download the Templates


What Is a Ledger?

A ledger is a book, document, or electronic system used to record and track financial transactions. It shows us the details of debit and credit and the current balance of that company after every transaction.

Ledgers are essential for maintaining accurate financial records, analyzing financial performance, and preparing financial statements.

Ledger books usually come in 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 split into two subtypes:
    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.

How to Create a Ledger in Excel?

Step 1 – Make a Layout of a Ledger

  • The Ledger template contains the debit and credit and the current balance after every transaction. It also contains the opening and closing date and balance with the Company name and address.
  • After creating the layout, we’ll format the ledger sheet.

Creating Layout of Ledger


Step 2 – Create a Monthly Ledger

  • In cell G3, use the following formula.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&" "&2023

Calculating Sheet Name with Year

This formula returns the sheet name in the selected cell.

Formula Breakdown

  • CELL(“filename”, A1): The CELL function gets the complete name of the worksheet
  • FIND(“]”, CELL(“filename”, A1)) +1: The FIND function will give you the position of ] and we’ve added 1 because we require the position of the first character in the name of the sheet.
  • 255: Excel’s maximum word count for the sheet name.
  • MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255): The MID function uses the text’s position from start to end to extract a specific substring.

=DATEVALUE("1"&G3)

Calculating first date of a month

The DATEVALUE function converts the date in the form of text to a number that represents the date in Microsoft Excel date-time code.

  • We’ll calculate the end date of that month using the following formula in cell G7:
=EOMONTH(D7,0)

Calculate the end date of this month

The EOMONTH function gives the closing day of the month from a given date.


Step 3 – Inserting Data into the Ledger

  • Input the transaction date, Bill reference, and descriptions and values of the debit and credit balance manually. Here’s a sample.

Inserting the transaction date, Bill ref and descriptions of the debit and credit balance

  • Insert the following SUM formula cell E18 to calculate the total debit balance.

=SUM(E11:E17)

  • Drag the formula to cell D18 for total credit.

Using the SUM function to calculate the total debit and credit balance

  • Use the following formula to calculate the total running balance of January 2023, which represents the closing balance of that month in cell G18:

=D8-E18+F18

Calculating closing balance

  • Use the following formula to calculate the running balance of the first date in cell G11:

=D8-E11+F11

Calculating the running balance of Ledger

  • Select cell G12 and insert the formula below:

=G11-E12+F12

Here G12, E13, and F13 serve as the corresponding Balance of the previous entries, Debit and Credit.

  • AutoFill the formula to cell G17.

Using Mathematical formula to calculate the Ledger in Excel

  • Enter the following formula in cell G8 to calculate the closing balance.

=G18

Calculate the closing balance

  • We’ll make a similar ledger for February and March.

Step 4 – Making a Summary of Ledger

The summary of the ledger for the months of January, February, and March looks like the image below.

Summary of the Ledger in Excel


How to Make Business Account Ledger in Excel?

A business account ledger is a subset of the general ledger that focuses specifically on tracking transactions related to a particular business account. It may record all transactions associated with a specific bank account, a specific client, or a specific project.

You can make a Business Account Ledger like the above process and the final output is given in the below screenshot.

Making Business Account ledger in Excel

Click here to enlarge the image


How to Create a Party Ledger Reconciliation Format in Excel?

While the general ledger provides an overview of all financial transactions, the business account ledger focuses on a specific account within the overall ledger, and the party ledger concentrates on transactions associated with a specific party or entity.

Party Ledger Reconciliation is the process of comparing the balances in an organization’s ledgers with the balances in the corresponding ledgers of its counterparties. The reconciliation is done to ensure that the two sets of ledgers are in agreement.

You can see a Party Ledger in Excel in the below screenshot.

Customer Summary of Party Ledger


How to Create a General Ledger From General Journal Data?

The general journal is an accounting record of any company’s financial transactions. It is the master of all company entities. Each transaction that a company produces throughout the year is recorded in its general journal.

A typical general journal consists of 5 different columns such as date, account, reference, debit, and credit columns. You can create a General Ledger from General Journal data in Excel. Let’s follow the instructions below to learn!

Step 1 – Create General Journal Data

For a General Journal, create a data model like the below screenshot and make a table for that data.

Creation of General Journal data

Step 2 – Use Journal Data to Create a General Ledger Pivot Table

  • Select the data range B4:F20.
  • Go to Insert tab, choose Tables, and select PivotTable.

Selection of the PivotTable feature

  • The PivotTable from table or range dialog box appears. Select Existing Worksheet.
  • Insert ‘Gen Ledger’!$H$4 in the Location box.
  • Click OK.

Selecting the Existing Sheet Location

  • You will be able to see the PivotTable after checking the following options in the PivotTable Fields.
Check the PivotTable Fields options

Click here to enlarge the image

  • Select any cell of the pivot table (we have selected cell H4).
  • Go to the Insert tab, select Filters, and choose Slicer.

Selection of the Slicer feature

  • The Insert Slicers dialog box pops up. Check the Account option and click OK.

Check the Account option

  • A Slicer named Account is added to the worksheet.

Inserting Slicer to Filter data

  • Let’s check if our Slicer will work or not. Select the Cash option and the Slicer will automatically filter the pivot table data.
Selection of the Cash option from the Slicer

Click here to enlarge the image


What Are the Benefits of Using Ledger in Excel?

Creating a ledger in Excel offers numerous advantages for financial management.

  • It provides a structured framework for recording and organizing financial transactions for individuals and businesses alike. Excel’s powerful functions and features, such as SUM, MID, and EOMONTH, facilitate efficient data manipulation and calculation, reducing manual errors and saving time.
  • A ledger in Excel allows for precise tracking of income, expenses, and account balances. This helps users maintain a clear overview of their financial situation, facilitating budgeting and financial planning.
  • Excel’s customization options enable users to tailor the ledger to their specific needs. You can design your ledger layout, add additional columns for notes or categorization, and apply formatting for enhanced clarity and readability.
  • Excel’s flexibility ensures that ledger entries can be easily updated and adjusted as needed, providing real-time accuracy in financial data. This adaptability is crucial for businesses experiencing growth or changes in financial activity.

What Things You Have To Remember?

  • While creating a general ledger, you need to use the slicer from the PivotTable Analyze tab.
  • After creating a pivot table, we convert it into a tabular form to make it better to understand.
  • You can also create a table using keyboard shortcuts Ctrl + T.

Frequently Asked Questions

Why should I use a ledger in Excel?

Using a ledger in Excel offers several benefits.

  • Provides a structured way to record and analyze financial data
  • Allows you to easily track income and expenses,
  • Monitor account balances, and generate reports.

Can I generate financial reports from my ledger in Excel?

Yes, Excel allows you to generate various financial reports from your ledger data. You can use built-in functions and tools to summarize data, create charts and graphs, and calculate totals, averages, and other relevant metrics. With the flexibility of Excel, you can design custom reports that meet your specific reporting requirements.

How to Maintain a Ledger Book in Excel?

You can maintain a Ledger by creating a sheet in Excel with columns for Date, Description, Debit, Credit, and Balance. Enter opening balances, record transactions, and calculate balances using formulas. Regularly review and reconcile data for accuracy. Customize columns and apply formatting as needed. Backup your ledger regularly to prevent data loss.

Is there any alternative to Excel ledger?

Yes, there are alternatives available instead of using Excel ledger. You can use specialized accounting software or online financial management tools that offer more advanced features for tracking and analyzing financial transactions. These tools often provide automation, real-time syncing, and comprehensive reporting capabilities beyond what Excel can offer.


Ledger in Excel: Knowledge Hub


<< Go Back to Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo