The SORTBY function is a valuable addition to Excel’s suite of data manipulation tools, specifically designed for sorting data in a versatile and efficient way. Introduced in Excel 365, this function allows users to rearrange rows in a range or array based on the values from one or more accompanying arrays. It simplifies the sorting process, especially when dealing with multiple criteria, and retains the integrity of the original data.

Syntax

The syntax of the SORTBY function is as follows:

SORTBY(array, by_array1, [by_array2], …)

– array: The range or array that you want to sort.
– by_array1: The range or array by which you want to sort the array.
– by_array2, …: Optional additional arrays that can be used as secondary sort keys.

Examples

Example 1: Basic Sorting

To sort a list of products by their price in ascending order, you can use:

=SORTBY(A2:A10, B2:B10)

Where A2:A10 contains the list of products, and B2:B10 contains their corresponding prices.

Example 2: Sorting with Multiple Criteria

You can sort data first by last name and then by first name. For example:

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

In this formula, the A2:A10 range is sorted in ascending order first, followed by B2:B10 also in ascending order.

Example 3: Descending Order Sorting

To sort a range of sales data in descending order based on sales figures:

=SORTBY(D2:D10, C2:C10, -1)

Here, D2:D10 contains the sales data, and C2:C10 contains the respective sorting criteria in descending order.

Error Handling

When using the SORTBY function, several errors may arise:

– VALUE!: This error may occur if the sizes of the array and the by_array arrays do not match.
– REF!: This indicates that a reference in the formula is invalid. Ensure that all referenced ranges exist and are correctly entered.

Conclusion

The SORTBY function is a robust tool that significantly improves data management in Excel. Whether you need to arrange data based on one or multiple criteria, SORTBY offers a straightforward method to do so without disrupting your original dataset. By understanding its syntax and practical application through examples, users can enhance their data analysis capabilities and improve their overall productivity in Excel.