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.
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.
Step 2 – Create a Monthly Ledger
- In cell G3, use the following formula.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&" "&2023
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.
- To get the first date of a month, use the following DATEVALUE function in cell D7.
=DATEVALUE("1"&G3)
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)
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.
- 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.
- 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
- Use the following formula to calculate the running balance of the first date in cell G11:
=D8-E11+F11
- 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.
- Enter the following formula in cell G8 to calculate the closing balance.
=G18
- 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.
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.
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.
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.
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.
- The PivotTable from table or range dialog box appears. Select Existing Worksheet.
- Insert ‘Gen Ledger’!$H$4 in the Location box.
- Click OK.
- You will be able to see the PivotTable after checking the following options in the PivotTable Fields.
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.
- The Insert Slicers dialog box pops up. Check the Account option and click OK.
- A Slicer named Account is added to the worksheet.
- Let’s check if our Slicer will work or not. Select the Cash option and the Slicer will automatically filter the pivot table data.
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
- How to Make a Ledger in Excel
- How to Make a General Ledger in Excel
- Create a General Ledger in Excel From General Journal Data
- How to Maintain Ledger Book in Excel
- How to Create a Checkbook Ledger in Excel
- How to Make a Bank Ledger in Excel
- How to Make Subsidiary Ledger in Excel
- How to Export All Ledgers from Tally in Excel
<< Go Back to Excel for Accounting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
