How to Use a Cus­tom List for Sort­ing Data in MS Excel

Sandeep Agarwal

Sorting Feature
Image via Flickr.

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.

Bdk6C
Original Image via Flickr.

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.

Sample Sorting Data

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.

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

Data Sorting

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.

Order By Custom List

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

Define Custom List 1

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.

Define Custom List 2

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

Define Custom List 3

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

Finalize Sorting Order

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

Sorted Data

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.


The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.

Also See
#msexcel #How-to/Guides