How to Create Custom Sort List in Excel

Data Set Sorted According to Custom List

While working in Excel, we often have to sort a range of cells according to our own needs. Sorting these manually may take a lot of time and it is quite troublesome too.

Creating your custom sort lists comes very handy in these situations.

Today I will be showing how you can create your own custom sort list.

Download Practice Workbook

How to Create Custom Sort List in Excel

Here we have a data set with the Names, Marks and Grades and some students in an examination of Saint Xavier’s High School.

Data Set to Create Custom Sort in Excel

The grades are divided into 6 categories, A+, A, A-, B, C and F.

To see clearly, I have given them six different colors.

The order of the grades is A+, A, A-, B, C, F.

Our objective today is to create a custom sort list of the grades to sort the students according to their grades.

You can create this in two ways.

1. Creating Custom Sort List from Excel Toolbar

1. Select your data set (select the data set with the Column Headers) and go to the Home>Sort & Filter tool in your Excel toolbar under the section Editing.

Sort & Filter Tool in Excel Toolbar

2. Click on Sort & Filter. From the options available, select Custom Sort.

Custom sort from Sort & Filter

3. The Sort dialogue box will open.

In the Sort by menu, under the section Column, select the column according to which you want to sort the data set.

For the sake of this example, select Grade.

Under the Sort On section, select Cell Values.

And under the Order section, select Custom List.

Sort Dialogue Box in Excel

4. The moment you choose Custom List, the Custom Lists dialogue box will open.

Under the Custom lists section, click on NEW LIST.

Custom Lists Dialogue Box in Excel

5. In the List entries section, insert your custom sort list separated by commas(,). For the sake of this example, insert A+, A, A-, B, C, F.

Entering List in Custom Lists Dialogue Box in Excel

6. Then click ADD.

Adding a List in Custom Lists Dialogue Box

You will find your list entered in the Custom lists section.

New List Entered in Custom Lists Section

2. Creating Custom Sort List from Excel File Menu

1. Go to File>Options menu from Excel Toolbar.

File>Options Menu in Excel Toolbar

2. Click on Options. You will get the Excel Options Window. Click on Advanced on the left panel.

Exccel options Window

3. Scroll down through the window. You will find a section called General. Click on the button Edit Custom Lists there.

Edit Custom Lists Button in Excel

4. You will get the Custom Lists dialogue box. Under the Custom lists section, click on NEW LIST.

Custom Lists Dialogue Box in Excel

5. In the List entries section, insert your custom sort list separated by commas(,). For the sake of this example, insert A+, A, A-, B, C, F.

Entering List in Custom Lists Dialogue Box in Excel

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

New List Entered in Custom Lists Section

OR

1. Create the list in a new column in Excel Worksheet, maintaining the order.

Custom Sort List in Excel Worksheet

2. Follow steps 1-3 from the previous section, to open the Custom Lists dialogue box.

Custom Lists Dialogue Box in Excel

3. Click on the Import icon right to the Import list from cells box.

Importing List into Custom Lists Dialogue Box

4. The Custom Lists dialogue box will be compressed like this. Select the sort list from the Excel Worksheet. Then again click on the Import icon.

Importing List into Custom Lists Dialogue Box

5. The Custom Lists dialogue box will be back to the original form. Click on the button Import.

Importing List into Custom Lists Dialogue Box

6. You will find your list entered in the Custom lists section.

Custom Sort List Created in Excel

7. Click on OK to end the process.

You can create your custom sort list by following any of the two processes.

Next to use the list, select your data set (select the data set with the Column Headers) and go to the Home>Sort & Filter tool in your Excel toolbar under the section Editing.

Sort & Filter Tool in Excel Toolbar

Click on Sort & Filter. From the options available, select Custom Sort.

Custom sort from Sort & Filter

The Sort dialogue box will open.

In the Sort by menu, under the section Column, select the column according to which you want to sort the data set.

For the sake of this example, select Grade.

Under the Sort On section, select Cell Values.

And under the Order section, select Custom List.

Sort Dialogue Box in Excel

The moment you choose Custom List, the Custom Lists dialogue box will open.

From the Custom lists section, choose your custom sort list (A+, A, A-, B, C, F in this example).

Custom Lists Dialogue Box

Then click OK. You will be back to the Sort dialogue box. Again click OK.

You will find your data set sorted according to the custom list.

Data Set Sorted According to Custom List

Now if you want, you can again go to the Sort & Filter > Custom Sort options.

And add another level in the Sort panel to sort the students according to their Marks, after the Grades, from largest to smallest.

IAdding Second Level to Sort Panel

Data Set Sorted According to Custom List

You can follow the same procedure for this data set.

Data Set to Create Custom Sort List in Excel

Can create a custom sort list of the column Category: High, Medium, Low.

Custom Lists Dialogue Box in Excel

And then sort the data set according to this list.

Data Set Sorted According to Custom Lists

Conclusion

Using this procedure, you can create a custom sort list in Excel, and then apply it to sort any data set 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