How to Use the SORTBY Function in Excel for Multi-Level Dynamic Sorting

The Problem with Static Sorting

In traditional Excel workflows, if you want to sort a massive dataset, you highlight the data, navigate to the Data tab on the ribbon, and click the “Sort” button.

The problem is that this action is static and destructive. It permanently alters the original data grid. If new rows of data are added ten minutes later, or if a sales figure changes, the data is no longer sorted correctly. You must manually click the Sort button again.

With the introduction of Dynamic Arrays in Microsoft 365, Excel fundamentally modernized this workflow with the SORTBY function. SORTBY analyzes your master data and instantly “spills” a perfectly sorted copy of that data into adjacent cells. The original data remains untouched, and most importantly, the new sorted array updates dynamically in real-time as the source data changes.

SORT vs. SORTBY

Excel actually introduced two sorting functions: SORT and SORTBY.

• SORT is simple. It sorts an array based on an index number (e.g., “Sort this table by column 3”).
• SORTBY is exponentially more powerful. It allows you to sort an array based on multiple different columns simultaneously, and it explicitly references the columns by their actual range rather than a confusing index number.

The Syntax of SORTBY

The syntax allows you to stack as many sorting rules as you need:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

• array: The block of data you want to output (e.g., A2:D100).
• by_array1: The specific column you want to sort by first (e.g., C2:C100).
• [sort_order1]: 1 for Ascending (A-Z), -1 for Descending (Z-A).

Step-by-Step Examples

Assume you have a master list of employee sales data in columns A through C.

• Column A (A2:A100): Region (East, West, North, South)
• Column B (B2:B100): Salesperson Name
• Column C (C2:C100): Total Revenue

Example 1: The Basic Sort

You want to generate a leaderboard that outputs the entire table, but sorted by Revenue (Column C) from highest to lowest.

Click on an empty cell (e.g., E2) and type:

=SORTBY(A2:C100, C2:C100, -1)

Excel grabs the entire table and spills a perfect leaderboard starting at E2. If John Smith’s revenue in the master table is updated from $1,000 to $50,000, he will instantly shoot to the top of your dynamic leaderboard without you touching a single button.

Example 2: Multi-Level Sorting

Multi-level sorting is where SORTBY shines. Suppose you want to sort the table primarily by Region (alphabetically, A-Z). But, within each region, you want the employees sorted by Revenue from highest to lowest.

You simply chain the logic together:

=SORTBY(A2:C100, A2:A100, 1, C2:C100, -1)

How Excel processes this:

1. It first looks at Column A (Region) and sorts it ascending (1). All the “East” employees are grouped together, then “North,” etc.
2. When there is a tie (multiple employees in the “East” region), it moves to the second rule.
3. It looks at Column C (Revenue) and sorts those specific tied employees descending (-1).

The result is a beautifully organized, perfectly grouped report.

Sorting with Excel Tables

Like all Dynamic Arrays, SORTBY is most effective when paired with official Excel Tables (created via Ctrl+T). If your raw data is formatted as a Table named “SalesTable”, you can write the formula like this:

=SORTBY(SalesTable, SalesTable[Region], 1, SalesTable[Revenue], -1)

Now, as hundreds of new employees are added to the bottom of the raw data table over the next year, your dynamic report automatically expands and continuously resorts itself flawlessly.

Conclusion

The SORTBY function fundamentally changes data reporting in Excel. By replacing manual, destructive UI actions with a dynamic, multi-tiered array formula, analysts can build self-maintaining dashboards that present complex, prioritized data in real-time.