How to Use a Custom List for Sorting Data in MS Excel

What’s the first thing that strikes your mind when somebody talks about sorting? I’m sure ascending and descending are two words that ring a bell. Or maybe it’s the ever illustrious Sorting Hat from Harry Potter.

Although those methods are tried and true (the Sorting Hat is never wrong), there are many other ways to sort data. Take, for example, the sample data shown below. It has three columns: Name, Department, and Salary.

My boss asked me to sort the data by Department. His requirement was neither ascending nor descending. Instead, he wanted the data to be sorted in the following order.

Thankfully I use MS Excel for working with data sets. And hence, I could deliver the results with very little effort and in very little time. Here’s how you can use the feature and apply custom sorting on MS Excel.

Cool Tip: Another powerful MS Excel data feature is the Filter option. Read our guide to better understand and learn how to use it.

Step 1: Select the data range that you wish to sort. For me it was the data shown in the sample.

Step 2: Once you have the data selected, head over to the ribbon and switch to the Data tab. Click on Sort.

Step 3: That will launch the Sort dialog window. Drop down the Sort by column (here I selected Department), select Values under Sort On and Custom List for Order.

Step 4: After you click on OK you will be presented with a window named Custom Lists.

It is likely that you do not already have a custom list added if you have never used it before. So add a fresh list, the one that you need, here.

Step 5: Under List entries, type the values you want the custom list to contain. Make sure your list is in the same order in which you want the data to be sorted. Also, each entry should be placed on a new line. When done, click on Add.

Step 6: Your list will now appear under Custom lists. Highlight the list and click on OK.

Step 7: Back at the Sort window, make sure the Order reflects the custom list that you just created. Click on OK.

Step 8: That’s it. Your data is already sorted. Have a look at the result from my sample below.

Cool Tip: We have a cool tip on custom lists that helps you select a random name from a given list. And the trick is not a built-in Excel feature. So don’t forget to take a look!

Conclusion

I still remember a college assignment where we had to write a program using Java. The task was to sort a list of data in a custom order. It turned out to be a big effort. And that’s exactly what makes me realize the value of the custom sorting feature on MS Excel.

Hopefully it helps you to simplify your work with Excel.