How to Create a Custom Sort Order in Microsoft Lists
Question details
The user needs to sort custom Choice values or numerical data in a specific, business-defined logical order rather than standard alphabetical order in Microsoft Lists.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Organizing issue trackers, task lists, or project management boards that rely on custom priority rankings (e.g., Critical, High, Medium) or status indicators with emojis.
- Observed behavior
- Microsoft Lists forces lexicographical sorting (e.g., 1, 10, 2, 20) for certain numeric lists and cannot natively recognize the logical progression of custom text choices or values containing emojis.
Ensure you have 'Edit' or 'Owner' permissions for the Microsoft List so you can add new columns and modify the current view settings.
Create a Numeric Helper Column
Use a hidden number column to assign a mathematical rank to your custom choices, forcing the list view to sort by logic rather than alphabetically.
This is the most reliable method for sorting non-alphabetical categories, such as 'Critical', 'High', and 'Low', or when you are utilizing emojis in your choice labels.
Open your Microsoft List, scroll to the right of your columns, click on '+ Add column', and choose 'Number'.
Name the column something identifiable like 'Sort Rank' or 'Priority Level', set the decimal places to zero, and click 'Save'.
Edit your list items (using 'Edit in grid view' for speed) and input a number that corresponds to the desired rank of each item (e.g., 1 for Critical, 2 for High).
Click the view dropdown menu in the top right (usually named 'All Items'), select 'Edit current view', scroll down to the 'Sort' section, and choose your new 'Sort Rank' column.
To keep the list visually clean, uncheck the 'Sort Rank' column in the 'Columns' section of the view editor so it drives the sorting but remains invisible to users.
Add Number Prefixes to Choice Values
Modify your existing Choice column options to include number prefixes, aligning alphabetical sorting with your custom logical order.
Create a Custom Sort Order Easily in WPS Spreadsheet
If you frequently work with tabular data and custom business metrics, WPS Spreadsheet offers native custom list sorting. You can arrange your data logically without relying on cumbersome helper columns or numbering workarounds.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing the data you want to sort.
- 2. Select the sort range: Highlight the entire dataset or the specific columns you need to organize.
- 3. Access the Custom Sort menu: Navigate to the 'Data' tab on the top ribbon and click the 'Sort' button to open the sorting dialog box.
- 4. Choose Custom List: Select the column you wish to sort by. Under the 'Order' dropdown menu, select 'Custom List...'.
- 5. Define your sort logic: Type your custom values in the desired order, separated by commas (e.g., Critical, Very High, High), click 'Add', and apply the sort.
Frequently Asked Questions
Why does Microsoft Lists sort numbers sequentially like 1, 10, 2?
This happens when Microsoft Lists or SharePoint interprets a column's data type as text rather than a number. In text-based lexicographical sorting, the system looks at the first character first, placing '10' before '2' because 1 comes before 2.
Can I use emojis in Microsoft Lists Choice columns?
Yes, you can use emojis in choice labels. However, emojis can break standard alphabetical sorting. If you use them, it is highly recommended to implement a hidden numeric helper column to dictate the sort order.
Is there a native Custom List sorting feature in Microsoft Lists?
No, Microsoft Lists does not currently have a built-in Custom Sort mapping feature like desktop spreadsheet applications do. You must rely on alphabetical sorting, numeric sorting, or manual drag-and-drop in specific board views.
