Custom Sort in Excel: Sort by Your Own Order

A custom sort in Excel puts rows in an order you choose, such as High, Medium, Low, or Small, Medium, Large, instead of alphabetical order. You set it up once in the Sort dialog with a custom list, or you write it as a formula with SORTBY and MATCH. This guide shows both, plus the built-in lists for days and months.

Why A to Z isn't always the right order#

Alphabetical sorting only looks at letters. That works for names, but not for words with a meaning order. Sort this ticket list A to Z by Priority and you get High, High, High, Low, Low, Low, Medium, Medium. Low ends up before Medium, which no manager wants.

The examples use this list in A1:C9:

Other common cases: sizes (S, M, L, XL), stages (Lead, Proposal, Won), and departments in the order your company lists them. If you need the basics of sorting first, see the guide to sorting data in Excel.

How to do a custom sort with a custom list#

1. Click any cell inside the data.
2. Go to Data > Sort. Make sure My data has headers is ticked.
3. In Sort by, choose Priority. Leave Sort On as Cell Values.
4. Open the Order drop-down and choose Custom List.
5. With NEW LIST selected, type the entries in the List entries box, one per line: High, Medium, Low.
6. Click Add, then OK. The Order box now shows "High, Medium, Low".
7. Click OK to sort.

The tickets now run T-02, T-04, T-07 (High), then T-03, T-06 (Medium), then T-01, T-05, T-08 (Low). Rows with the same priority keep their original order.

To sort the other way, open Order again and choose Low, Medium, High, which Excel offers automatically once your list exists.

Built-in custom lists: days and months#

Excel already knows four lists, so you don't need to create them:

Choose one under Order > Custom List when a column holds month or day names as text. If the column holds real dates instead, a normal Oldest to Newest sort is enough. The same lists power AutoFill: type "Mon" in a cell and drag the fill handle to get Tue, Wed and so on.

How to save a custom list for every workbook#

Lists you add in the Sort dialog are saved on your computer, so you can reuse them in any file. To manage them directly:

• Windows: File > Options > Advanced, scroll to General and click Edit Custom Lists.
• Mac: Excel > Preferences > Custom Lists.

Here you can type a new list, delete one, or import a list from cells. Select a range such as E2:E4 that already holds High, Medium, Low, and click Import. Note that custom lists are stored on your computer, not in the workbook, so a colleague opening the file won't have them.

Custom sort with more than one level#

A custom order can be one level of a bigger sort. To sort by priority, then by owner A to Z within each priority:

1. Set up the Priority level with your custom list, as above.
2. Click Add Level.
3. In Then by, choose Owner with A to Z.
4. Click OK.

Within High, Jamal (T-04) now comes before Sara (T-02) and Wei (T-07). The guide on how to sort by multiple columns explains how levels work.

How to do a custom sort with a formula#

In Excel 2021, Excel 2024 or Microsoft 365, you can sort by your own order with a formula that updates as the data changes. Type the order in cells, say E2:E4 (High, Medium, Low), then:

=SORTBY(A2:C9,MATCH(B2:B9,E2:E4,0))

How it works:

1. MATCH(B2:B9,E2:E4,0) finds each priority's position in your list: Low becomes 3, High becomes 1, Medium becomes 2.
2. SORTBY sorts the whole table by those numbers, smallest first.

The result spills the same order as the Sort dialog. Change the list to Low, Medium, High, and the result flips at once. You can also put the order inside the formula with curly brackets: MATCH(B2:B9,{"High","Medium","Low"},0). XMATCH works too: XMATCH(B2:B9,E2:E4).

If a priority isn't in your list, such as "Urgent", MATCH returns #N/A and the formula fails. Wrap MATCH in IFERROR to send unknown values to the end:

=SORTBY(A2:C9,IFERROR(MATCH(B2:B9,E2:E4,0),99))

For more on the function, see SORT and SORTBY, and for choosing between them, SORT vs SORTBY.

Custom sort in older Excel with a helper column#

Without SORTBY, add a helper column that turns the order into numbers, then sort by it:

1. In D2, type =MATCH(B2,$E$2:$E$4,0) and fill it down to D9.
2. Sort the table by column D, Smallest to Largest.

This works in every version. You can hide column D afterwards. The trade-off between sorting in place and sorting with a formula is covered in the Sort button vs the SORT function.

Practise it#

Custom orders come up whenever data has categories, and tests like to check that you don't settle for A to Z. Practise sorting and filtering a table with the ribbon tools, then practise the SORT function in a live spreadsheet, where you type the formula and it checks it instantly. Try the SORTBY exercise on this page first.