Sort a list of values in Excel by defining a custom list through the Options menu or using the Sort dialog’s "Custom List" option. This method ensures specific orders for items like days, months, or industry terms, handling special characters and accented letters correctly without relying on default alphabetical rules.
Understanding Default vs. Custom Sorting
By default, Excel sorts data alphabetically (A-Z) or numerically (Smallest to Largest). While this works for standard text, it fails when you need a specific logical order that does not follow the dictionary sequence. For example, sorting days of the week alphabetically would place "Friday" before "Monday," which is often incorrect for scheduling or reporting purposes. Similarly, sorting months might result in "April" coming before "August" simply because 'A' comes before 'A', but then 'pril' vs 'ugust' creates confusion if not handled properly, or more commonly, it places them in strict ASCII order rather than calendar order. This limitation becomes more pronounced with industry-specific hierarchies, such as priority levels (Low, Medium, High) or product categories. If you have a list of priorities where "High" should come first, default sorting will push it to the end because 'H' is near the middle of the alphabet. To solve this, Excel provides two primary mechanisms: creating a permanent custom list for reuse or applying a one-time custom sort within the Sort dialog. Both methods allow you to define the exact sequence your data should follow. For users dealing with complex datasets involving multiple columns, understanding how these custom lists interact with other sorting criteria is vital. You can read more about managing these lists in Correctly Sorting an Excel Custom List: A Step-by-Step Guide.Method 1: Creating a Permanent Custom List
Creating a custom list allows you to define a sequence once and use it across multiple sheets or workbooks. This is ideal for static lists like days of the week, months, or standard business ranks. First, navigate to the File tab and select Options. In the Excel Options dialog box, click on Advanced in the left-hand pane. Scroll down until you find the section labeled General. At the bottom of this section, there is a button labeled Edit Custom Lists.... Clicking this opens a new dialog box. In the Custom Lists window, you can either import a list from your worksheet or type it manually. To import, click the Import button and select the range of cells containing your desired order in the spreadsheet. Ensure the cells are in the exact order you want them sorted (e.g., cell A1 contains the first item, A2 the second, etc.). If you prefer manual entry, type each item into the List entries box, pressing Enter after each one. Once your list is defined, click Add and then OK. Now, whenever you sort a column containing these items, go to the Data tab, click Sort, and under Order, select Custom List... instead of "Cell Values." Your newly created list will appear in the dropdown, allowing you to apply that specific sequence.Method 2: One-Time Custom Sort via Dialog Box
If you do not want to save the list permanently, you can define the sort order directly within the Sort dialog. This is useful for ad-hoc reports or temporary data organization. Select the range of data you wish to sort, including headers if applicable. Go to the Data tab on the Ribbon and click the Sort button. In the Sort dialog box, ensure your key column is selected in the Column dropdown. Under Sort On, choose Cell Values. Crucially, change the Order dropdown from "A to Z" or "Largest to Smallest" to Custom List.... A small window will pop up showing available custom lists. If you have previously created lists, they will appear here. If not, you may need to create one using Method 1 first, as Excel requires a defined list to reference in this dropdown. Select your desired list and click OK. The data will now be rearranged according to the sequence you specified. Note that this method does not create a new list; it only applies an existing one. For advanced scenarios involving alphanumeric sorting or syncing columns during the process, refer to Advanced Sorting: Alphanumeric Orders and Column Synchronization.Handling Special Characters and Accents
Excel’s sorting engine generally respects Unicode standards, meaning accented characters like "é" or "ñ" are treated distinctly from their base letters. However, the behavior can vary depending on your system's locale settings. In many Western locales, Excel treats accented characters as variants of the base letter for primary sorting, but distinguishes them for secondary sorting. When using custom lists, special characters are included as part of the string value. Therefore, if you include a hyphenated term like "Pre-Alpha" in a custom list, it will be treated exactly as typed. Ensure that your custom list entries match the formatting of your source data precisely, including spaces and punctuation. Mismatches in case sensitivity (e.g., "Item" vs "item") can also cause issues if your data contains mixed cases. It is best practice to standardize your data casing before sorting to avoid unexpected results.Troubleshooting Common Issues
If your custom sort does not seem to work, check several common pitfalls. First, verify that the data type is consistent. Text formatted as numbers, or vice versa, can interfere with sorting logic. Second, ensure that no merged cells exist within the sort range, as this can disrupt the alignment of rows. Third, confirm that the custom list actually contains the exact strings found in your data. If your data has leading or trailing spaces, the sort may fail to recognize the match. Use the TRIM function to clean your data if necessary. Additionally, remember that custom lists are stored per-user profile. If you switch computers or share the workbook, the custom list may not be available unless exported or recreated. For guidance on exporting these settings, consult resources on managing Excel preferences.Frequently Asked Questions
Why doesn't my custom list appear in the Sort dialog?
The custom list must be created in Excel Options before it appears in the Sort dialog's Order dropdown. If you cannot see it, go to File > Options > Advanced > Edit Custom Lists to verify its existence. Ensure you clicked Add after entering the list entries.
Can I sort numbers within a custom list?
Yes, you can include numbers in a custom list, but they are treated as text strings. This means "10" will come before "2" if sorted strictly by character code unless you define the order explicitly in your custom list. Define the sequence manually to control numeric placement.
How do I remove a custom list I no longer need?
Go to File > Options > Advanced > Edit Custom Lists. Select the list from the left pane and click Delete. Confirm the action. This removes the list from your profile, so future sorts will revert to default alphabetical ordering for those items.
Does custom sorting affect the entire row?
Yes, when you sort a range, Excel moves all data in the same row together to maintain data integrity. Ensure your selection includes all relevant columns. If you only select one column, Excel may warn you that the selection does not match the entire range, asking if you want to expand the selection.
What happens if my data contains items not in the custom list?
Items not present in the custom list are typically sorted alphabetically among themselves, usually appearing at the end or beginning depending on the sort direction. Excel prioritizes the custom list order for matching items, leaving unmatched items in a default secondary sort. Check your data for typos that might prevent matches.