To add a custom sort list in Excel for Microsoft 365 on Windows, go to File > Options > Advanced, find the General section, and click Edit Custom Lists. Enter the items in the order you want, click Add, and then use Data > Sort and choose that list under Order to sort your data by the custom sequence.
What a custom sort list does
A custom list tells Excel that certain text values belong in a particular sequence rather than ordinary alphabetical order. For example, a status column might need to read New, In Progress, Waiting, Complete. An A-to-Z sort would put Complete first, but a custom list can place each status in the order that matches your workflow.
Custom lists are also useful for recurring categories that have a meaningful sequence but are not naturally alphabetical: sales territories, production stages, priority levels, or department names. Excel already includes common lists such as the days of the week and months of the year, so you usually do not need to create those yourself. A custom list is especially helpful when the same order is used across many workbooks.
A custom list can also guide AutoFill. For example, entering the first item of a recognized list and dragging the fill handle can continue the sequence. The list is not a new worksheet or a data-validation menu; it is an ordering rule Excel can use when sorting and filling cells.
Prepare the list and your data
Before adding a list, decide on the exact text and sequence you want Excel to recognize. The entries should match the values in the column you plan to sort. If your cells contain In progress but your custom list says In Progress, Excel generally matches text without regard to capitalization, but extra spaces or different wording can prevent the expected match.
- Write one category per cell in a single, continuous column if you plan to import the list from a worksheet.
- Remove blank cells from the middle of the proposed list so the intended sequence is clear.
- Check for spelling differences, trailing spaces, abbreviations, and duplicate labels in the data.
- Keep a header row in your data table, such as Status or Department, so Excel can identify the field being sorted.
For example, if a worksheet has the statuses New, In Progress, Waiting, and Complete in different rows, create the custom list in that intended order. You do not need to rearrange the records first. Excel will use the list as a ranking when you apply the sort.
Add a custom list in Excel for Windows
You can type the sequence directly in the Custom Lists dialog or import it from cells. Importing is convenient when the entries already exist in the workbook; typing is quick for a short list.
- Open Excel and select File > Options.
- In the Excel Options window, choose Advanced.
- Scroll to the General section and click Edit Custom Lists.
- To type the sequence, click the List entries box, enter one item per line, and keep the entries in the desired order. Click Add.
- To import entries instead, select the worksheet range containing the list, choose that range in the Import list from cells box, and click Import.
- Click OK in the Custom Lists dialog, then click OK again to close Excel Options.
For example, typing New, In Progress, Waiting, and Complete on separate lines and clicking Add creates that sequence. If your list is already in cells D2:D5, select or enter that range in the import box and use Import. Check the list shown in the dialog before closing it; importing the wrong range can create an incomplete or unintended order.
Sort a worksheet using the custom list
After adding the sequence, apply it to the full set of records so the values in each row stay together. Sorting only the status cells without their related names, dates, or amounts can separate information that belongs to the same record.
- Click a cell inside the data range or Excel table.
- Choose Data > Sort. If Excel asks whether to expand the selection, choose the option that includes the adjacent data.
- In the Sort dialog, check that My data has headers is selected if the first row contains column names.
- Under Sort by, choose the relevant column, such as Status. Set Sort On to Values.
- Open the Order menu and choose Custom List. Select the sequence you added, then click OK to confirm the custom list and again to run the sort.
If the table needs a second sorting rule, add another level in the Sort dialog, such as sorting statuses in the custom order and then sorting each status group by due date. The custom order controls the selected column; the other sort levels break ties within that order. Verify the result by checking that the category groups appear in the sequence you intended and that each complete row remains intact.
Examples of useful custom orders
Custom sorting can express a process or priority that alphabetic order cannot. A project tracker might use Planned, Active, On Hold, Finished. A support queue could use Critical, High, Normal, Low. A regional report might place territories in a presentation order that matches a company’s reporting structure rather than the alphabet.
Choose labels that reflect the actual values in the column. If one row says High priority and another says High, a list containing only High may not place both as expected. Likewise, a custom order cannot infer that several different spellings mean the same category. Standardizing the source labels first makes sorting more reliable and improves filtering, formulas, and summaries too.
For common calendar sequences, use Excel’s built-in month or weekday lists when they meet your needs. A custom list is worthwhile when the order is specific to your work or when you want a nonstandard sequence, such as a fiscal reporting period that begins in a month other than January. Dates stored as real Excel dates can often be grouped or sorted by date directly; a custom text list is mainly useful when the cells contain labels rather than date values.
Where Excel keeps custom lists and version differences
In desktop Excel, custom lists are generally a setting for the Excel installation or user profile, not content stored inside the workbook as a portable rule. The sorted row order is saved in the workbook, but someone opening the file on another computer may not have the same custom list available for a later re-sort. If a workbook will be shared, tell collaborators the intended order or include a helper ranking column in the workbook.
On Excel for Mac, the available controls depend on the release. Look for Excel > Settings or Excel > Preferences > Custom Lists; the wording varies between versions. Add or import the list there, then use Data > Sort and the custom-list order if the option is available in that release. Windows keyboard shortcuts are not always the same on Mac; these steps use ribbon menus and do not depend on a shortcut.
Excel for the web has fewer controls for managing desktop custom lists, and the custom-list setup described above is a desktop Excel feature. If you cannot create or choose the desired list in the browser, open the workbook in the desktop app. For a workbook that must sort consistently in the browser or across different computers, a helper column with explicit numeric ranks is a more portable approach.
Common problems and how to fix them
- The custom order does not appear: Confirm that you clicked Add or Import in the Custom Lists dialog. Reopen the dialog and check that the list is present. If it is not, add it again and close the dialogs with OK.
- Some values appear at the end: Those cells may not exactly match a list item, or they may contain leading or trailing spaces. Correct the labels or clean the source values, then repeat the sort.
- Rows no longer match their original details: Undo immediately with Ctrl+Z on Windows, or Command+Z on Mac, if the sort is the most recent action. Then select a cell in the data and sort the entire range or table, not a single column by itself.
- The sort looks alphabetical: Reopen Data > Sort and inspect the Order setting. Choose Custom List and select the intended sequence rather than A to Z or Z to A.
- The list is missing on another computer: The custom-list setting may not travel with the workbook. Add it on that computer or use a ranking column stored in the workbook.
When sorting a normal range, the prompt to expand the selection is important: expanding keeps adjacent fields aligned with each record. Excel tables usually make it easier to work with a complete set of fields, but it is still sensible to inspect the sort range and header setting before confirming a sort.
Alternative: sort with a helper rank column
A helper column is a practical alternative when the file is shared, when desktop custom lists are unavailable, or when you want the order visible in the workbook. Put the preferred sequence in a separate range and assign each category a number. Sort the records by that number from smallest to largest; the explicit ranking travels with the workbook.
With statuses in A2:A5 and the desired order in H2:H5, enter =MATCH(A2,$H$2:$H$5,0) in B2 and fill it down. If H2:H5 contains New, In Progress, Waiting, Complete in that order and A2 contains Waiting, the formula returns 3. Sort the full data range by column B in ascending order. The exact-match argument 0 makes MATCH look for the category as written; a value missing from the order range returns an error, which can reveal an unlisted status that needs attention.
You can hide the helper column after sorting if readers do not need to see it, but keeping it visible can make the ranking easier to audit. Unlike a custom list stored in desktop settings, the category sequence is visible in the workbook, although the formula range must remain intact for the ranks to recalculate correctly.
Frequently Asked Questions
Can I sort by a list that is not alphabetical?
Yes. Add the categories in the order you want under Edit Custom Lists, then select that custom list in the Order menu of the Sort dialog.
Can I use a custom sort list with more than one column?
Yes. Add a custom-list sort level for the category column, then add another level for a field such as date or name. Excel uses the additional level to order records within matching categories.
Does a custom list change the values in my cells?
No. It changes the order in which Excel arranges the rows; it does not rewrite the category text. Sorting can be undone with Ctrl+Z on Windows or Command+Z on Mac if it is the latest action.
Why does my custom list work on one computer but not another?
Desktop custom lists are not generally embedded as reusable settings in the workbook. Add the list on the other computer or use a helper ranking column that is part of the file.
Can I create a custom sort list in Excel for the web?
The desktop custom-list management steps are not available in the same way in Excel for the web. Open the file in desktop Excel to manage the list, or use a helper rank column for a portable sort.
Related
- Access Database vs Excel Spreadsheet: Which to Use?
- How to Protect Cells in Excel and Control Editing Safely
- Excel Merge Zones: 11 Ways to Combine Cells and Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Extract Text in an Excel Cell: Formulas and Steps That WorkTo extract part of a cell’s text, use a formula such as TEXTBEFORE for text before a separator,…
- Excel Formula for Grouping Data by CategoryIn Excel for Microsoft 365, use UNIQUE to list each category once and FILTER to return the records…
- Freeze Values in Excel: Turn Formulas Into Fixed ResultsTo freeze values in Excel, select the formula cells, copy them, then choose Home > Paste > Paste…
- HLOOKUP in Excel: Purpose, Examples, and LimitsHLOOKUP in Excel searches for a value across the top row of a table and returns a related…
- Vlookup with multiple criteria?Learn how to use VLOOKUP with multiple criteria in Excel, build a reliable helper key, avoid common errors,…
- XLOOKUP Example With IFERROR: Excel Formula and TipsAn XLOOKUP formula wrapped in IFERROR can show a useful message instead of an Excel error: with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.