In this tutorial, you will learn how to sort with a custom list in Excel.
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.
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.
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.
2. In the Sort window, in the Order drop-down, choose Custom List, and click OK.
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.
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.
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.
As a result, values in Column B are now sorted by the custom list you created.
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.
1. In the Ribbon, go to File > Options.
2. In Excel Options, select Advanced and under General, click Edit Custom Lists.
3. In the Custom Lists window, click the arrow icon next to Import to select the range for a new custom list.
4. Select a range with sorted items for the list (D2:D9), and click the arrow icon to return to the Custom Lists screen.
5. Under Custom lists, there is now a new list of names. Click Import and OK.
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.