How to Use SORTBY Function in Excel

Key Insights

SORTBY sorts data based on a separate column or range, while SORT uses the data itself—making SORTBY essential when your sort criteria differs from your display data
You can stack multiple sort criteria directly in one formula without nesting, sorting by up to 126 different columns with independent ascending/descending orders
SORTBY creates dynamic arrays that update automatically when source data changes, eliminating the manual re-sorting required with traditional Sort & Filter buttons

Introduction to SORTBY

The SORTBY function arrived in Excel 365 and Excel 2021 as part of Microsoft’s dynamic array revolution. Unlike clicking the Sort button in the Data tab, SORTBY creates a formula-based sort that updates automatically when your source data changes. This matters when you’re building dashboards, reports, or any worksheet where data updates frequently.

The critical difference between SORTBY and traditional sorting: SORTBY is a formula that returns sorted results without modifying your original data. Your source table stays intact while you display sorted versions wherever needed. This separation of concerns is fundamental to building maintainable spreadsheets.

Traditional sorting also breaks when you add new rows—you must manually re-sort. SORTBY formulas recalculate automatically, keeping your sorted views current without intervention. For anyone building data models or automated reports, this alone justifies learning the function.

Basic SORTBY Syntax and Parameters

The SORTBY syntax follows this structure:

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

array: The range you want to sort and display. This is your output data.

by_array1: The range containing values that determine sort order. This can be the same as your array or completely different.

sort_order1: Optional. Use 1 for ascending (default) or -1 for descending.

by_array2, sort_order2, etc.: Additional sort criteria. You can specify up to 126 sort levels.

The key conceptual shift: array is what you display, by_array is what controls the order. They don’t have to be the same.

Here’s a minimal example sorting sales data:

=SORTBY(A2:B10, B2:B10, -1)

This displays the range A2:B10 (perhaps names and revenue) sorted by column B (revenue) in descending order. The original data in A2:B10 remains unchanged.

Single-Column Sorting Examples

Let’s work with a practical employee dataset. Assume column A contains names and column B contains salaries.

Sorting by salary (ascending):

=SORTBY(A2:B50, B2:B50, 1)

This returns all employee data sorted from lowest to highest salary. The 1 explicitly requests ascending order, though you could omit it since ascending is default:

=SORTBY(A2:B50, B2:B50)

Sorting inventory by stock quantity (descending):

Assume A2:C100 contains Product Name, SKU, and Stock Quantity. To see which products need reordering:

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

The -1 reverses the order, showing highest stock quantities first. This immediately highlights your best-stocked items at the top.

Notice you’re sorting the entire three-column range based solely on column C values. The by_array doesn’t need to include all columns—only the one containing your sort criteria.

Multi-Column Sorting (Nested Criteria)

SORTBY’s real power emerges with multiple sort levels. Unlike nested SORT functions, you simply add more by_array and sort_order pairs.

Sorting sales data by region, then by amount:

Assume A2:C200 contains Salesperson, Region, and Sales Amount.

=SORTBY(A2:C200, B2:B200, 1, C2:C200, -1)

This sorts first by Region (column B) alphabetically, then within each region, sorts by Sales Amount (column C) from high to low. The formula reads naturally: sort the data by region ascending, then by amount descending.

Three-level sort example:

=SORTBY(A2:E500, B2:B500, 1, C2:C500, 1, D2:D500, -1)

This sorts by column B (ascending), then column C (ascending), then column D (descending). Each additional pair adds another sort level. The order matters—earlier criteria take precedence.

Advanced SORTBY Techniques

Sorting by calculated values:

You can sort by values that don’t exist in your table. Suppose you want to sort employees by total compensation (salary + bonus), but you don’t have a Total Compensation column.

Assume B2:B50 is salary and C2:C50 is bonus:

=SORTBY(A2:C50, B2:B50 + C2:C50, -1)

The by_array is a calculated array (B2:B50 + C2:C50), not a static range. Excel computes the sum for each row and sorts accordingly.

Combining SORTBY with FILTER:

FILTER returns only rows meeting criteria, but in arbitrary order. Wrap it with SORTBY for sorted, filtered results:

=SORTBY(FILTER(A2:C100, B2:B100="East"), 3, -1)

This filters for “East” region, then sorts the filtered results by the 3rd column descending. Note we use column index 3 instead of a range reference because FILTER’s output range isn’t predictable.

Case-sensitive text sorting:

SORTBY normally ignores case. For case-sensitive sorting, use EXACT with COUNTIF:

=SORTBY(A2:A20, COUNTIF(A$2:A2, A2:A20), 1)

This is admittedly complex and rarely needed, but demonstrates SORTBY’s flexibility with array formulas.

Sorting by non-adjacent columns:

You want to display columns A, B, and E, sorted by column G:

=SORTBY(CHOOSECOLS(A2:E100, 1, 2, 5), G2:G100, -1)

CHOOSECOLS selects specific columns, then SORTBY sorts them based on column G, which isn’t even in the output.

Common Errors and Troubleshooting

#CALC! Error:

This typically means your array and by_array have different row counts:

=SORTBY(A2:B10, C2:C15, 1)  // Wrong - mismatched ranges
=SORTBY(A2:B10, C2:C10, 1)  // Correct

Both ranges must have identical row counts. Column counts can differ—you might sort 5 columns based on 1 column’s values.

#VALUE! Error:

Usually caused by sorting mixed data types inappropriately, or by referencing entire columns in Excel versions that don’t support it:

=SORTBY(A:B, C:C, 1)  // May cause issues
=SORTBY(A2:B1000, C2:C1000, 1)  // Safer

Spill Range Conflicts:

SORTBY returns dynamic arrays that “spill” into multiple cells. If any destination cell contains data, you’ll get a #SPILL! error. Clear the cells where results will appear, or move your formula.

Compatibility:

SORTBY doesn’t exist in Excel 2019 or earlier. If sharing workbooks with users on older versions, they’ll see #NAME? errors. Consider using Table sorting or VBA macros for backward compatibility.

Incorrect sort order parameter:

=SORTBY(A2:B10, B2:B10, "DESC")  // Wrong - expects number
=SORTBY(A2:B10, B2:B10, -1)      // Correct

Use 1 or -1, not text strings.

SORTBY vs. SORT: When to Use Each

The SORT function sorts data by its own columns, while SORTBY sorts data based on separate criteria. Here’s when to use each:

Use SORT when:

You’re sorting data by columns within that same data range
You want to sort by the 1st, 2nd, 3rd column of your output
Your formula reads more clearly referencing column indexes

=SORT(A2:C100, 2, -1)  // Sort by 2nd column of the range

Use SORTBY when:

Your sort criteria is in a different column than your output
You’re sorting by calculated values not in your dataset
You need to sort by non-adjacent columns
You want more explicit control over which columns drive sorting

=SORTBY(A2:B100, D2:D100, -1)  // Sort by column D, display A:B

Performance consideration: Both functions perform similarly. Choose based on formula clarity. If you’re referencing column indexes (1, 2, 3), SORT is cleaner. If you’re referencing actual ranges (B2:B100, D2:D100), SORTBY is more explicit and maintainable.

Combining both: You can nest them, though it’s rarely necessary:

=SORTBY(SORT(A2:C100, 1, 1), 2, -1)

This sorts first by column 1, then re-sorts by column 2. In practice, just use multiple criteria in one function.

The bottom line: SORTBY offers more flexibility for complex sorting scenarios, especially when your sort logic differs from your display data. Master both functions, then choose based on which produces more readable formulas for your specific use case.