How to Make a Custom Sorting List in Excel

To make a custom sorting list in Excel, enter the order you want in the Custom Lists settings, then apply it from the Sort dialog. For example, you can sort project stages as Planning, In Progress, and Complete instead of alphabetically. You can type the order directly or import it from worksheet cells. The steps below are for Excel for Microsoft 365 on Windows; custom lists are an Excel desktop feature, so use the Windows or Mac desktop app to create one.

What You’ll Need

Excel for Microsoft 365 desktop on Windows, or the desktop app on Mac. The Excel for the web interface does not provide the same controls for creating custom lists.
A short, ordered set of entries, such as department names, priority labels, or workflow stages. Decide the exact spelling and order before you add it.
A worksheet with a table or contiguous data range to sort. Include a clear heading for the column that contains the entries.

A custom list is not the same as a list of values you sort alphabetically. It tells Excel which sequence to use for matching entries. Your worksheet values should match the custom-list entries, including spaces and punctuation, for the intended order to apply.

Step-by-Step

Prepare your worksheet data. Put each record on one row and use a header for every column, such as Task, Owner, and Status. Check that the column you plan to sort contains consistent text: avoid trailing spaces, accidental spelling variations, or a mix of labels such as In progress and In Progress. Keep the data in a continuous block, with no completely blank row or column dividing it. If your data is already an Excel table, click any cell inside it. Otherwise, select the full range, including headers, and press Ctrl+T on Windows or Command+T on Mac. Confirm that My table has headers is selected, then choose OK. A table makes it easier to sort records together as the data grows.
Open the Custom Lists settings on Windows. Choose File > Options, select Advanced, and scroll down to the General section. Click Edit Custom Lists. This dialog contains Excel’s built-in lists, such as days of the week, as well as lists you add. If you are using Excel for Mac, open Excel > Settings (or Preferences, depending on the release) > Custom Lists. The labels and layout can differ slightly, but the task is the same: add or import the ordered entries.
Choose whether to type or import the list. To type it, select New List in the Custom Lists dialog, click in the List entries box, and enter one item per line in the order Excel should use. For example, enter Planning, then In Progress, then Complete, each on a separate line. Click Add to save the new list. To import existing worksheet entries instead, put the desired order in a single column, select that range in the Import list from cells box, and click Import. Use a simple range containing just the entries, not a heading or unrelated cells. Importing is useful when the order is long or already maintained in the workbook.
Confirm that the list was added. Look for your sequence in the Custom Lists pane. Select it and check that the entries appear in the intended order. Click OK to close the Custom Lists dialog, then click OK again to close Excel Options if you are on Windows. If the dialog rejects an entry or the list does not appear, check that you clicked Add or Import; typing entries alone does not save them. On Mac, close Settings after confirming the list appears.
Open the Sort dialog for your data. Return to the worksheet and click a cell inside the table or data range. Go to Data > Sort. If Excel asks whether to expand the selection, choose Expand the selection so that information in other columns stays with its row. In the Sort dialog, make sure My data has headers is selected if the first row contains column names. Under Column, choose the heading whose values should follow your custom order, such as Status. Keep Sort On set to Values.
Apply the custom order. Open the Order menu and choose Custom List. Select the list you added, then choose OK to return to the Sort dialog. Check that the chosen list is shown as the sort order. Choose OK to sort. Excel rearranges the rows according to the sequence you created, rather than alphabetically. If the sort dialog includes other sort levels, check that the custom-order column is the primary level unless you intentionally want another column to take precedence.
Check the result before continuing. Confirm that the custom-list values appear in the expected order and that each row’s related details, such as owner and due date, moved with it. Look closely at entries that are blank or are not in your custom list; do not assume they were placed where you want them. If the result is not right, press Ctrl+Z on Windows or Command+Z on Mac immediately to undo the sort, then check the selected range and list entries before trying again.
Reuse or revise the list when needed. You can apply the same custom list to another suitable column by repeating Data > Sort and selecting it from Custom List. To change the list, return to Edit Custom Lists on Windows or Custom Lists in Mac Settings, select the entry, edit or remove it as appropriate, and confirm. Custom lists are stored in Excel’s settings rather than as a portable part of the worksheet, so another person or computer may not have your added list. If a workbook depends on a particular order, document the sequence in the workbook and check the custom-list settings on any computer where it will be sorted.

Common Mistakes

Sorting only one column. Selecting just the Status cells and sorting them can disconnect statuses from their owners or other row details. Start inside an Excel table or select the complete data range, and choose Expand the selection if Excel prompts you.
Forgetting to save the typed list. Entering items in the List entries box is not enough; click Add. If you import from cells, click Import. Confirm the list appears in the saved custom lists before closing the dialog.
Including a heading in the sequence. If you import a range that starts with a column heading, Excel may treat that heading as an item. Select only the ordered values, or type the items without a heading.
Using inconsistent labels. Custom sorting depends on matching the worksheet entries to the list. A trailing space or different spelling can stop a value from matching as expected. Clean up the source values or edit the custom list so the labels agree.
Leaving the wrong sort level first. When a sort has multiple levels, Excel sorts by the first level, then uses later levels to break ties. Put the custom-order field first if it should determine the overall grouping.
Expecting the custom list to travel with the file. The list is an Excel setting, not a normal worksheet range embedded as sorting instructions. A colleague may need to add the same sequence in their own desktop Excel settings.

Tips

Put the sequence in a dedicated worksheet column and treat it as the master reference. That makes the intended order easy to review, reuse, and import again if needed.
Use short, distinct labels. For example, use High, Medium, and Low consistently rather than mixing High priority with High or using abbreviations in some rows.
Test the sort on a copy of the worksheet if the data is important or the range is complex. This gives you a way to compare the original row order with the sorted result.
Use filters or table header arrows for quick everyday sorts, but use Data > Sort when you need a custom list or multiple sort levels. The full dialog makes the chosen column and order easier to inspect.
For a hierarchy such as Region, then Priority, add the custom-list field as one sort level and the secondary field as another. Set the hierarchy deliberately: the first level groups the data, and the next level sorts within those groups.
If you need to share the intended order, include a visible reference list in the workbook and tell collaborators to add it to their Custom Lists settings before sorting. This avoids relying on a setting that may exist only on your computer.

Troubleshooting

The custom order is missing from the Order menu: Reopen Custom Lists and confirm that the sequence was added, not just typed. On Windows, check File > Options > Advanced > General > Edit Custom Lists. On Mac, check Excel > Settings > Custom Lists. If you are using Excel for the web, open the workbook in the desktop app to create or manage the list.

Values are still in alphabetical order: Return to Data > Sort, choose the correct column, and select Custom List from Order. Verify that the intended list is selected and that the sort is not being controlled first by a different sort level. A regular A to Z sort does not use your custom sequence.

Some entries do not land where expected: Compare the worksheet text with the list character by character. Look for different punctuation, extra spaces, singular versus plural forms, and spelling differences. Correct the source values or update the list, then sort again. Also inspect blanks and any values that are not included in the custom list; decide how you want to handle them rather than assuming they follow the sequence.

Other columns no longer match the sorted values: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Then sort the whole table or full data range, not just the one column. If the range has blank rows or columns, select the complete range manually before using Data > Sort.

The list works on your computer but not a colleague’s: The custom list is held in local Excel settings. Share the sequence and have the colleague add it to their desktop app, or sort the data on the computer where the list is available. Keeping a visible copy of the order in the workbook makes it easier to recreate.

Frequently Asked Questions

Can I create a custom sort list from cells?

Yes. Put the values in the exact order you want, select only those cells in the Import list from cells field in Custom Lists, and click Import. You can then choose that list in Data > Sort.

Can I sort by more than one column and still use a custom list?

Yes. In Data > Sort, set the custom-order column as one level and add another level for a secondary sort, such as Owner or Date. Put the most important grouping level first.

Does the custom list change the values in my worksheet?

No. It changes the order in which Excel arranges matching rows. It does not rewrite the cell values or add list entries to the worksheet.

Will my custom list be included when I send someone the workbook?

Usually, no. Custom lists are saved in Excel settings on the computer rather than as a standard part of the workbook. Share the sequence separately or include it in a reference column so the recipient can recreate it.

Can I use a custom sorting list in Excel for the web?

The web app does not offer the same controls to create and manage desktop custom lists. Use Excel for Microsoft 365 on Windows or Mac to add the list and perform the custom sort; basic sorting options in the web app may differ.

How do I remove a custom list?

Open Edit Custom Lists on Windows or Excel > Settings > Custom Lists on Mac, select the list, and choose Delete or Remove if available. Built-in lists may not be removable. Removing a custom list from settings does not delete worksheet values.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.