Others

How to Fix Excel Custom List Sorting Values in the Wrong Order

Muhammad TalhaMuhammad Talha Oct 29, 2026 870 views

Question details

The user is experiencing an issue where an Excel custom list does not sort or filter values in the expected order.

How to Fix Excel Custom List Sorting Values in the Wrong 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.
Before you start

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.

Solution 1Recommended

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.

1
Select your data range

Highlight the column or specific cells containing the values you want to sort.

2
Convert data to text

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'.

3
Open the Sort dialog

With your data still selected, click 'Sort' in the Data tab. Choose the column you wish to sort.

4
Apply the custom list

Under the 'Order' dropdown, select 'Custom List...'. Choose your specific sequence from the list and click 'OK' to apply the sort.

Standardize Data Types to Text and Apply Custom Sort
Pro Tip: If you only have a few values, you can manually force them into a text format by typing a single apostrophe (') before the number in the cell.
Efficiently Sort Complex Data

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. 1. Open your document: Launch WPS Spreadsheet and open the file containing the data you want to sort.
  2. 2. Define your custom list: Go to 'Menu' > 'Options' > 'Custom Lists' to input your specific sorting sequence and click 'Add'.
  3. 3. Select the data to sort: Highlight the table or specific range of cells that requires custom sorting.
  4. 4. Apply the sort order: Navigate to the 'Data' tab, click 'Sort', and choose your newly created custom list from the 'Order' dropdown menu.
Free to use with a familiar, user-friendly interfaceFully compatible with Microsoft Excel formats (.xlsx, .xls)Easily create, edit, and apply custom sorting listsLightweight application that runs smoothly on all devices
microsoft office alternative - wps office

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.