# 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.

## 3 Simple Steps to Create Sales and Purchase Ledger in Excel

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

### Step 1: Create 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”. ### 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-
`=F19`

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-
`=C9+D12-E12` • 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-
`=F12+D13-E13` • 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)-
`=SUM(D12:D18)`
• 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)-
`=SUM(E12:E18)`
• 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)-
`=C9+D19-E19`

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-
`=C9-D12+E12` • In the same style, for cell (F13) write the following formula-
`=F12-D13+E13` • 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.

## 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.

## Related Articles #### Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts 