To edit a custom sort list in Excel for Microsoft 365 on Windows, open File > Options > Advanced, scroll to General, and select Edit Custom Lists. Choose the list, revise its entries, and select Add to save the changes. You can then apply that order from Data > Sort. Custom lists are useful when a normal alphabetical or numerical sort is not the order your work requires, such as sorting by project stage, department, or workday.
Before you start, make sure you have the exact sequence you want and know whether it is already entered in a worksheet column. Excel saves custom lists for the Excel user on that computer, not inside the workbook, so someone opening the file elsewhere may need to create or import the same list on their computer.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows. Excel for the web does not provide the same dialog for creating and editing custom lists; open the workbook in the desktop app.
- The intended list entries, in their desired order. You can type them in the Custom Lists dialog or import them from a worksheet range.
- A saved copy of your workbook if you plan to sort data. Sorting changes the row order, so confirm that each record is kept together before applying it.
Step-by-Step
-
Open the custom-list settings. In Excel for Windows, select File > Options. In the Excel Options window, choose Advanced. Scroll down to the General section near the bottom of the page and click Edit Custom Lists. The dialog is separate from the worksheet, so your selected cell does not affect which lists appear.
-
Select the list you want to change. In the Custom Lists box on the left, select the existing list. Its items should appear in the List entries box on the right, one entry per line. Take a moment to check that you selected the intended list; Excel also includes built-in lists such as the days of the week and months of the year.
-
Replace or revise the entries. Click in List entries, make the changes, and put each item on its own line in the order Excel should use. For example, a project workflow could be entered as Not started, In progress, Waiting, and Complete. The order here is the sort order, not an alphabetical suggestion. To avoid carrying over an old typo or unwanted item, select and replace the full contents when you intend to rebuild the list rather than changing only one line.
-
Save the edited list. Click Add. This updates the selected custom list using the entries currently shown. Do not close the dialog before clicking Add, or your edits may not be saved. Check that the revised sequence appears in the list on the left. Then click OK to close Custom Lists and click OK again to close Excel Options.
-
Import entries from a worksheet instead, if useful. If the desired order is already in cells, first put one item in each cell in a single column or row. Open the Custom Lists dialog as above, click the Import list from cells box, and select the range with the worksheet selector or type its address. Click Import. Excel adds the range as a custom list in the order of the cells. Verify the entries in List entries before closing the dialog. Import is handy for a long sequence or one maintained in a reference sheet.
-
Apply the custom order to your data. Return to the worksheet and click a cell inside the data range. Select Data > Sort to open the Sort dialog. If the first row contains column headings, confirm that My data has headers is selected. Choose the column containing the values you want to reorder in Sort by, and set Sort On to Cell Values. In the Order menu, choose Custom List.
-
Choose the list and finish the sort. Select the intended sequence in the Custom Lists window that appears and click OK. The selected order should now show in the Sort dialog’s Order field. Review the sort settings, then click OK to reorder the rows. If you need more than one sorting rule, add another level in the Sort dialog, but remember that the custom-list order applies to the column selected for that level.
On a Mac, the custom-list controls are under Excel > Preferences > Custom Lists in many Microsoft 365 releases; some releases label Preferences as Settings. Select or create a list there, make the changes, and use the available add or import control to save it. The worksheet sorting process is still available from the Data tab, though dialog labels and placement can differ slightly by Excel release. Excel for the web does not offer the full desktop custom-list editing dialog, so use Open in Desktop App for these steps.
Common Mistakes
-
Changing the entries but not clicking Add. Editing the text in the List entries box is not enough; click Add before leaving the Custom Lists dialog. Reopen it to confirm that the change stuck.
-
Sorting only one column of a table. If Excel asks whether to expand the selection, choose Expand the selection. Sorting a single column on its own can separate names, dates, and other fields from their original records. If your data is formatted as an Excel table, click within the table and sort from its data.
-
Expecting a list to travel with the workbook. Custom lists are associated with the local Excel installation or user profile. Sending the workbook does not reliably send the sort list with it. Provide the sequence separately or keep it in a worksheet so another person can import it.
-
Trying to edit a built-in list. Excel’s standard day and month lists are built in and cannot be modified or deleted in the same way as a user-created custom list. Create a separate custom list for a different sequence, such as fiscal periods or abbreviated department names.
-
Using entries that do not match the data. A custom order is most useful when the column values correspond to the list items. Check spelling, spaces, punctuation, and abbreviations. If the cells use In progress but the list says In-Progress, standardize the text before judging the sort result.
Tips
-
Keep the list short, clear, and consistent. Use one spelling for each category in both the custom list and the worksheet. Consistent labels reduce ambiguity when you review the sorted result.
-
Keep a source copy of important lists in a reference worksheet. It is easier to review and share a visible column than to rely on a list stored only in one person’s Excel settings. You can import that column again if needed.
-
Test a new or revised order on a copy of the data, especially when sorting a large table or a report used by others. Confirm the first few and last few categories, and check that all columns stayed aligned.
-
For recurring sorts, save the custom list once and use it whenever the same category sequence is needed. You do not need to rebuild the list each time; choose it again through the Sort dialog’s Order menu.
-
If you maintain several lists, give the source columns clear headings and keep each sequence in a separate range. That makes the correct range easier to select when importing and helps prevent adding a header as an unintended list item.
Troubleshooting
The revised list does not appear in the sort options: Reopen File > Options > Advanced > Edit Custom Lists and check whether you clicked Add. If it is missing, enter or import it again, save it, and reopen the Sort dialog. Also confirm that you are working in the desktop version of Excel rather than Excel for the web.
The sort looks alphabetical or otherwise wrong: Open Data > Sort and inspect the Order field. It must show the custom sequence you intended, not A to Z or Z to A. Check that the selected Sort by column is correct and that the text in the cells corresponds to the list. Values absent from the custom list may not fall into the category order you expect, so inspect those values and standardize them if necessary.
Some rows moved but their details no longer match: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the workbook has not been changed in a way that prevents undo. Then select the full data range and sort again, choosing Expand the selection if prompted. For a table, sort from within the table rather than selecting one column alone.
Import does not bring in the expected entries: Check that the selected range contains only the intended sequence, with no blank cells or heading included by mistake. Make sure the range is on the active workbook sheet and is selected in the Import list from cells field before clicking Import. If the range is large, verify the imported entries in the dialog rather than assuming the correct cells were picked.
The list is unavailable on another computer: This is expected when the list was created only in your Excel settings. On the other computer, use the desktop app to import the list from a shared worksheet range or enter it manually. Saving and reopening the workbook alone does not transfer the local custom-list settings.
Frequently Asked Questions
Can I change the order of an existing custom list?
Yes. Select the list in the Custom Lists dialog, arrange the entries in the List entries box in the order you want, and click Add to save the revised sequence.
Can I remove a custom list?
Yes. Select the user-created list in the Custom Lists dialog and click Delete, then confirm if Excel asks. This removes the list from that Excel installation; it does not delete worksheet data. Built-in month and weekday lists are not removable this way.
Does a custom list change the order of the cells in my worksheet automatically?
No. Editing or adding a list only sets an available ordering. Apply it to a range using Data > Sort and choose that list from the Order menu.
Will my custom list be included when I email the workbook?
Do not assume so. Custom lists are stored with Excel’s local settings, not as a portable part of the workbook. Include the sequence in a worksheet or share the entries separately so the recipient can import it.
Can I edit custom lists in Excel for the web?
The web app does not provide the full custom-list management dialog described here. Open the file in Excel for Microsoft 365 desktop on Windows or Mac to create, edit, import, or delete a custom list.
Can I use a custom list for more than one sort level?
Yes. In the Sort dialog, add a level and select the column for that level. Set its Order to the desired custom list. Use this when a primary sort groups records and a secondary custom order sorts items within those groups.
Related
- How to Delete Only Visible Rows in Excel Without Hidden Data
- Delete Only Filtered Rows in Excel Without Hidden Data
- How to Delete Multiple Tabs in Excel at Once
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- Variance Formula in Excel: Calculate Percentage VarianceTo calculate percentage variance in Excel, subtract the reference value from the actual value and divide by the…
- Excel Formula to List Only Weekdays in a Date RangeWith actual Excel dates in A2:A100, use =FILTER(A2:A100,IFERROR(WEEKDAY(A2:A100,2)<=5,FALSE),"No weekdays") to return only Monday-to-Friday dates from that list. If…
- Compare Two Dates in Excel: Formulas and FormattingTo compare two dates in Excel, enter this formula in a result cell: =IF(A2<B2,"A is earlier",IF(A2>B2,"A is later","Same…
- Copy Formulas in Excel Across Rows: A Simple GuideTo copy a formula horizontally, select its cell and drag the small fill handle at the lower-right corner…
- Filter Data Between Two Dates in Excel: A Practical GuideTo filter data between two dates in Excel for Microsoft 365 on Windows, select a cell in your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.