Sort by a Given List in Excel: Custom Order Sorting

Formulas & functions · Updated November 20, 2026

To sort Excel data in the order of a list you specify, select the full table, choose Data > Sort, and set the sort order to Custom List. If you want a formula-driven result instead, use SORTBY with XMATCH to rank each row against your ordered list.

What sorting by a given list means

A normal alphabetical or numerical sort follows a built-in order: A to Z, smallest to largest, or oldest to newest. Sorting by a given list means using an order you define, such as Urgent, Standard, Low, rather than alphabetically sorting those labels. It is useful when category names have a business-specific priority or a workflow sequence.

For example, a list of support tickets might contain the priorities Urgent, Standard, and Low. An alphabetical sort would put Low before Standard and Urgent. A custom order can put Urgent first, Standard second, and Low last, while keeping each ticket’s other information on the same row.

There are two practical approaches. Excel’s Custom List setting sorts the existing table in place and is convenient for a one-time or repeat manual sort. A formula can create a separate, automatically updating sorted result, which is useful for reports and dashboards. A helper column is another option when you want to sort manually or need a method that works in more Excel versions.

Sort a table with a custom list

In Microsoft Excel for Microsoft 365 on Windows, use the Sort dialog to apply your chosen order:

  1. Click a cell inside the data, then choose Data > Sort. If Excel asks whether to expand the selection, choose to expand it so all columns in each record move together.
  2. In the Sort dialog, choose the column containing the values you want to order under Sort by. Set Sort On to Cell Values.
  3. Open the Order menu and choose Custom List. Select an existing list, such as the built-in weekday or month order, or enter a new one.
  4. For a new list, type each value on its own line in the List entries box, in the exact order you want, then choose Add and OK. Confirm the sort dialog to sort the table.

For instance, entering Urgent, Standard, and Low on separate lines makes Urgent the first group, Standard the second, and Low the third. The labels need to match the values in your data. Differences such as an extra space or a different spelling can make items fail to match the intended list entry.

Sort the entire table, not just the category column. Sorting one column by itself can separate names, dates, or amounts from the records they belong to. If your data is formatted as an Excel table, click within it before opening Sort; still check that the sort applies to the complete table.

Create or manage a reusable custom list

If you use the same order often, save it as a custom list instead of re-entering it each time. On Windows, go to File > Options > Advanced. In the General section, choose Edit Custom Lists. Enter the items in order, one per line, and choose Add. You can later select the saved list from the Sort dialog’s Order menu.

Custom lists are useful for sequences that are not inherently alphabetical or numerical: sales territories, project stages, academic terms, or priority levels. They can also define a month or weekday sequence that starts at a particular point, such as a reporting year that begins in July. A custom list defines the order of the labels; it does not change the labels in the worksheet.

Keep the list concise and consistent. If a column has a value not included in the custom list, do not assume it will be placed exactly where you intend. Check the result and decide whether to add the missing value to the list or handle it separately. Custom lists are stored in Excel’s settings, so they are not necessarily carried automatically to another person’s computer when you share a workbook.

Sort dynamically with SORTBY and XMATCH

A dynamic-array formula is useful when you want to keep the original data unchanged and show a sorted copy elsewhere. It recalculates when the source values change. This approach requires Excel 2021 or Microsoft 365 for the functions used here; the output spills into neighboring cells, so those cells must be empty.

Assume the category and ticket columns are in A2:B8, with categories in column A and ticket IDs in column B. The requested order is in E2:E4: Urgent, Standard, Low. Enter this formula in an empty cell outside the source data:

=SORTBY(A2:B8,XMATCH(A2:A8,E2:E4),1)

XMATCH returns each category’s position in the specified list, and SORTBY sorts the two-column records by those positions in ascending order. In this example, the result groups the Urgent tickets first, followed by Standard and then Low, while keeping each ticket ID beside its category. The formula returns a sorted view; it does not rearrange the original cells.

If you also want tickets sorted by ID within each priority, add a second sort key. With the same ranges and layout, use:

=SORTBY(A2:B8,XMATCH(A2:A8,E2:E4),1,B2:B8,1)

The first sort key establishes the custom category order. The second sorts ticket IDs in column B in ascending order when categories match. SORTBY accepts additional sort-key and direction pairs, which lets you combine a custom primary order with ordinary alphabetical, numerical, or date sorting.

Handle values missing from the order list

XMATCH returns an error if a category is absent from the custom-order range. That can happen when someone adds a new label to the source data without updating the list. One way to put unlisted categories after the listed ones is to give unmatched values a rank just beyond the end of the list.

With categories in A2:A8 and the order list in E2:E4, enter this version in an empty output area:

=SORTBY(A2:B8,IFNA(XMATCH(A2:A8,E2:E4),ROWS(E2:E4)+1),1)

