How to Create and Sort by a Custom List in Microsoft Excel

Sorting tabular records in Microsoft Excel typically adheres to alphabetical order (A to Z) or numerical sequence. However, real-world operational workflows rarely mirror alphabetical logic. Project priorities (Critical, High, Medium, Low), garment sizing (XS, S, M, L, XL), and regional sales territories require a defined hierarchy that alphabetical sorting disrupts.

Microsoft Excel resolves this limitation through Custom Lists. By creating a custom list, you can establish an authoritative sequence that dictates how data sorts across your workbooks and enables rapid series generation via AutoFill. This guide walks you through building custom lists from existing worksheet ranges, defining manual lists in Excel Options, and executing multi-level custom sorts.

How Custom Lists Function in Excel

Excel ships with four predefined custom lists out of the box: abbreviated days of the week (Sun–Sat), full days of the week (Sunday–Saturday), abbreviated months (Jan–Dec), and full month names (January–December). These built-in lists explain why typing Monday and dragging the fill handle down auto-populates subsequent days rather than duplicating the word.

When you register a user-defined custom list, Excel stores the hierarchy at the local application level. This ensures the sequence remains available across all current and future workbooks on your machine, without requiring macro workarounds or hidden helper columns.

Method 1: Importing a Custom List from an Existing Cell Range

If you already have your designated hierarchy typed into a spreadsheet column, importing the values into Excel Options is the fastest approach:

1. Open your workbook and highlight the range containing your ordered categories (for example, cells A2:A6 containing Urgent, High, Medium, Low, Backlog).
2. Click the File tab in the ribbon and select Options at the bottom of the left sidebar.
3. In the Excel Options dialogue, click Advanced.
4. Scroll down to the General section near the bottom of the pane.
5. Click the Edit Custom Lists… button.
6. In the Custom Lists dialogue, verify that your cell selection appears in the Import list from cells box.
7. Click the Import button. Your ordered values will instantly transfer into the List entries box and register as an active list in the left-hand column.
8. Click OK twice to close both dialogue windows and save your preferences.

Method 2: Manually Typing a New Custom List in Excel Options

If your desired hierarchy does not currently exist in your sheet cells, you can enter the items directly into the system preferences:

1. Navigate to File > Options > Advanced.
2. Scroll to the General group and click Edit Custom Lists….
3. Ensure NEW LIST is selected in the Custom lists pane on the left.
4. Click inside the List entries box on the right.
5. Type your sequence in strict order of hierarchy, pressing Enter after each entry:

   Critical
   High
   Medium
   Low
   Resolved

6. Click the Add button to save the entries into the custom list registry.
7. Click OK and then OK again to return to your spreadsheet.

Applying Your Custom List to Sort Data Tables

Once registered, your custom sequence does not apply automatically to standard one-click toolbar sort buttons; you must instruct Excel to utilise the custom list via the Advanced Sort dialogue.

1. Click any single cell inside your dataset or table.
2. Go to the Data tab on the ribbon and click the large Sort button in the Sort & Filter group.
3. Ensure the My data has headers checkbox is ticked in the top-right corner.
4. Under the Column dropdown, choose the field you wish to sort (e.g. Priority Level).
5. Keep Sort On set to Cell Values.
6. Open the Order dropdown and select Custom List….
7. In the dialogue that appears, locate and highlight your custom sequence (e.g. Critical, High, Medium, Low, Resolved).
8. Click OK to confirm the custom order.
9. Click OK in the primary Sort dialogue. Your table will instantly reorganise based on your custom ranking rather than alphabetical characters.

Combining Custom Lists with Multi-Level Sorting

In comprehensive operational sheets, you will often need to sort by multiple criteria simultaneously—such as ordering by custom priority first, followed by closing date or monetary value:

1. Open the Data > Sort dialogue.
2. Configure your first sorting tier using your custom list (e.g. Department ordered by custom corporate operational tiers).
3. Click the Add Level button in the top-left corner of the dialogue.
4. In the Then by dropdown, choose your secondary field (e.g. Due Date).
5. Set the secondary Order to Oldest to Newest.
6. Click OK. Excel will categorise all records into your custom department order, and within each department group, sort tasks chronologically.

Using AutoFill with Custom Lists

In addition to sorting, custom lists significantly speed up manual data entry across workbooks. Whenever you type any single item from your custom list into a cell and drag the fill handle horizontally or vertically, Excel cycles through subsequent items in your defined order.

For example, typing High into cell A1 and dragging down five rows will automatically produce Medium, Low, Resolved, Critical, and High.

Editing or Deleting Custom Lists

If organizational restructuring changes your priority levels, you can update or remove obsolete lists at any time:

1. Return to File > Options > Advanced > Edit Custom Lists….
2. Select your list from the Custom lists column.
3. To update values, edit the text inside the List entries box and click Add.
4. To permanently remove a list, click the Delete button. Excel will prompt you to confirm removal; click OK. Note that built-in weekday and month lists cannot be modified or deleted.

Troubleshooting Custom List Sorting Issues

• Values not sorting in expected order: Check for accidental trailing spaces in either your worksheet data or the custom list definition. A cell containing "High " will not match a list entry defined as "High". Use =TRIM() to clean cell contents before sorting.
• Custom list missing on another computer: Custom lists are stored in the local application preferences of your machine, not embedded directly into workbook XML files. If sharing workbooks across a team, either configure the list on recipient workstations or instruct colleagues to import the range using Method 1.
• Numbers sorting incorrectly: Custom lists are designed for text strings. If your sequence contains raw integers or dates, Excel defaults to numerical evaluation. To create a custom numerical list, format the cells as text strings prior to importing.