Custom Sorting in Excel

One of the reasons Microsoft Excel is so popular and commonly used is because of two overused features, Sorting and filtering. They are widely used to arrange, filter and analyze the data based on some defined conditions.

There are various sorting options already available in Excel. But what if you want to apply some customized sorting option that is not available in the predefined list? To cater to such a problem, Excel provides a default function known as the Custom Sort option. This option allows the users to define their custom order and sort the data per their requirements.

Let's get started knowing more about the custom sort, the steps to create a custom sort in your Excel worksheets and various other advanced applications.

What is Custom Sorting?

"Excel Custom Sorting is a feature allowing users to sort the data based on their preferences and requirements. To sort the order based on precedence, Excel provides the option to create a custom list for the preferred sorting order."

Custom Sorting in Excel is used to define your own custom order. At times, you may need to sort data that is not an alphabetical or ascending sort. Excel lets you create your own custom lists to sort data. For instance, in the below given data we want to sort the data on basis of continent, Asia, Africa, Europe, US.

Here we will apply the Custom Sorting option and put the order of the continents in the List entries textbox (as shown below).

Excel will sort the data on the basis on the specified condition. You will get the following output.

Steps to create a Custom Sorting

In the below Excel worksheet, we are given a list of Students whose scores are arranged in any order. In Excel, there aren't any options to sort values based on grades. Now how to solve this?

The solution to this problem is Custom Sorting. We will solve this problem using the custom sort dialogue window. To create a custom sorting, follow the below-given steps:

1. The first step is to select any cell inside the specified data set.
   
2. Next, click on the Data tab from the Excel ribbon tab. Go to the Sort & Filter group-> click on the Sort option.
   
3. The Sort window will appear as shown below. From the resulting window, click on the sort by drop-down list. You will have different options. Select the one based on which you want to sort your data. In our case, we have selected the Score option.
   
4. Next, we will specify the Order. Click on the Order drop-down and select the Custom List option from the resulting window.
   
5. Excel will open the Custom Lists dialog box (as shown below). Type the order of your grade in the List entries textbox. Since we have to arrange it from high to low so it will be: A1, A2, B1, B2, C1, C2, D1, D2, F
6. Click on Add button to add the sorting option in the custom lists.
7. Click on OK.
   
8. If your data has headers, make sure to tick the checkbox option 'My data has headers'.
9. Once all is done, click on the OK button to apply the sort.
   
10. As a result, Excel will sort the data on the basis of high to low grades.

NOTE: If two students have the same grade, the first one will be put first on the list if no other sorting is applied.

Yes! The Custom Sort is that's easy.

But in this example, we have only sorted the text based on grades (we have sorted a single column). What if we want to sort it based on grade, and if two students have same grades, the sorting should occur based on the naming alphabets?

Now let's explore how to apply the custom sort for multiple columns.

How to sort the multiple columns in a data?

From the previous section, we arranged the entire data from an order of Max, Average, Min. Refer to the below output.

The above data shows that Smith, Fedrick and Lark have the same grade, A1, but the names are not arranged alphabetically. We can apply a second sorting option that will make sure, to sort the names alphabetically in case students gets the same grades. This is known as Multiple Sorting.

When you use a multilevel sort, Excel sorts the data according to the levels you set up in the window. For example, in our setup, Excel will sort by Shoes and then Jackets using a Cell Color for both.

Below given are the steps to apply multiple sorting in the above dataset:

NOTE: We are starting the steps where we left them in the earlier section. Make sure to cover the above sorting steps.

1. Select any cell inside the specified data set. Click on the Data tab from the Excel ribbon tab. Go to the Sort & Filter group-> click on the Sort option.
   
2. The Sort window will appear as shown below. It will already show the first sorting formula that we have already applied in our data set. To apply the second sorting option, click on the Add level option.
   
3. It will add another sorting column. Click on the sort by drop-down list. You will have different options. This time, we will select the 'Students names' field since we have to sort the names alphabetically.
4. In the Order list, we will select A to Z option.
   
5. Click on OK
   
6. Excel will sort the strudent's names alphabetically who have got the same grade. Refer to the below output.
   
7. If you want you can continue to add more levels to the sort. You can even change the order of the levels of the sorting. Select a level and use theMove UpandMove Downarrows to arrange the sort levels.
8. To remove a level from the custom sort, select it and clickDelete Level.
   

Hope this tutorial has given you complete knowledge about Custom Sorting in Excel. However, you can try it with some advanced options as well, unlike custom sorting with conditional formatting, etc.,