Excel Sortable Custom List - Sort by a List that you Create or Select

You will often find yourself wanting to sort a spreadsheet based on something other than alphabetic or numeric order.  Look at the spread sheet below.  What if you wanted to sort this spreadsheet in order by Freshman, Sophmore, Junior and then Senior? You could create a custom list of Freshman, Sophmore, Junior, Senior and then sort on that custom list.

Spreadsheet before Appying Custom Sort

Create an Excel Custom List

To create a Custom List you need to perform the following steps:

  1. Select the data to be sorted.  (In this example that would be the range A1:E9)
  2. Click on the ribbon's Data tab
  3. In the Sort & Filter group click on the Sort button

    Clicking on the Sort Button

  4. From the Order drop down box select Custom List

    Select Custom List from Order drop down box.

  5. Notice that Excel has already created some Custom lists for days of the week and months of the year.
    Enter Freshman Sophmore Junior Senior on separate lines in the List entries list box.
    Click on the Add button. The list you created should now appear in the Custom lists
    Click on the Ok button.

    Excel Custom Lists dialog box

    The classes should now appear in the Order drop down box.

    Classes entered now appear in Order drop down box

  6. Click on the down arrow for the Order drop down box.
    You can decide to sort the classes as you entered them or in reverse order.

    Order Lets you select classes in A to Z or Z to A order

  7. A.  Since the headings in the table are selected you should select My data has headers.
    B.  Select the row header for the column you want to sort by, which in this example is Class.
    C. Select Freshman, Sophmore, Junior, Senior from the Order drop down box.
    D. Click on the OK button.

    Select options on Excel Sort dialog box.

    The spreadsheet is now sorted in Class order.

    Spreadsheet after being sorted by class