We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
You’ll want to create a custom sort list in Excel for items that you don’t want to sort in alphabetical or numerical order.
By doing a custom sort list you can specify the order in which your list will be sorted in.
Let’s say you need to sort a list where the items are rated as either High, Medium or Low. The trouble with sorting High, Medium, Low is that it can’t be done alphabetically.
Oddly enough, neither can Small, Medium or Large…
Anyways, to be able to sort this, you’ll need to create a custom list to sort by.
How to Create a Custom Sort List in Excel
There are a couple of ways you can make a custom sort list in Excel.
1. Create a sort list directly from the sheet you’re currently working on
To create a custom sort list from your current worksheet, place your cursor anywhere on the data set you want to sort, go to the Data tab, then the Sort & Filter section, and click on Sort.
Once the pop-up window opens:
- Choose the heading you want to sort by
- Under “Order”, select Custom List
- Then, select New List
- In the “List Entries” field, either type or paste the items you need sorted, in the order that you want them to be sorted
- When done, click Add, then OK
- Click OK again to sort your data using your new custom sort list!
2. Create a sort list in your Excel Options
You can also create a custom sort list from your Excel Options. Here, you have the added flexibility of importing an existing list that’s already in the order you want to sort by.
- From any worksheet, click on the File tab, then on Options
- Once the Excel Options window opens, select Advanced and scroll to the General section
- Click on Edit Custom Lists
Here, you’ll find an option to type in a custom sort list or import an existing list if you have one.
Click OK. Click OK again to exit out of the Excel Options.