Excel: How to Sort Data Using a Custom Sort Order

If you have some data in your spread sheet you can use the sort function to sort this data in either ascending order or descending order very easily.

Let’s say you have a list of 100 books under three categories - Fiction, Management, and Self-help. You can sort the books alphabetically by each category. Excel will provide you the option to sort the data in ascending order (A to Z) or descending order (Z to A). So, you can have books sorted as Fiction, Management, and then Self-help books, or you can have books sorted as Self-help, management, and Fiction books.

What if you want to sort them in a custom order? You want all management books to display first, fiction books after that and self-help books in the end.

To do so you can use the custom list facility available in excel.  Follow the following steps to sort your data in custom order.

Step 1: Place your cursor in any cell in the list that you want to sort.

Step 2: Select Data -> Sort. This will bring up the sort dialog box.

Step 3: From the ‘Sort By’ dropdown list, select the column that contains the books category.

Step 4: Leave the ‘Sort On’ dropdown list to the default (Values).

Step 5: From the ‘Order’ dropdown list, select ‘Custom List’. Another dialog box will appear where you will be asked to enter your custom list. Enter the values Management, Fiction and then Self-help. Press the ENTER key after entering each value. After entering all the values, click ‘Add’. The new list will now be added. Select this list, and click OK.

Your data is now sorted in the order of your custom list.