How to Create and Use Custom Sort in Excel

While working in Excel, sometimes we have to sort a data set in ascending order. Sometimes we have to sort them in descending order. These are accomplished pretty conveniently using Excel toolbar options or formulas.

But sometimes we have to sort a data set in a custom order. Those who don’t know how to sort a data set in a custom order in Excel, often face trouble while solving these types of problems.

Today I will be showing how you can create a custom sort list in Excel, and then use it.

Download Practice Workbook

Creating a Custom Sort List

Here we have a data set with the Names, Marks in Mathematics and Grades of some students of a school called Sunflower Kindergarten.

There are a total of 6 different grades. A+, A, A-, B, C, and F. To highlight them further, I have given them six different colors, from dark to light.

Our objective today is to sort the students according to their grades, from A+ to F.

That means the sorting sequence will be: A+, A, A-, B, C, F.

You can create a sort list in two ways.

1. Writing Directly in the Custom Lists Dialogue Box

1. To create a sorted list of this sequence, first go to File>Options from Excel Toolbar.

2. Click on Options. You will get a new window called Excel Options.

Click on Advanced from the left pane.

3. Scroll down through the window. Under the General section, you will find a button called Edit Custom Lists. Click it.

4. You will get the Custom Sort dialogue box.

Under the section Custom lists, you will find all the default sorting lists like Sun, Mon, Tue, Wed, Thu, Fri.

Or Jan, Feb. Mar. Apr. May, Jun. Jul, Aug, Sep, Oct. Nov, Dec.

Click on NEW LIST.

5. Then in the List entries section, write down the names of your custom entries, separated by comma (,), following your desired sort order.

For the sake of this example, write A+, A, A-, B, C, F.

6. Then click OK. You will find your list added in the Custom lists section.

2. Importing a List from the Workbook

1. First create the sorting list in your workbook maintaining the desired sequence.

2. Then follow steps 1-3 from the previous section to get the Custom Lists dialogue box.

3. Click on the Import icon right to the empty cell named Import list from cells.

4. You will find the Custom Lists dialogue box compressed like this. Select the list from the worksheet. The formula will be automatically inserted in the blank box.

5. Again click on the Import icon right to the box.

6. You will find the Custom List dialogue box back to the expanded form. Click on Import.

7. You will find your list added to the Custom lists.

8. Click on OK to finish the process.

Using the Custom Sort List

Now we have finished creating the custom sorting list.

This time we shall use this list to sort a data set.

1. Remember our data set with the names, marks, and grades of the students? Select that data set (along with the titles) and go to the Sort & Filter option under the Home tab.

2. Click on the drop-down menu. You will see a few options. Select Custom Sort.

3. You will get a Dialogue box called Sort.

Under the Column option, select the title of the column according to which you want to sort.

For the sake of this example, select Grade.

Under the Sort On option, select Values.

And under the Order option, select Custom List.

4. The moment you select Custom List in the Sort dialogue box, you will get the Custom Lists dialogue box. Select your desired list from the Custom lists.

5. Or in case you don’t have the Custom List built earlier, select NEW LIST and enter your list in the List entries sequentially.

6. Click on OK twice (Once in the Custom Lists box and once in the Sort box). You will get your data set sorted according to the custom order.

7. If you want, you can add another level in the Sort Panel to sort the marks in descending order after sorting according to the grades.

8. Click Ok. And you will get the data set sorted according to the descending order of the marks along with the grades.

Conclusion

Therefore, using this method, you can sort any data set in any custom order according to your wish. Do you have any questions? Feel free to ask us.