How to Create Custom Sort in Excel (Both Creating and Using)

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

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 fix this problem.

Table of Contents hide

Download Practice Workbook

Creating a Custom Sort List in Excel

1. Writing Directly in the Custom Lists Dialogue Box

2. Importing a List from the Workbook

Using the Custom Sort List in Excel

Conclusion

Download Practice Workbook

How to Create and Use Custom Sort in Excel.xlsx

Creating a Custom Sort List in Excel

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

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

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

Click on Advanced from the left pane.

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

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.

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.

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

2. Importing a List from the Workbook

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

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

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

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.

Again click on the Import icon right to the box.

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

You will find your list added to the Custom lists.

Click on OK to finish the process.

Using the Custom Sort List in Excel

Now we have finished creating the custom sorting list.

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

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.

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

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.

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.