How to Sort Data in Excel Using Custom List

In this article, you will learn how to sort data in Excel using the custom or user defined list.

Custom lists allow you to sort data according to your specific criteria, rather than being limited to alphabetical or numerical order. This is particularly useful when dealing with non-standard sequences. By using custom lists, you can save time that would otherwise be spent manually rearranging data.

Example

Let’s look into an example. Suppose that, in an Excel sheet, you have student name and mark obtained details in a particular examination. If you need to sort the data by student name column in the order of girl’s name first, then boy’s, here comes the use of custom sort.

In the example above, in the range A2:B13, there is data of student name and mark obtained. requirement here is to sort the data by girl’s name first then boys, which is a custom order.

Let’s learn how to do this. Custom sorting basically involves two steps.

1. Add the custom sorting list to custom list of Excel.
2. Sort the data.

Add Sort Order to Custom List

Follow these steps to add the sorting order to custom lists.

1. Make the custom sort order list somewhere in your Excel sheet. For example, F2:F13 range in above example.
2. Click on File tab of Excel, then Options.
3. In the side menu, click on Advanced.
4. Drag the scrollbar down.
5. Click on Edit Custom List button.
6. Click on import list from cells box.
7. Select the range where your custom list is. (F2:F13 in above example)
8. Click on Import button.
9. Click on ok or close the custom list window and Excel options window.

After completing above steps, the list will be added in custom lists.

Sort the Data

After adding the list in Excel, to sort the data, follow below steps.

1. Select the data to sort. A1:B13 range in above example.
2. Click on Data tab, then Sort.
3. In the sort by, select student name column. In the sort on, select cell values. In the order, select custom lists.
4. Select the custom order list that you just created byclicking on it.
5. Click on the ok button of both windows.

The data is now sorted in custom order i.e. girl’s name first then boys.

So, this is how we sort data by custom list in Microsoft Excel. If this article is useful, request you to share this to Excel learners. Thank You.