How to Create and Use Custom Sort in Excel

Creating a Custom Sort List 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.

Data Set to Custom Sort in Excel

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.

Options in File Menu in Excel

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

Click on Advanced from the left pane.

Excel Options Window

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

Edit Custom Lists Button in Excel

  1. 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.

New List in Custom Sort Dialogue Box

  1. 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.

Creating a Custom Sort List in Excel

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

New List Added in Custom Lists

2. Importing a List from the Workbook

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

Creating a Sorting List in the Workbook

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

Custom Lists Dialogue Box in Excel

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

Importing Custom Sort List from the Workbook

  1. 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.

Selecting Custom Sort List from the Workbook

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

Importing Custom Sort List from the Workbook

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

Custom List Dialogue Box in Excel

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

A New List Added to the Custom Lists

  1. 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.

Sort & Filter Option in Excel Toolbar

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

Custom Sort from Sort & Filter in Excel Toolbar

  1. 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.

Sort Dialogue Box in Excel

  1. 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.

Custom List Dialogue Box from the Sort Dialogue Box

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

Creating New Sort List in the Custom List Dialogue Box

  1. 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.

Data Set Sorted Using Custom Sort

  1. 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.

Adding Another Sorting Level after the Custom Sort Level

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

Data Set Sorted with two Levels of Sorting

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo