In this Excel tutorial, we’ll learn how to create a ledger in Excel. We will also learn how to format a ledger and even maintain a Ledger in Excel.
While preparing this article, we used Microsoft 365 for applying all operations but they are also applicable in all Excel versions.
We will use Excel’s built-in functions (MID, CELL, FIND, DATEVALUE, EOMONTH, and SUM) and other features to create a ledger. We will also create ledgers in Excel for different purposes.
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 Practice Workbook
What Is 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 are usually 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 usually two types:
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?
In this section, we will learn how to create a Ledger in Excel by following the detailed steps mentioned below. We will also learn how to edit, format and maintain Ledger.
Step 1: Make Layout of Ledger
- A Ledger 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 as we want.
Step 2: Create a Monthly Ledger
Now we are going to create the monthly ledger account dataset to keep records of our financial activities.
- In cell G3, write down 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 type the 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 Ledger
- Inserting the transaction date, Bill ref. and descriptions and values of the debit and credit balance, we will get the following image.
- Insert the following SUM formula cell E18 to calculate the total debit balance.
- Then, drag the formula to cell D18 for total credit.
=SUM(E11:E17)
- Write down the following Mathematical formula to calculate the total running balance of January 2023, which represents the closing balance of that month in cell G18.
=D8-E18+F18
- Write down the following Mathematical formula to calculate the running balance of the first date in cell G11.
=D8-E11+F11
- Select cell G12 and put down the formula below.
- Hence, AutoFill it up to cell G17.
=G11-E12+F12
Here G12, E13, and F13 serve as the corresponding Balance of the previous entries, Debit and Credit.
Finally, the ledger for the month of January.
- Enter the following formula in cell G8 to calculate the closing balance.
=G18
Similarly, we’ll make a 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 General Ledger
We’ll create a Pivot table for our General Ledger using the Journal data.
- Select data range B4:F20.
- Go to Insert tab >> Tables >> PivotTable.
- PivotTable from table or range dialog box appears >> Select Existing Worksheet >> Inserting ‘Gen Ledger’!$H$4 in the Location box >> 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 Insert tab >> Filters >> Slicer.
- Insert Slicers dialog box pops up >> Check the Account option >> OK.
- Hence, a Slicer named Account is added to the worksheet.
- Now, let’s check if our Slicer will work or not. To do that, select the Cash option, and the Slicer will automatically filter the pivot table data.
- Look at the screenshot below to get a clear understanding.
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.
- Additionally, 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.
- Moreover, 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.
In conclusion, using a ledger in Excel streamlines financial record-keeping improves data accuracy, and supports better decision-making through insightful analysis and reporting.
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
1. Why should I use a ledger in Excel?
Answer: 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.
2. Can I generate financial reports from my ledger in Excel?
Answer: 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.
3. How to Maintain a Ledger Book in Excel?
Answer: 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.
4. Is there any alternative to Excel ledger?
Answer: 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.
Conclusion
From the above discussion, you have learned how to create a ledger in Excel for different purposes. Using Excel for maintaining a ledger provides a practical and accessible solution for businesses.
With its familiar interface and spreadsheet capabilities, Excel allows for efficient data entry, organization, and basic calculations. It offers flexibility in designing a customized ledger format and supports easy sorting and filtering of data.
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!
