How to Sort With a Custom List in Excel

In this tutorial, you will learn how to sort with a custom list in Excel.

custom list add 2

Sort Data With a Custom List

In Excel, you have the option to sort data by custom lists. There are standard lists – like days and months – but you can also create a custom list for your data.

excel standard custom lists

Say you have the list of names pictured below in Column B and want to sort first by female and then by male names. To do this, use a custom list.

custom sort list 1

Create a Custom List

1. Select any cell in Column B (range B2:B9), and in the Ribbon, go to Home > Sort & Filter > Custom Sort.

custom sort list 2

2. In the Sort window, in the Order drop-down, choose Custom List, and click OK.

custom sort list 3

3. In the Custom Lists window, (1) select NEW LIST in the Custom lists menu, and (2) enter names in the order you need (first female from A to Z, then male from A to Z). (3) Click Add to add the list.

custom list add

4. As a result, the new list is added to the menu on the left side. Click OK to use it in the custom sort.

custom list add 2

5. This takes you back to the Sort window. The new custom list of names is selected in the Order drop-down list. Click OK to sort the data according to the list.

custom list sort

As a result, values in Column B are now sorted by the custom list you created.

custom sort list 5

Import a Custom List

Instead of typing custom list items directly into the Custom list window, you could also import a list from a range of cells in the workbook. Say you have the custom list sorted in Column D (range D2:D9) shown below and want to import it and use it for a custom sort.

custom sort list 6

1. In the Ribbon, go to File > Options.

import custom list 2

2. In Excel Options, select Advanced and under General, click Edit Custom Lists.

import custom list 3

3. In the Custom Lists window, click the arrow icon next to Import to select the range for a new custom list.

import custom list 4

4. Select a range with sorted items for the list (D2:D9), and click the arrow icon to return to the Custom Lists screen.

custom sort list 7

5. Under Custom lists, there is now a new list of names. Click Import and OK.

import custom list 6

Finally, the list from the Excel workbook is imported and can be used for custom sorting in the file.

6. Now go to Home > Sort & Filter > Custom Sort and select the new list in the Order field as shown in the section above.