Excel SORT and SORTBY Functions Masterclass in 2026: Dynamic Sorting Without Macros or Manual Work

For years, sorting data in Excel meant clicking the Sort button, choosing columns and order, clicking OK, and then repeating the entire process every time your data changed. If data was added, you sorted again. In 2026, the SORT and SORTBY functions have fundamentally changed this workflow. These dynamic array functions return a sorted copy of your data that updates automatically whenever the source changes, and they are now among the most-used functions in modern Excel.

This masterclass covers SORT and SORTBY from first use through advanced multi-column sorting scenarios, combined with other dynamic array functions for powerful automated analysis.

Understanding Dynamic Array Sorting

When you use the Sort button, Excel physically rearranges the cells in your worksheet as a one-time action. When you use the SORT or SORTBY function, Excel creates a new output range that displays your data in sorted order while the original data stays exactly where it is. The output updates instantly whenever the source data changes.

This means you can have your raw data in one area, sorted views in another area, and they stay synchronized without any manual intervention. Multiple people can add data to the source, and every sorted view refreshes automatically.

The SORT Function: Syntax and Basic Usage

The SORT function syntax is: =SORT(array, [sort_index], [sort_order], [by_col])

array: The range or array you want to sort, such as A2:C100 or a named range

sort_index: Which column to sort by (1 for the first column, 2 for the second). Default is 1

sort_order: 1 for ascending (the default) or -1 for descending

by_col: FALSE to sort rows (the default) or TRUE to sort columns instead of rows

Basic Example

Suppose you have a list of sales reps in column A and their sales totals in column B, spanning rows 2 through 51. To display this data sorted by sales total from highest to lowest, enter this formula in an empty cell: =SORT(A2:B51, 2, -1)

This returns all 50 rows sorted by column 2 in descending order. The result spills automatically into as many rows and columns as needed. If you add a new sales rep to the source range, the sorted output updates instantly.

The SORTBY Function: Sorting by External Criteria

SORTBY is more flexible than SORT because it lets you sort one range based on values in a completely separate range or array. The syntax is: =SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)

array: The data you want to return in sorted order

by_array1: A column or row of values to sort by (does not need to be in your output array)

sort_order1: 1 for ascending, -1 for descending

Additional by_array/sort_order pairs enable multi-level sorting

Practical Example: Sorting by a Calculated Column

Imagine you have customer names in column A, purchase amounts in column B, and discount percentages in column C. You want to see customers sorted by their net purchase value without adding a helper column. With SORTBY: =SORTBY(A2:C100, B2:B100*(1-C2:C100), -1)

This returns columns A through C sorted by the net value in descending order, without requiring a helper column anywhere in your worksheet.

Multi-Level Sorting with SORTBY

SORTBY really shines when you need to sort by multiple criteria simultaneously. Example: sort a sales table first by region A to Z, then by sales amount highest to lowest within each region:

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

This sorts by column A ascending as the primary sort, then by column C descending as the secondary sort within each primary group. You can chain as many sort levels as needed by adding more by_array and sort_order pairs.

Combining SORT with Other Dynamic Array Functions

SORT + FILTER

One of the most powerful combinations is SORT with FILTER to filter your data and display results in sorted order in a single formula: =SORT(FILTER(A2:C100, B2:B100>10000), 2, -1)

This returns only rows where column B exceeds 10000, sorted by column B from highest to lowest. The output updates automatically when source data changes.

SORT + UNIQUE

UNIQUE extracts distinct values and SORT arranges them alphabetically: =SORT(UNIQUE(A2:A100))

This gives you a dynamic, automatically alphabetized list of unique values that refreshes whenever column A changes. Perfect for dropdown list sources or report headers.

SORT + TAKE

The TAKE function lets you extract the top N rows from a sorted result: =TAKE(SORT(A2:B100, 2, -1), 10)

This returns the top 10 rows by column B value. Combined with SORT and FILTER, TAKE lets you build dynamic top-N tables that need no manual maintenance whatsoever.

Common Errors and How to Fix Them

SPILL error: The output range is blocked by existing content. Clear the cells where the function wants to spill its results

VALUE error: The sort_index number is higher than the number of columns in your array. Check that your sort_index matches a real column position

REF error: The by_array in SORTBY does not match the row count of the main array. Both must have the same number of rows

Sort does not update: Your source range may not include all the data. If you added rows beyond the original range, expand the range in your formula or use a structured Table reference

Using SORT with Excel Tables

When your source data is formatted as an Excel Table created with Ctrl+T, your SORT and SORTBY formulas can reference the table by name instead of cell range. This is best practice because table references expand automatically when new rows are added:

=SORT(SalesTable, 2, -1)

Replace SalesTable with your actual table name. Now when anyone adds a new row to the table, the sorted output expands accordingly without you touching the formula. This is the most robust approach for shared workbooks with regularly changing data.

Conclusion

SORT and SORTBY represent one of the most practical upgrades to Excel in the dynamic array era. They replace repetitive manual sorting with formulas that work once and keep working as your data evolves. By combining them with FILTER, UNIQUE, and TAKE, you can build sophisticated data views that would previously have required VBA macros or Power Query transformations.

Start experimenting in your next spreadsheet by replacing your next manual sort with a SORT formula in an empty area of the sheet. Once you see how it auto-updates, you will never want to go back to clicking the Sort button again.