IFNA replaces a missing XMATCH result with 4, because the order list has three rows. The listed categories sort at ranks 1, 2, and 3; categories not found in the list sort after them. If several categories are unlisted, they share that rank, so add a second sort key if you need a defined order among them. For example, you could use the category text as a further key.

This formula assumes the order list contains no blank entries that should be treated as real categories. Review the output after changing the list, especially if new categories are important: placing them at the end prevents an error but does not decide where they belong in your workflow.

Use a helper column for a manual sort

A helper column makes each row’s position visible and can be useful if you want to sort the original records in place. Assume categories are in A2:A8 and the custom order is in E2:E4. Enter this formula in C2, then fill it down through C8:

=IFERROR(MATCH(A2,$E$2:$E$4,0),ROWS($E$2:$E$4)+1)

MATCH with a final argument of 0 looks for an exact match. The formula returns 1 for Urgent, 2 for Standard, and 3 for Low. An unmatched value receives 4, placing it after the listed categories. Select the whole table and sort by the helper column from smallest to largest. You can hide the helper column afterward if it is not useful to readers.

This method works in older Excel versions that do not have SORTBY or XMATCH, because MATCH, IFERROR, and ROWS are widely available. The formula is copied down row by row, so it does not spill a sorted copy automatically. If you change the order list, the ranks recalculate, but you must run the table sort again to rearrange the source rows.

Choose the right method

Choose the approach based on whether you want to change the source table or produce a separate view:

  • Custom List sort: Best for sorting the existing table through Excel’s interface, especially when you repeat the same manual task.
  • SORTBY and XMATCH: Best for a separate result that updates as the source data changes, provided your Excel version supports dynamic arrays and these functions.
  • Helper column: Best when you want to see each row’s sort rank, sort the source data manually, or use an older Excel version.

These methods do not all behave the same way. A custom list is an instruction for a manual sort; it is not a formula that continuously rearranges the worksheet. A dynamic-array formula displays a calculated result elsewhere, while the helper-column method gives you ranks that you use in a separate sort action.

Common problems and how to fix them

  • Rows no longer line up: The sort may have been applied to only one column. Undo immediately if possible, then select the complete data range and sort again. Convert a regular range to a table if you often add or sort records.
  • A category appears in the wrong place: Check the custom list order and confirm that the spelling in the cells matches the list. Look for leading or trailing spaces and inconsistent labels.
  • The formula returns #N/A: XMATCH did not find a source value in the order range. Add the missing category to the list or use the IFNA version to place unmatched values after the listed ones.
  • The formula says the spill range is blocked: Clear the cells where the result needs to appear. A dynamic-array formula cannot spill over existing content or into merged cells.
  • New rows are not included: A formula using a fixed range such as A2:B8 will not automatically include data added below row 8. Use an Excel table as the data source or update the formula’s ranges when the dataset grows.
  • Items with equal ranks seem unordered: A single sort key does not specify a meaningful order within a category. Add a second key, such as date or ID, when a consistent tie-break is required.

Excel on Mac and Excel for the web

Excel for Mac has a Sort dialog with custom-order sorting, although the exact labels and placement can vary by release. In current Mac versions, custom lists can generally be managed through Excel > Settings (or Preferences) > Custom Lists. If you do not see that path, check the Excel version and its help for the custom-list setting. The Windows path for managing lists is File > Options > Advanced > Edit Custom Lists.

Excel for the web supports ordinary sorts and many dynamic-array functions, but custom-list controls and settings can differ from desktop Excel. If the custom order option is not available in your browser, use desktop Excel for the Custom List sort, or use a rank formula in a helper column and sort by that rank. Sorting the full range remains important in either interface.

Frequently Asked Questions

Can I sort Excel by a list in a specific order?
Yes. Use Data > Sort and choose Custom List from the Order menu, or rank the values with XMATCH and sort the output with SORTBY. Specify the list in the order you want the values to appear.

How do I sort Monday through Sunday instead of alphabetically?
Choose the built-in Monday-to-Sunday custom list in the Sort dialog’s Order menu. If you need a different starting day, select or create a custom list with the desired sequence.

Can Excel sort by a list without changing the original data?
Yes. Put a SORTBY formula in a clear area outside the source data. It returns a separate sorted result, leaving the original rows where they are. The formula requires a version that supports the functions used.

Why does my custom sort put some values at the end?
Those values may not be included in the custom list, or they may be spelled differently from the list entries. Check for extra spaces and inconsistent wording, then add or correct the relevant list item.

Can I sort by two conditions, including a custom order?
Yes. In SORTBY, use the custom-list rank as the first sort key and add another range as a second key. In the Sort dialog, use Add Level to set another sort condition after the custom-list order.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.