How to Fix Excel Custom List Sorting Values in the Wrong Order
Question details
The user is experiencing an issue where an Excel custom list does not sort or filter values in the expected order.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sorting or filtering a table using a custom list that contains hierarchical numbering formats (e.g., 1, 1.1, 1.1.1).
- Observed behavior
- Excel sorts the values out of order because it interprets some entries as numbers and others as text, ignoring the defined custom list sequence.
Ensure that your custom list is correctly saved in Excel's settings and that the source data you are trying to sort doesn't contain hidden spaces or trailing characters.
Standardize Data Types to Text and Apply Custom Sort
Converting all data to a consistent Text format ensures Excel doesn't split the sorting logic between standard numbers and text strings.
When dealing with values like 1.1 and 1.1.1, Excel automatically treats 1.1 as a decimal number and 1.1.1 as text. This mixed data type prevents the custom list from overriding default sorting behaviors. Forcing all cells to act as text resolves this conflict.
Highlight the column or specific cells containing the values you want to sort.
Go to the 'Data' tab on the ribbon and click 'Text to Columns'. Click 'Next' twice to reach step 3, select 'Text' under the Column data format options, and click 'Finish'.
With your data still selected, click 'Sort' in the Data tab. Choose the column you wish to sort.
Under the 'Order' dropdown, select 'Custom List...'. Choose your specific sequence from the list and click 'OK' to apply the sort.

Verify and Recreate the Custom List
Check if the custom list accurately matches the exact values and format of your source data to ensure it triggers correctly during a sort.
Use WPS Spreadsheet for Seamless Custom Sorting
WPS Office offers a robust Spreadsheet tool that handles custom lists and data sorting efficiently. It is fully compatible with Microsoft Excel formats and provides an intuitive interface for managing mixed data types without hassle.
- 1. Open your document: Launch WPS Spreadsheet and open the file containing the data you want to sort.
- 2. Define your custom list: Go to 'Menu' > 'Options' > 'Custom Lists' to input your specific sorting sequence and click 'Add'.
- 3. Select the data to sort: Highlight the table or specific range of cells that requires custom sorting.
- 4. Apply the sort order: Navigate to the 'Data' tab, click 'Sort', and choose your newly created custom list from the 'Order' dropdown menu.

Frequently Asked Questions
Why does Excel treat numbers like 1.1 as numbers but 1.1.1 as text?
Excel recognizes basic decimals (like 1.1) as standard mathematical values. However, multiple decimal points (like 1.1.1) do not fit standard mathematical numbering, so Excel automatically categorizes them as text strings.
Can I import a custom list directly from a range of cells?
Yes. Go to File > Options > Advanced > Edit Custom Lists. Click the cell reference icon in the 'Import list from cells' box, highlight your desired range on the spreadsheet, and click 'Import'.
Why is my custom list not appearing in the Sort menu?
Ensure that you have correctly added and saved the list in the 'Edit Custom Lists' dialog. Custom lists are saved locally to the device, so if you are working on a new computer or a different user profile, you will need to recreate or import the list.
Does changing the cell format to 'Text' automatically fix the sorting issue?
Simply changing the format dropdown to 'Text' often doesn't update existing data immediately. You must re-enter the data, use the 'Text to Columns' tool, or prepend an apostrophe to force the existing values to act as text.




