How to Sort By Custom List in Excel

Why This Task Matters in Excel

Imagine a regional sales report where the territories need to appear North, South, East, West instead of alphabetical order, or a product backlog that must show Priority 1, Priority 2, Priority 3. Standard ascending or descending sorts fail in these situations because they follow strict alphanumeric rules. In day-to-day business, however, we often think in hierarchies, phases, seasons, and grades that do not align alphabetically.

Finance teams may need fiscal quarters ordered Q1, Q2, Q3, Q4; logistics departments might prefer delivery status arranged Ready, In-Transit, Delivered, Returned; human resources might rank performance as Outstanding, Exceeds, Meets, Below. In each scenario, conveying information in a meaningful sequence is critical for dashboards, pivot tables, PowerPoint exports, and decision-making conversations.

Excel excels (pun intended) at structured manipulation of data, and it offers several pathways—built-in Custom Lists, dynamic array formulas, helper columns, Power Query, and even VBA—to impose bespoke sort orders. Failure to master these options can lead to manual drags, copy-pastes, or confusing reports in which “Low risk” precedes “Critical risk” or December jumps ahead of February. Understanding custom sorts also tightens your control over pivot tables, eliminating the need to manually reposition items every refresh. Moreover, being able to apply a company-specific ranking immediately influences downstream formulas such as INDEX/MATCH, XLOOKUP, and aggregation functions because the data arrives in the correct sequence right from the source.

Best Excel Approach

For most modern Excel installations (Microsoft 365, Excel 2021), the fastest, most flexible technique is a dynamic array that combines SORTBY with XMATCH (or traditional MATCH). This formula-based route is completely automated: as soon as new rows appear or a custom list changes, the sorted output updates without any additional clicks. It is especially powerful when you want the original table untouched and a sorted view elsewhere.

Why SORTBY + XMATCH?

• It uses native functions—no VBA or add-ins.
• It returns the entire dataset, not just one column.
• It respects any custom order stored on the worksheet, eliminating the need to change Excel’s global Custom List settings.
• It spills, so it automatically expands or contracts with the source data.

Syntax overview:

=SORTBY(source_table, XMATCH(source_table[column_to_sort],
                             custom_order, 0, 1))

• source_table – the range or structured table to be re-ordered
• column_to_sort – the specific column inside source_table whose position drives the custom order
• custom_order – a single-column range or array listing the desired sequence
• XMATCH returns numeric positions; SORTBY uses those positions as the sort key.

Alternative approaches include the legacy Custom List dialog (manual yet quick for one-off sorts), a helper column with MATCH plus a conventional sort, Power Query for repeatable ETL pipelines, and VBA for complex interfaces. Each has merits, but dynamic arrays usually provide the best mix of automation and transparency.

Parameters and Inputs

1. source_table

   • Can be an ordinary range [A2:E1000] or an official Excel Table (recommended for auto-expansion).
   • Should have consistent headers—no merged cells or blank header names.

2. column_to_sort

   • The field containing values that match items in your custom_order.
   • Data type must align: text with text, numbers with numbers.

