How to Use the SORTBY Function in Excel

Excel's SORTBY function: a tool for efficient sorting

Microsoft Excel provides a variety of data sorting methods, and the SORTBY function is undoubtedly the simplest and most convenient one. It allows you to dynamically sort data based on multiple arrays while maintaining the integrity of the original data.

SORTBY function syntax

The syntax of the SORTBY function is concise and clear:

 <code>=SORTBY(a,b¹,b²,c¹,c²...)</code>

in:

• a (required) is the region or array to be sorted;
• b¹ (Required) is the first region or array to sort by;
• b² (optional) is the sort order of b¹ ;
• c¹ and c² (optional) are the second arrays sorted by and their sort order, and can support up to 64 such pairs.

Omit the sort order parameter or enter 1 and the data will be arranged in ascending or alphabetical order. Enter -1 and arrange it in descending or inverse alphabetical order.

Parameters a , b¹ , c¹ , d¹ etc. must be compatible in dimensions. For example, if the region selected by the a parameter has 10 rows, the array sorted by must also have 10 rows.

Single-column sorting

Let's see how to sort data by a single column using the SORTBY function.

First, prepare the area in the worksheet for storing the sort results. In this example, we copy-paste the column header into cells G1 to K1 and adjust the font size and alignment of cells G2 to K25 to allow vertical expansion to match the format of the original data.

Multi-column sorting

One of the major advantages of the SORTBY function over the SORT function is that you can select multiple variables as the ordering basis.

Continue with the example above, assuming you still want to sort individuals by total points, but want all those who reach the goal to be ranked ahead of those who fail to reach the goal. In other words, you want to sort by the "Met?" column first, and then by the "Points" column.

After preparing the cell where the data is stored (including copying the column title and deleting any SORTBY functions entered before), enter in the G2 cell:

 <code>=SORTBY(T_Targets,T_Targets[Met?],-1,T_Targets[Points],-1)</code>

in:

• T_Targets is an array;
• T_Targets[Met?] is the first sorted by column in the array;
• -1 Instruct Excel to sort T_Targets[Met?] in anti-alphabetical order (Y before N);
• T_Targets[Points] is the second sorted by column;
• -1 Instruct Excel to sort T_Targets[Points] in descending order.

After pressing Enter, you will get the following results. Note how the sum of points for those who have reached the target is grouped together and displayed in descending order, as are those who have not reached the target.

SORTBY is used in combination with FILTER function

The SORTBY function can be used in conjunction with other functions in Excel to further optimize the results. Specifically, we will show how to use SORTBY with FILTER while sorting and filtering the data.

Suppose your goal is to generate a result sorted by the "Points" column, but only display people with a total score of more than 55 points.

After creating the result column header in row 1, enter in cell G2:

 <code>=FILTER(SORTBY(T_Targets,T_Targets[Points],-1),SORTBY(T_Targets[Points],T_Targets[Points],-1)>55)</code>

Then press Enter.

Let's break down this formula and learn more about how it works.

Comparison between SORT and SORTBY

Both SORT and SORTBY functions are used to reorganize data based on specific values, but they work slightly differently:

feature	SORT function	SORTBY function
Formula syntax	The SORT function allows you to define arrays, index numbers of columns to be sorted, sorted order, and options to sort by row rather than by column.	The SORTBY function allows you to define regions, as well as up to 64 sorted arrays - sequential pairing.
Sort level	When using SORT functions, you can only sort by one row or one column.	When using the SORTBY function, you can sort by up to 64 rows or columns.
Parameter reference	The rows or columns to be sorted are referenced by an index number, which means that if you add or delete rows or columns in the original data, this index number may be outdated.	Since the array to be sorted can be a named reference (such as column headings), this reference will remain the same even if you add or delete columns in the original data.

In addition to the SORTBY function, another way to create an actionable copy of the dataset (while keeping the original data unchanged) is to create a pivot table. This powerful Excel tool allows you to aggregate and reorganize data in a variety of ways, meaning you can quickly generate calculation results and analyze data without using complex formulas.

The above is the detailed content of How to Use the SORTBY Function in Excel. For more information, please follow other related articles on the PHP Chinese website!