This tutorial demonstrates how to sort with a custom list in Excel.
Sort Data With a Custom List
Say you have the list of names pictured below in Column B and want to sort first by female and then by male names. Do this with a custom list.
Create a Custom List
- Select any cell in Column B (range B2:B9), and in the Ribbon, go to Home > Sort & Filter > Custom Sort.
- In the Sort window, in the Order drop down, choose Custom List… Then click OK.
- In the Custom Lists window, choose NEW LIST as the list.
Type in names in the order you need (e.g., female from A to Z, then male from A to Z). Between each name, press ENTER.
Click Add to add the list.
- This adds your new list to the menu on the left. Click OK to use it in the custom sort.
- This takes you back to the Sort window. The new custom list of names appears in the Order drop down. Click OK to sort the data according to the list.
The result is values in Column B sorted by the custom list you created.
Import a Custom List
Instead of typing custom list items directly into the Custom Lists window, you can 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.
- In the Ribbon, go to File > Options.
- In Excel Options, go to Advanced > General, and then click Edit Custom Lists.
- In the Custom Lists window, click the arrow icon next to Import to select the range for a new custom list.
- Select the range with sorted items for the list (D2:D9), and click the arrow icon to return to the Custom Lists screen.
- Under Custom lists, there is now a new list of names. Click Import, then OK.
- Finally, the list from the Excel workbook is imported and can be used for custom sorting in the file.
Now go to Home > Sort & Filter > Custom Sort and choose the new list in the Order field as shown in the section above.