It is a fact that data becomes useless when you cant get any useful insight from it. Deriving useful insight from data depends on how well the user can manipulate said data to extract insights. The challenge sometimes is that the data may be too voluminous to go through line by line, and this is where analytical tools would prove very useful. There is a need to organize and sort the data to be able to identify trends inherent, in order to be useful for future forecasting.
Thankfully, excel has plenty of tools to assist in manipulating data, in order to transform data into useful insights. In today’s post, we would touch on advanced sorting techniques in excel and take you step by step through each process. Some of these sorting techniques include the following:
- Sorting from top to bottom
- Sorting from left to right
- Multiple level data sorting
- Sorting based on cell color
- Case sensitive sorting
- Sorting based on custom list
- Sorting by using SortBy function
Sorting from Top to Bottom
We have two sets of examples to illustrate all the sorting techniques we stated above. In this section, we have been provided with information on products sold by an international sports shop in their various locations as our first example.
Our goal is to sort by the Product Name column. To do that, we only need to follow the steps below:
Step 1: select your range of data in column A and navigate to the data tab > Sort & Filter > Sort
Step 2: A sort warning box pops out, select expand selection and click OK. Please note that the second option (continue with current selection) should be used in the column you selected is not related to nearby columns.
Step 3: In the Sort dialog box, we would select Sort by “Product name” in the dropdown list.
Step 4: Click on OK and you are good to go.
Result: The whole data set is sorted by Product name
Sorting from left to right
We would be explaining this section with the help of our second example. In this example we have a horizontal data set that requires sorting from left to right. The data set contains information on 5 employees, and their self-appraisal and manager appraisal scores in the recently concluded performance evaluation.
To sort based on employee name (row), please follow the steps below:
Step 1: Select the employee details from rows 3 to 5
Step 2: Navigate to the data tab > Sort & Filter > Sort
Step 3: In the Sort dialog box, untick “my data has headers” and click on Options
Step 4: In the small pop out box, select Sort left to right and click on ok
Step 5: In the sort by drop down, select the row you want to sort with (row 3 in our case) and click on OK when you are done.
Result: Your data should be sorted in ascending alphabetical order based on the values of row 3
Multiple Level Data Sorting
Using the data set in our first example, we would illustrate how to perform a multiple level data sorting. Recall that we had the sales performance of 10 products for a multinational sports shop. In this section we would be sorting our data based on the shop location and then the Sales Volume.
The first step is to select your data and pop out the Sort dialog box as discussed in previous sections:
Step 2: In the Sort dialog box, we would select Shop location as our first level sorting, then we click Add level and select Sales Volume (000) as our second level Sorting. Finally, click on OK
Result: Our data is sorted, first by Shop Location in ascending order, and then by Sales Volume from Largest to Smallest.
Sort by Cell Color
We are adopting the same example in the previous section; however we are adding colors to our shop location column. Let’s see how it looks below:
We intend to Sort our data according to the colors from left to right below:
Step 1: Select your data and navigate to the data tab > Sort & Filter > Sort
Step 2: Choose “Shop Location” as criteria to Sort by and for the Sort on section, select sort on Cell Colour. Now in the Order section, select the color you want to display first.
Step 3: Rinse and repeat 1, 2 and 3 in the screenshot above for the other four colours. Please remember that you need to click on Add level to create subsequent lines to select the next color.
Result: Our data is sorted according to the order we specified above
Case Sensitive Sorting
For this section, we have duplicated two of our products (padel bags and wrist brace) to illustrate. A pair of the products are in small letters whilst the other pair are in Capital letters.
Let’s see how excel handles the sorting when we turn on case sensitive sorting by following these steps:
Step 1: Select your data and open up the Sort dialog box as discussed in previous sections.
Step 2: Select “Product Name” in the Sort by dropdown and click on Options
Step 3: After completing the preceding step, a small selection box would pop out. Make sure to select case sensitive and click on ok.
Step 4: After clicking on ok, you are returned to the previous sort dialog box. Click on OK again and you are done.
Result: We have the new data sorted accordingly. The small letters appear before their equivalent in CAPS.
Sort based on Custom List
In excel, you can select a column in your data set and sort your data based on a predefined / custom list. In our example, we would specify an order for our shop location column and then sort our data based on that order. The order for our custom list is as follows:
Minnesota > Paris > London > California > Sao Paulo
To sort based on the above order, we only need to follow the steps below:
Step 1: Select your data and open the sort dialog box by following the steps in the previous section.
Step 2: Select Shop Location as the sorting criteria, then click the Order drop down and select Custom List
Step 3: In the custom lists dialog box, type the shop locations in the order specified above and separate by hitting the space bar. Then click OK when done.
Step 4: Now you are returned back to the initial Sort dialog box. Click on OK and you are done.
Result: Our data is sorted according to the custom list specified
Sort Using SORTBY Function
Now it is time to make use of the sales volume figures we have. With the SORTBY function, we would sort based on sales volume in descending order.
We would create a section to the right to output SORTBY Function and with the help of the formula below, we would output the entire data sorted from highest sales volume to lowest sales volume:
Result:
