Custom List in Excel: Create & Use for Sorting

This guide shows you how to create and use a custom list in Excel for sorting data in a specific, non-alphabetical order. You’ll learn to define your own sequence (like department order or priority levels) and then apply it to sort any range or table, saving time and ensuring consistency.

What You’ll Need

• Microsoft Excel (desktop version recommended; steps are for Excel 2016 and later, including Microsoft 365).
• A list of values you want to sort in a specific order (e.g., “Low, Medium, High”).
• Your data to be sorted should be in a contiguous range or an Excel Table.

Step-by-Step

1. Open Excel and go to the “File” tab. Click “Options” at the bottom of the left sidebar.
2. In the Excel Options dialog, select “Advanced” from the left panel.
3. Scroll down to the “General” section and click the “Edit Custom Lists…” button. This opens the “Custom Lists” dialog.
4. Create your custom list. In the “Custom lists” box on the left, you’ll see built-in lists (like days of the week and months). To add a new one, click “NEW LIST” in that box.
5. Type your list entries in the “List entries” box on the right. Enter each item on its own line. For example:
   
   Low
   
   Medium
   
   High
   
   Critical
6. Click “Add” to move your list to the “Custom lists” box. Then click “OK” to close the dialog, and “OK” again to close Excel Options.
7. Now apply your custom list to sort data. Select any cell within the data range you want to sort. Go to the “Data” tab on the ribbon and click “Sort” (in the Sort & Filter group).
8. In the Sort dialog, choose the column you want to sort by from the “Sort by” dropdown. For “Sort On,” keep “Cell Values.”
9. In the “Order” dropdown, select “Custom List…” at the bottom of the list.
10. In the “Custom Lists” dialog that appears, select your custom list from the left panel (it will be highlighted). Click “OK.”
11. Click “OK” in the Sort dialog. Your data will now be sorted according to your custom list order.

If you want to sort by more than one column, click “Add Level” in the Sort dialog and repeat steps 8–10 for each additional column.

Common Mistakes

• Not selecting the entire data range. If you select only one column, Excel will ask if you want to expand the selection. Always ensure the whole table is selected or that you’re using a Table object.
• Typing entries with extra spaces or different capitalization. The custom list is case-insensitive but space-sensitive. “Low” and “Low ” (with a trailing space) are treated as different items.
• Using a custom list that doesn’t cover all values in your column. Any value not in the custom list will be placed at the end of the sort, in ascending order (or descending if you choose Z to A).
• Forgetting that custom lists are stored in the Windows Registry. They are tied to your user profile, so they won’t automatically appear on another computer.

Tips

• Use Excel Tables. Convert your data range to a Table (Ctrl+T) before sorting. This ensures that filters and sorting apply to the entire table automatically, and custom lists work seamlessly.
• Create a custom list from an existing range. Instead of typing entries manually, you can import them from a worksheet. In the “Custom Lists” dialog, click the icon next to the “Import” field, select the range containing your list, and click “Import.”
• Use custom lists for more than sorting. They also work for auto-fill. Type any item from your custom list, then drag the fill handle to fill the rest in order.
• Keep your custom list short. Excel allows up to 255 characters per entry and up to 255 entries, but a shorter list is easier to manage.
• Edit an existing custom list. In the “Custom Lists” dialog, select the list in the left panel, modify the entries in the right box, and click “Add.” This will replace the old list.

Troubleshooting

• Custom list doesn’t appear in the Order dropdown. Make sure you selected “Custom List…” at the bottom of the Order dropdown, not just the default “A to Z.” If you don’t see it, close and reopen the Sort dialog.
• Sorting doesn’t use the custom order. Check that you selected the correct custom list in the “Custom Lists” dialog. Also, ensure that the column you’re sorting by contains values that exactly match the list entries (no extra spaces).
• Values not in the custom list are sorted incorrectly. Excel places unmatched values at the end. To avoid this, either add all possible values to your custom list or use a formula to map values to a numeric rank and sort by that.
• Custom list works on one computer but not another. As mentioned, custom lists are stored locally. You’ll need to recreate the list on the other machine, or use a workbook-level solution like a helper column with a VLOOKUP to a ranking table.
• Sorting with custom list on a filtered range. If you have filters applied, sorting will only affect visible rows. Clear all filters before sorting to avoid unexpected results.

Frequently Asked Questions

Can I use a custom list to sort by month order (like Jan, Feb, Mar)?
Yes, Excel has built-in custom lists for months and days of the week. You can select them directly in the Order dropdown without creating a new list.

How do I delete a custom list?
Go to File > Options > Advanced > Edit Custom Lists. Select the list you want to delete in the “Custom lists” box, and click “Delete.” You cannot delete built-in lists.

Can I use a custom list in a formula?
Yes, you can use the MATCH function with a custom list by referencing the list as an array constant. For example, to rank a value in cell A1, use: =MATCH(A1,{“Low”,”Medium”,”High”},0). This returns 1, 2, or 3, which you can then sort by.

Does the custom list work with Excel Online or Google Sheets?
Excel Online does not support custom lists directly. Google Sheets has a similar feature called “Custom sort order” but it’s not the same as Excel’s custom lists. In Excel Online, you’ll need to use a helper column approach.

What happens if I sort using a custom list and then filter the data?
The sort order remains intact when you apply filters, but if you re-sort the filtered data, Excel will only sort the visible rows. To maintain the custom order, clear filters first.

Can I share my custom list with a colleague?
Custom lists are stored on your local machine, so you cannot directly share them. However, you can share a workbook that includes the list as a named range and instruct your colleague to import it via File > Options > Advanced > Edit Custom Lists > Import.

Related

• How to Check if Motherboard Supports Windows 11
• Fix iCloud Outlook Error: Setup Couldn’t Be Started
• Fix SSH DLL Errors on Windows: Step-by-Step Guide