How to Organize Sales Leads in Excel (with Quick Steps)In the middle of heaps of data and information, it is difficult to discern the relative importance of data between them. For better clarity and ease of access, the organization of the data is very important. In this article we are going to show in steps, how you can organize your data in Excel from lowest to highest in detailed explanation.
We are going to organize the sales record information based on different criteria in elaborate steps. First, we will show you in steps how you can calculate different parameters, and then organize them by sorting them.
Step 1: Preparing Dataset in Excel
- In the beginning, we need to input the basic information of the sales data.
- This includes the Products and their Quantity, Unit Price, and the Revenue we generated throughout selling.
- We need to be extra careful while filling up the table as any kind of error in the input can lead to faulty results and insight.
Step 2: Evaluating Cost and Profit Margin
After we have the Product Id and their Quantity, Unit Price, and Revenue. We need to estimate the cost per unit in the Cost column.
To do this, select cell G5 and enter the following formula:
=F5*E5
- Then drag the Fill Handle to cell G18.
- Doing this will calculate the cost of producing the products in cells C5:C18.
- Next, select the cell J5, and enter the following formula:
=(I5-G5)/I5
This formula will show the profit margin of the products in the range of cells C5:C18.
- Then drag the Fill Handle to cell J18.
- Doing this will calculate the cost of Profit Margin in the range of cells C5:C18.
- For better clarity, we better express the Profit Margin in percentages.
- To do this, we need to select the whole range of cells J5:J18, and then click on the Percentages symbol in the Number group.
- After clicking on the Percentage Symbol, we will notice that the Profit Margins now showing in the percentage symbol.
- Right after some formatting, our Profit Margin data will look like the below.
Step 3: Organizing Sales Data from Lowest to Highest by Quantity
With our dataset prepared, we can organize our Sales Records according to the Quantity of products sold.
- We first need to organize the sales record by the Quantity column value.
- To do this, select the Sort command from Sort & Filter group in the Data tab.
- After then, there will be a new dialog box asking for the expansion of the table. Select the Expand the selection and click on Sort.
- Clicking on Sort will select the whole dataset.
- Then, on the Sort dialog box, click on the sort by drop-down menu and select Quantity.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Click OK after this.
- After clicking OK, you will notice that the dataset is now organized from lowest to highest according to the Quantity value of the products in the range of cells B5:B17.
Read More: How to Organize Raw Data in Excel
Step 4: Arranging Sales Data by Unit Price in Excel
Our next step will be to sort the Sales Records according to their Unit Price.
- First, click on the Sort command once again.
- On the Sort dialog box, click on the Sort by drop-down menu and select Unit Price.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Click OK after this.
- The dataset will now be sorted from lowest to highest by the product’s Unit Price value in the fields B5:B18 once you click OK.
Step 5: Organizing Sales Data from Lowest to Highest by Cost
By sorting our sales records by the Cost of production, we will organize our sales records in the next step.
- First, click on the Sort command once again.
- On the Sort dialog box, click on the Sort by drop-down menu and select Cost.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Click OK after this.
- The dataset will now be sorted from lowest to highest by the product’s Cost value in the fields G5:G18 once you click OK.
Read More: How to Organize Sales Leads in Excel
Step 6: Managing Sales Data by Rating
By sorting them based on customer ratings, we will organize the records.
- First, click on the Sort command once again.
- On the Sort dialog box, click on the Sort by drop-down menu and select Rating.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Click OK after this.
- After clicking OK, you will notice that the dataset is now organized from lowest to highest according to the Rating of the products given by the customers in the range of cells H5:H18.
Step 7: Organizing Excel Sales Data from Smallest to Largest by Revenue
Our sales records can be sorted by Revenue, which will make them more organized.
- First, click on the Sort command once again.
- On the Sort dialog box, click on the Sort by drop-down menu and select Revenue.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Click OK after this.
- After clicking OK, you will notice that the dataset is now organized from lowest to highest according to the Revenue of the products generated through selling products in the range of cells I5:I18.
Step 8: Sorting Sales Data by Profit Margin
Sorting the sales records based on Profit Margin can help organize them.
- First, click on the Sort command once again.
- On the Sort dialog box, click on the Sort by drop-down menu and select Profit Margin.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Click OK after this.
- After clicking OK, you will notice that the dataset is now organized from lowest to highest according to the Profit Margin of the products sold in the range of cells J5:B18.
And this is how you can organize your sales data in Excel from lowest to highest quite easily.
Read More:Â How to Organize Expenses in Excel
Step 9: Organizing Sales Data from Lowest to Highest by Multiple Criteria
We will organize our sales records based on a number of criteria in the next step. So first we will sort the sales records by Rating, and then we will again sort by Profit Margin.
- Click on the Sort command in the Data
- On the Sort dialog box, click on the Sort by drop-down menu and select Rating.
- Next, select Cell Values in the Sort On drop-down menu.
- Finally, select Smallest to Largest in the Order group.
- Right after this, click on the Add Level on the top.
- This will add another row of sort criteria named Then by.
- Click on the drop-down menu box, and select Profit Margin.
- In both criteria, choose Smallest to Largest in the Order column.
- Click OK after this.
- After clicking OK, we will notice that the sales record is now organized.
- This record is sorted by Rating first, then by Profit Margin.
And this is how we managed to organize the data from lowest to highest in Excel in a total of 9 steps.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to Organize data in Excel is answered here in separate steps with elaborate explanations.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section.
