Need to learn how to make a ledger in Excel? If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 5 easy and convenient steps in making a ledger in Excel. Show Table of Contents hide Download Practice Workbook What Is a Ledger? Step by Step Guidelines to Make a Ledger in Excel Step-01: Create Layout of Ledger in Excel Step-02: Make a Monthly Ledger in Excel Step-03: Give Some Sample Data as Input into Ledger in Excel Step-04: Add Other Months Step-05: Generate a Summary Conclusion Related Articles Download Practice WorkbookYou may download the following Excel workbook for better understanding and practice yourself. Making Ledger.xlsx What Is a Ledger?Ledger is an essential document for any organization. It shows us the details of debit and credit and the current balance of that company after every transaction. Sales Ledger Purchase Ledger General Ledger Step by Step Guidelines to Make a Ledger in ExcelTo demonstrate the procedure, we will show you the making approach of a three-month ledger book with the summary in Excel. The procedure is discussed below step-by-step: Step-01: Create Layout of Ledger in ExcelIn the first step, we shall construct a space where we can include all the pertinent details about the organization. In this section, we’ll make the appropriate space in each monthly ledger.
Note: Also, we can do the same work by pressing CTRL+SHIFT+L.
Read More: Create General Ledger in Excel from General Journal Data Step-02: Make a Monthly Ledger in ExcelIn this step, we are going to generate the monthly ledger account dataset to keep the records of our financial activities.
Formula Breakdown
At this point, we can see the name of our Sheet on this cell with 2022. Note: While typing this formula, make sure to enter any cell references on this sheet. Otherwise, the formula won’t work properly. For example, here we’ve entered the reference of cell A1.
The DATEVALUE function converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code.
The EOMONTH function gives the inferred number of months before or after the start_date. It is the sequential number for the closing day of the month. At this moment, the worksheet is ready to use as a monthly ledger sheet. Read More: How to Maintain Ledger Book in Excel (with Easy Steps) Step-03: Give Some Sample Data as Input into Ledger in ExcelIn this third step, we will input sample data into our ledger book. Let’s follow the steps carefully.
Here, D8, E12, and F12 represent the Opening Date Balance, Debit, and Credit respectively.
Here G12, E13, and F13 serve as the corresponding Balance of the previous entries, Debit and Credit.
It calculates the total Debit in the E12:E18 range.
It calculates the total Credit in the F12:F18 range.
Here, D8, E19, and F19 represent the Opening Balance, Total Debit, and Total Credit consecutively. Notice that the amount in cell G18 and in cell G19 are the same. So we can be sure that the calculation is correct. It’s one kind of cross-checking.
Read More: How to Create a Checkbook Ledger in Excel (2 Useful Examples) Step-04: Add Other MonthsIn this step, we’ll create ledgers for other months also. So, let’s just follow these steps.
Here, the Opening Balance is equal to the Closing Balance for January month.
Here, we have entry till Row 16. If we want to add other entries below, we can do that easily. Because we’ve transformed the data range into a table previously.
Notice that the Totals in Row 18 and Balance in cell G17 are automatically calculated.
Read More: How to Make Subsidiary Ledger in Excel (With Easy Steps) Step-05: Generate a SummaryIn the final step, we’ll create a summary of the monthly ledger sheets. Just follow along.
Here, we’re sourcing this data from cell G19 of the sheet Jan. It contains the Total Debit amount for the month of January.
It calculates the Total Debit in these three months.
Here, D8, E14, and D14 represent the Opening Balance, Total Debit, and Total Credit consecutively.
Read More: How to Make a Bank Ledger in Excel (with Easy Steps) ConclusionThis article provides easy and brief solutions to make a ledger in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more. |