3. custom_order

   • A single-column range such as [G2:G5] or an inline array like [\"High\",\"Medium\",\"Low\"].
   • Case-insensitive by default with XMATCH (set match_mode = 0).
   • Cannot include duplicates; if a duplicate exists, the first position wins.

Optional parameters inside XMATCH:

• match_mode 0 for exact match, 1 for exact or next larger, -1 for exact or next smaller.
• search_mode 1 for first-to-last, -1 for last-to-first (rarely needed here).

Data validation tips:

• Add Data Validation on column_to_sort to restrict entries to your custom_order list.
• Use COUNTIF on custom_order to ensure no repeats before applying the formula.

Edge cases:

• Items not present in custom_order return #N/A with XMATCH. Wrap in IFNA and push them to bottom or top.
• Blank cells evaluate as position zero. Decide whether to ignore or place blanks last using IF logic.

Step-by-Step Examples

Example 1: Basic Scenario — Sorting Months in Fiscal Order

You have a marketing calendar with months in [B2:B13] and activities in [C2:C13]. The months are currently April to March because the list was imported from another system. You want to show them in true fiscal order: April, May, June, July, August, September, October, November, December, January, February, March.

1. Enter the custom order in [E2:E13] exactly as desired.
2. Convert the original range [A1:C13] to an Excel Table named tblCal (Ctrl + T).
3. In an output sheet, in cell H2, enter:

=SORTBY(tblCal, XMATCH(tblCal[Month], $E$2:$E$13, 0, 1))

4. Press Enter. The table spills below H2 with months reorganized.
5. Add a new line for April 2024 in tblCal; check that the sorted list auto-expands.
6. Change the custom order to start January; watch the output reorder instantly.

Why it works: XMATCH returns the position of each month within [E2:E13]. SORTBY then reorders tblCal based on those numeric positions.

Troubleshooting: If July shows #N/A, confirm its spelling matches the custom list, including trailing spaces.

Variations: Convert custom_order to an inline array for portability:

=SORTBY(tblCal, XMATCH(tblCal[Month], {"Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar"},0,1))

Example 2: Real-World Application — Risk Matrix Prioritization

A project-management office tracks risks with columns: ID, Description, Owner, Severity, Date Logged. Severity may be Critical, High, Medium, Low. Executives insist risks appear in descending seriousness: Critical first.

1. Severity order list

• Create a small table in [J2:J5]: Critical, High, Medium, Low.
• Name the range SeverityOrder.

2. Source data

• Table named tblRisks occupying [A1:E500].

3. Formula in a dashboard sheet cell A2:

=SORTBY(tblRisks,
        XMATCH(tblRisks[Severity], SeverityOrder, 0, 1),
        -1)          // Secondary descending by Date Logged

Explanation:

• XMATCH supplies the custom severity rank.
• The optional SORTBY argument -1 ensures that within each severity group, newer risks appear first.

Business impact: Executives open the dashboard knowing the top of the list are the most threatening issues, updated live from the risk log. No macro or manual sorting required.

Performance: On 500 rows the calculation is instantaneous. For thousands of rows, store SeverityOrder on a separate sheet marked as “Very Hidden” to prevent accidental edits while keeping formula speed.

Example 3: Advanced Technique — Dynamic User-Driven Order

Suppose an analyst needs flexibility to change the sort order on the fly—for instance, toggling between alphabetical, revenue size, or strategic importance—without editing formulas.

Setup:

1. In [M1:M3] place a data-validation list offering three sorting modes: Alphabetical, Revenue, Strategy.
2. In [O2:O?] use CHOOSE mapping arrays:

=CHOOSEMATCH( $M$1,
              {"Alphabetical","Revenue","Strategy"},
              {A2:A100,                         // Column with names
               RevenueOrder,                   // Named range with custom order
               StrategyOrder} )                // Another named range

(Excel currently lacks CHOOSEMATCH, so replicate with nested IFS or LET and SWITCH.)

3. Wrap chosen range in SORTBY:

=LET(
     mode, $M$1,
     data, tblClients,
     key,  SWITCH(mode,
                  "Alphabetical", data[Client],
                  "Revenue",      XMATCH(data[RevenueBand], RevenueOrder,0,1),
                  "Strategy",     XMATCH(data[StrategicRank], StrategyOrder,0,1)
                 ),
     SORTBY(data, key)
)

Highlights:

• Uses LET for readability and performance.
• SWITCH chooses which ranking logic to apply.
• Dynamic arrays update as soon as the user selects a different mode.

Edge handling: Provide a default case in SWITCH to safely revert to alphabetical.

Tips and Best Practices

1. Use structured tables for both the source data and the custom order list. Structured references make formulas self-documenting and resilient to range changes.
2. Shade or lock the custom order list; accidental edits shift the entire sort outcome.
3. Wrap XMATCH in IFNA to place unknown items at bottom:

=SORTBY(data, IFNA(XMATCH(data[column], CustomOrder,0,1), 999))

4. Nest multiple XMATCH expressions inside SORTBY to create multi-level custom sorts (for example, Severity then Status).
5. For dashboards, reference custom order lists on a hidden sheet instead of arrays in the formula text; this simplifies maintenance.
6. If the order list is constant (months, weekdays), consider creating a global Custom List in Excel Options so manual sorts in pivot tables also respect the order.

Common Mistakes to Avoid

1. Duplicating an item in custom_order. XMATCH will grab the first occurrence, causing silent ranking conflicts. Always deduplicate with Remove Duplicates or COUNTIF checks.
2. Mismatched spelling or extra spaces. “High ” with a trailing space yields #N/A, pushing rows to the bottom or triggering errors. Trim the list or use the CLEAN and TRIM functions.
3. Forgetting absolute references ($) when pointing to the custom list. Relative references shift when formulas are filled sideways, breaking spills.
4. Sorting directly on the original table without a backup. If you apply a destructive sort then undo too late, you may lose the initial order. Keep a formula-based view or version control copy.
5. Overlooking error handling. Any item absent from the custom list shows #N/A; without IFNA, SORTBY stops. Always wrap XMATCH to maintain a working output.

Alternative Methods

When to choose:

• Need live updating inside the sheet? Use dynamic arrays.
• Preparing data for weekly import into a database? Power Query.
• Locked corporate template running on Excel 2013? Helper column approach.
• User must click a button and hates formulas? VBA macro.

FAQ

When should I use this approach?

Use SORTBY + XMATCH when you have Microsoft 365 or Excel 2021 and want hands-free updates. It is perfect for dashboards, reports that refresh daily, and situations with multiple custom sort needs in the same workbook.

Can this work across multiple sheets?

Absolutely. Point source_table to a Table on Sheet1 and custom_order to a range on Sheet2. Because both are sheet-qualified, the formula recalculates whenever either sheet changes.

What are the limitations?

The biggest limitation is version support: older Excel versions lack SORTBY and XMATCH. Additionally, arrays cannot spill into protected or non-adjacent ranges, and formulas cannot reside inside tables that overlap the spill range.

How do I handle errors?

Wrap XMATCH with IFNA or IFERROR to assign unknown items a very high or very low rank. Example:

=SORTBY(data, IFNA(XMATCH(data[Col], CustomOrder,0,1), 999))

Does this work in older Excel versions?

If you are on Excel 2016 or earlier, replace SORTBY with a helper column:

=MATCH([@Priority], $J$2:$J$5, 0)

Add this rank column to the table, then apply a normal ascending sort each time you refresh data.

What about performance with large datasets?

Dynamic arrays leverage Excel’s new calc engine and usually outperform manual sorts. Still, consider turning large ranges into Tables and avoid volatile functions. For 100 000 rows or more, Power Query may offer better memory management.

Conclusion

Sorting by custom list unlocks seriously professional reporting. Whether months, risk levels, or bespoke business stages, the ability to impose your own order keeps audiences focused and eliminates manual tinkering. Modern functions such as SORTBY and XMATCH make the process fully dynamic, while fallback methods ensure compatibility with older versions. Master this skill and you strengthen pivot-table design, dashboard clarity, and overall data governance. Next, explore combining custom sorts with FILTER or XLOOKUP for interactive, drill-down experiences that wow stakeholders.