SORTBY function
The SORTBY function in Excel is used to sort the contents of a range or array based on the values in one or more other columns (or arrays). It offers more flexibility than the regular SORT function by allowing you to sort data based on multiple criteria and control the sort order (ascending or descending) for each criterion.
Syntax
SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)
Parameters
array: The range or array to be sorted.by_array1: The first array or range to sort by. This can be a column in the dataset or an array of values.sort_order1(optional): The sort order for the first array. Use:1for ascending order (default if omitted).-1for descending order.
by_array2,sort_order2, etc. (optional): Additional arrays and corresponding sort orders to use for secondary, tertiary, etc., sorting.
How It Works
- The SORTBY function sorts the main array based on the values in the
by_arraycolumns. You can specify multiple sorting arrays to apply multi-level sorting. - For each
by_array, you can define whether the sorting should be done in ascending or descending order.
Examples
Example 1: Simple Sorting
Let’s say you have a dataset of employees with names and salaries and you want to sort it by salary in ascending order:
| Name | Salary |
|---|---|
| Alice | 45000 |
| Bob | 52000 |
| Charlie | 47000 |
To sort this data by salary in ascending order:
=SORTBY(A2:B4, B2:B4, 1)
This formula will return:
| Name | Salary |
|---|---|
| Alice | 45000 |
| Charlie | 47000 |
| Bob | 52000 |
Example 2: Multi-Level Sorting
If you want to sort the employees first by salary in descending order and then by name in ascending order:
=SORTBY(A2:B4, B2:B4, -1, A2:A4, 1)
This will sort the data by salary in descending order, and in case of ties, it will sort by name alphabetically (ascending):
| Name | Salary |
|---|---|
| Bob | 52000 |
| Charlie | 47000 |
| Alice | 45000 |
Example 3: Sorting a Table by Multiple Criteria
If you have a table of data with three columns—Name, Department, and Salary—and you want to sort first by Department (ascending) and then by Salary (descending):
| Name | Department | Salary |
|---|---|---|
| Alice | HR | 45000 |
| Bob | IT | 52000 |
| Charlie | HR | 47000 |
The formula would look like this:
=SORTBY(A2:C4, B2:B4, 1, C2:C4, -1)
This will sort by Department in ascending order (HR comes before IT), and within each department, it sorts by Salary in descending order:
| Name | Department | Salary |
|---|---|---|
| Charlie | HR | 47000 |
| Alice | HR | 45000 |
| Bob | IT | 52000 |
Key Benefits
- SORTBY is dynamic, so when the source data changes, the sorted data will automatically update.
- It allows sorting by multiple criteria, which is more flexible than the SORT function.
- SORTBY doesn’t require you to alter the original dataset, as it returns a new sorted array.
Summary
The SORTBY function in Excel is a powerful tool for dynamically sorting data based on multiple criteria. It allows flexible sorting by one or more columns, in both ascending and descending order, making it ideal for organizing and analyzing large datasets.