SORTBY formula in Excel sheets explained
Excel SORTBY formula is a powerful formula that allows you to sort data in a flexible and dynamic way, making it easier to organize and analyze your data. Read this article to explore the SORTBY formula and learn how to use it effectively.
What is the SORTBY formula?
Excel’s SORTBY function is used to sort a range or table of data according to the values in another range or array. It is a dynamic sorting formula that creates a sorted list without modifying the original data. SORTBY is particularly useful when you want to maintain the integrity of your data while displaying it in sorted order.
The SORTBY function has the following arguments:
- array: a range of data to sort. It must have compatible dimensions or SORTBY will return #VALUE!
- by_array: a range or array based on which you want to sort the data in the array. This argument can be only one row or one column.
- sort_order: An optional argument that specifies the sorting order. The sort_order argument can be: 1 for ascending order (by default) and -1 for descending order.
The syntax of the SORTBY function looks like the following:
=SORTBY(array, by_array, [sort_order], [array/order], …)
Now, let’s look at how to insert the SORTBY formula in ONLYOFFICE Spreadsheet Editor.
How to insert SORTBY formula
The SORTBY function is accessible from both the Home and Formula tabs on the top toolbar.
On the Home tab, you can use the Insert function button.
Open the Insert Function window that contains all the available functions classified by category. Use the search box to find the SORTBY function. Click OK.
On the Formula tab, go to the Lookup and reference functions and find SORTBY in the drop-down list.
Once you have chosen the function, the Function arguments window will appear. Enter the necessary values of each argument.
You can enter the function arguments either manually or by clicking the gridline icon and selecting a cell or cell range to be included as an argument.
The function result will be displayed below. When all the arguments are specified, click the OK button in the Function Arguments window.
SORTBY formula example with one argument
Suppose you have a table of marketing plan. You want to sort this table by the company profit for each month in descending order. Here’s how you can do that using the SORTBY function.
First, select an empty cell where you want the sorted data to appear.
Then, go to the Formula tab on the top toolbar —> Lookup and reference —> SORTBY formula. Define the arguments, where:
- C1:N2 is the range of data you want to sort.
- C2:N2 is the range by which you want to sort the data (in this case, by company profit).
- -1 indicates a descending sort order (from highest to lowest).
SORTBY formula example with two arguments
Let’s consider an example of using the SORTBY formula with two arguments to sort data by several criteria. In this example, we have a list of employees, their ages, and their department. We want to sort this data first by age (in ascending order) and then by department.
To sort this data based on two criteria, select an empty cell where you want the sorted data to appear.
Then, enter the SORTBY formula arguments:
- A3:C12 is the range of data you want to sort.
- Argument 1: B3:B12 is the age range; 1 indicates a descending sort order.
- Argument 2: C3:C12 is the range of the department; 1 also indicates a descending sort order.
Click OK.
The SORTBY function in Excel is a valuable tool for sorting data dynamically based on one or more criteria. Whether you need to sort financial data, inventory lists, or any other dataset, SORTBY provides a convenient way to arrange your information for analysis and presentation.
Analyze your data with the SORTBY function and try other ones online by creating an ONLYOFFICE DocSpace account or by downloading free desktop apps:
Discover what’s new in ONLYOFFICE Docs v7.5:
