A Guide to Sorting Data with the SORT and SORTBY Functions

A Guide to Sorting Data with the SORT and SORTBY Functions

While you can always sort data manually using Excel's ribbon, a more powerful and flexible approach is to use dynamic array formulas. The **SORT and SORTBY functions** allow you to create a live, sorted version of your data that automatically updates when the source data changes. These functions are a huge improvement over traditional methods and are essential for building dynamic reports and dashboards.

The SORT Function: Simple and Quick

The `SORT` function is the simplest way to sort a range of data by a single column. Its syntax is straightforward:

=SORT(array, [sort_index], [sort_order], [by_col])

• array: The range of data you want to sort.
• [sort_index]: The column number (e.g., 2 for the second column) you want to sort by.
• [sort_order]: `1` for ascending (default) or `-1` for descending.

If you have a list of products and their prices in cells `A2:B10`, you can sort the list by price in descending order with this formula:

=SORT(A2:B10, 2, -1)

The SORTBY Function: Flexible and Powerful

The `SORTBY` function is more powerful because it allows you to sort one range based on the values in another range. This is particularly useful for sorting a table based on a hidden or unselected column.

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

• array: The range you want to return, in sorted order.
• by_array1: The range that contains the data you want to sort by.
• [sort_order1]: `1` for ascending or `-1` for descending.

Imagine you have a table of student names and their test scores. You want to display the names in order of the highest score. If the names are in column A and scores are in column B, you would use:

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

This formula will return a list of names sorted by their scores in descending order, without displaying the scores themselves. By mastering both functions, you gain unparalleled control over how you present your data in Excel.