How to Create and Sort with a Custom List in Excel for Mac
Question details
The user wants to know how to create a custom list in Excel for Mac to sort data in a specific, non-alphabetical order.
- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Organizing spreadsheet data logically (e.g., by priority, department, or days of the week) rather than using standard alphabetical or numerical sorting.
- Observed behavior
- The user needs the specific navigation steps to set up a custom sorting sequence and apply it to their worksheet data.
Ensure you have your desired sorting order ready (such as a list of specific regions, roles, or priorities) before opening your Excel preferences.
Create a Custom List and Apply Custom Sort in Excel for Mac
Follow these steps to define a custom list in your Mac's Excel preferences and then apply it to sort your worksheet.
The process for creating a custom list differs slightly on a Mac compared to Windows. You must first define the list in the application preferences before it becomes available in the Data sorting menu.
Click on 'Excel' in the top Apple menu bar on your Mac and select 'Preferences' from the dropdown menu.
In the Preferences window, locate the 'Formulas and Lists' section and click on 'Custom Lists'.
Type your custom sort order into the 'List entries' box, pressing Enter after each item. Once finished, click the 'Add' button to save the list.
Return to your worksheet and select the data range you want to sort. Go to the 'Data' tab on the ribbon and click 'Sort'.
In the Sort dialog box, select your column, choose 'Custom List' under the Order dropdown menu, pick your newly created list, and click 'OK'.
Easily Create and Apply Custom Sort Lists in WPS Spreadsheet
WPS Office provides a highly intuitive, lightweight, and free Spreadsheet tool for Mac users. You can seamlessly set up custom lists to sort your data exactly how you want it, matching the familiar workflow of Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office on your Mac and open your spreadsheet document.
- 2. Access Options: Click on the 'Menu' button at the top left, then select 'Options' or 'Preferences'.
- 3. Add a Custom List: Navigate to the 'Custom Lists' tab, type in your desired sorting entries line by line, and click 'Add'.
- 4. Sort Your Data: Highlight the data you wish to organize, go to the 'Data' tab, click 'Sort', and choose your new custom list in the sort order options.
Frequently Asked Questions
Can I import a custom list from existing cells in Excel for Mac?
Yes. In the Custom Lists dialog box, you can click the selection icon next to the 'Import list from cells' box, highlight the range containing your list items in the worksheet, and then click 'Import'.
Why isn't my custom list appearing in the sort order dropdown?
Ensure you have correctly added and saved the custom list in the Excel Preferences. It must appear in the 'Custom lists' box on the left pane of the preferences window before it can be used as a sorting order.
Do custom lists apply to all workbooks on my Mac?
Yes. Once you add a custom list in your Excel for Mac preferences, it is saved to the application's configuration and becomes available for auto-filling and sorting in any workbook you open on that specific device.
