To sort records in a deliberate order—such as High, Medium, then Low—create an Excel custom list and select it as the sort order. In Windows desktop Excel, you can type the sequence into the Custom Lists dialog or import it from worksheet cells. On Mac, create the list in Excel Preferences. Then use Data > Sort to apply it to the relevant column and its records.
Choose how to create the list
Use direct entry for a short sequence you want to reuse, such as priority levels or product sizes. Import a worksheet range when the ordered values already exist in cells or the list is long. Either method creates a custom order; merely arranging values in a worksheet does not create a reusable custom list.
As an Amazon Associate I earn from qualifying purchases.
Create a custom list in Windows
The following documented path applies to Windows desktop Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Microsoft’s instructions are in Create or delete a custom list for sorting and filling data.
Enter a short list directly
- Open File > Options > Advanced.
- Under General, choose Edit Custom Lists.
- Select NEW LIST, then enter one item per line under List entries, in the order you want Excel to use. For example, enter High, Medium, and Low on separate lines.
- Choose Add, then confirm the dialogs.
Import an existing worksheet range
- Enter the list values in a worksheet column, in the desired order.
- Select the cells containing the values.
- Open File > Options > Advanced > Edit Custom Lists.
- Check that the selected cell range is correct, choose Import, and confirm.
Excel uses the sequence of values in the range as the list order.
Create a custom list on Mac
Microsoft documents this workflow for Microsoft 365 for Mac, Excel 2024 for Mac, and Excel 2021 for Mac. The menu path differs from Windows; see Sort a list of data in Excel for Mac.
- Open Excel > Preferences > Formulas and Lists > Custom Lists.
- Choose Add, enter the ordered values separated by commas, and choose Add again.
- Close the dialog.
Sort records using the custom list
For Windows, Microsoft’s sorting steps are documented in Sort data using a custom list. The Mac instructions use the same sort-dialog choices after the list has been created.
- Select the complete data set you want to sort, including every column that belongs to each record. Give each column a heading so it is easier to identify the sort field.
- Choose Data > Sort.
- In Sort by, select the column containing the values you want to order.
- Under Order, choose Custom List, then select the list you created.
- Confirm the sort.
For example, sorting a table by a Priority column with a High–Medium–Low custom list puts High records first, Medium records next, and Low records last. Excel sorts the selected records according to the selected column’s custom sequence.
Know where custom lists are stored
Excel adds a custom list to the computer so it can be used in other workbooks. A list used for sorting is also saved with that workbook. If the workbook is opened on another computer or server, its saved list is available in the Sort dialog’s Order choices, but it does not automatically appear in that computer’s Custom Lists dialog or become available there for Fill. These storage details are described by Microsoft Support.
Rank #3
Limits and unexpected results
- Lists are based on values, not appearance. A custom list cannot define an order by cell color, font color, or icon.
- Text and mixed entries are supported. Microsoft says a list can contain text or text mixed with numbers. If the list contains only numbers, create its entries as text first.
- Entry constraints apply. Microsoft’s range and table guidance sets a maximum length of 255 characters and says the first character must not begin with a number.
- Check value types on Mac if sorting looks wrong. Values stored inconsistently as numbers and text may not sort as expected; verify that the underlying entries use consistent types.
For exact behavior and current platform guidance, consult the relevant Microsoft Support instructions linked above.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
