See all How-To Articles

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.