Sole Trader Bookkeeping Excel

Get FREE Advanced Excel Exercises with Solutions!

This article will teach us how to create a sole trader bookkeeping Excel. Here, we will create a template and add all the information to complete the template. This template contains different payment methods, and we will get all information regarding the payment method you select.

Bookkeeping is an important and useful way to keep track of all the information in a business. You can get every information, a cost breakdown, and a profit and loss breakdown as well. This is the best way to keep all the information in one place for a business.


Download Practice Workbook

You may download the below workbook for practice.


How to Create a Sole Trader Bookkeeping in Excel: Step by Step Procedure.

Here we will show the step-by-step procedure to create sole trader bookkeeping in Excel. This procedure is quite simple and helps trace all the data.

Step 1: Adding Book Names with Prices, Quantities, and Order Date

  • Initially, write down all the required data (Book name, Quantity, Price, Payment Method, and Discount Applied) to add to the template.

Creating a template to get sole trader bookkeeping Excel


Step 2: Adding Pickup Points and Dates

  • Here, add some more information to improvise the template. For instance, add Pickup Points, Delivery Dates, and all the available Price Methods to add information.

Adding more information to get sole trader bookkeeping Excel


Step 3: Selecting Payment Method for Creating Sole Trader Bookkeeping

Therefore, to get detailed data for a selected payment method, create a drop-down list using the below process.

  • In the beginning, select cell I9 and go to Data >> Data Tools >> Data Validation to add the list in that cell.

Adding data validation

  • After that, the Data Validation dialog box will pop up.
  • Select, the List from the Data Validation Criteria option and select the source range H12:H15 to get the data from that range.
  • Lastly, select OK to complete the process.

Data validation dialog box pop up

  • Finally, the drop-down list is created as below.

Selecting particular payment method


Step 4: Getting Details for Selected Payment Method

  • Now, select cell B19 and enter the FILTER function to get detailed information for the selected payment method.
=FILTER(B7:D16,E7:E16=I9)

Adding FILTER function


Step 5: Sum Up the Total Payment for Selected Methods for Sole Trader Bookkeeping

  • Therefore, select cell I7 to sum up the total price of the selected method and complete the template using the SUM function.
=SUM(D19:D24)

Getting sole trader bookkeeping Excel


Things to Remember

  • While adding a drop-down list, select unique values as the source range if there is a duplicate value in the source range, then there will be duplicate values in the drop-down list.
  • The FILTER function works as an array, and we cannot select any cell value in that array.

Frequently Asked Questions

Q1: How to do bookkeeping for sole traders?

Ans: Bookkeeping is a method to keep detailed information about sales, costs, and products. You can use a template to collect all this data.

Q2: Can I do my bookkeeping in Excel?

Ans: Yes, you can do bookkeeping in Excel if the business is small, as you cannot add a huge range of information in Excel.

Q3: What is the best spreadsheet for self-employed people?

Ans: Excel is an excellent template to use for self-employed people. You can add all your information to an Excel sheet.


Conclusion

Here, we learned how to create a sole trader bookkeeping Excel template. Therefore, we create a template and add all the required information. The bookkeeping shows the different payment methods of a bookstore, and you will get detailed information about all the payment methods and the total price as well. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afrina Nafisa
Afrina Nafisa

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo