How to Create Sales and Purchase Ledger in Excel

If you are looking for a way to create a sales and purchase ledger in Excel then you are at the right place. Today, in this article, I am sharing with you how to create a sales and purchase ledger in Excel.

How to Create Sales and Purchase Ledger in Excel: 3 Simple Steps

In the following, I have explained 3 simple and easy steps to create a sales and purchase ledger in Excel.

Step 1: Create a Dataset with Proper Parameters

• Above all, open your workbook and type the “Company Name” and “Address”.
• Then, below company information start the ledger by creating small boxes with “Month Name”, “Opening Date”, Closing Date”, “Opening Balance”, and “Closing Balance”.

• Now, to complete the making of the dataset we will make 5 new columns to fill the data for “Date”, “Sales of Product”, “Cash Sales”, “Credit Sales”, and “Balance”.

Read More: How to Create Excel Checkbook Register with Reconciliation

Step 2: Use Formula to Calculate Sales Ledger

• In this step, we will fill the cells with data to complete our sales ledger.
• Let’s put the “Month Name”, “Opening Date”, and “Closing Date” manually.
• Now, let’s imagine we have an opening balance of $50,000 in our hands.

• Hence, for closing the balance, we will apply the following formula-

Where,

• The total amount will be added to the closing balance after completing sales for the total month.

• Press Enter to complete the formula.
• It will show a hyphen (–) as we haven’t put any values yet.

• Now, let’s fill the cells (B12:E18) with values collected from sales records.

• In order to check the total balance write down the below formula-

• Hit the Enter button to continue.
• Here we got the total sales amount sold on “5-January”.

• Similarly, we will put the formula for the next cell where the sold amount will be added with the previous total amount-

• Click enter to get the total amount after sales.

• Now, drag the “fill handle” down to cell (F18) to fill all the cells.
• As the total value will be calculated in reference to total cash and credit transaction thus we have not filled that cell.

• Starting with this step, we will calculate the total cash sold using the SUM function in Excel.
• Apply the formula in the cell (D19)-

• Press the Enter button to determine the total cash sales.

• In the same fashion, we will calculate the credit sales by writing the formula down in the selected cell (E19)-

• Click Enter.

• Finally, we will determine the total balance after all the cash and credit transactions. To do so-
• Put the formula down in the cell (F19)-

Where,

• The formula stands for, Total Balance = Opening Balance + Total Cash Sales – Total Credit Sales

• In conclusion, you will see our “Total Balance” is automatically added to the “Closing Balance”.

Step 3: Calculate Total Ledger Amount Using Formula in Excel

• In the same fashion, open another worksheet to create the purchase ledger.
• Similarly, just like the first step, create your dataset and fill it with all the “Purchased Records” for the following month.
• To start the calculation suppose we have $10,000 in our opening account.

• Just like the previous steps, we will determine the total balance for a single date in cell (F12) by applying the below formula-

• In the same style, for cell (F13) write the following formula-

• Now, drag the “fill handle” down to fill the cells.

• Just like our previous method, we will calculate the “Total Amount” for each column.

• After completing our calculation, in conclusion, we have successfully created our sales and purchase ledger in excel.

Things to Remember

• After applying the formula for the first transaction for a single date don’t drag the fill handle down. Because in the first-day transaction, the principal amount was the opening balance whether for the next cell, the principal amount will be the amount after the first transaction.

Download Practice Workbook

Conclusion

In this article, I have tried to cover all the steps to create sales and purchase ledger in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

<< Go Back to Excel Ledger Templates | Accounting Templates | Excel Templates