How to Use the SORTBY Function in Excel

There are galore ways to benignant information successful Microsoft Excel, and 1 of the simplest and astir convenient is the SORTBY function. It lets you dynamically benignant your information by much than 1 array portion preserving the integrity of the archetypal data.

The SORTBY Syntax

The syntax for the SORTBY relation successful Excel is some straightforward and logical:

=SORTBY(a,b¹,b²,c¹,c²...)

where

• a (required) is the scope oregon array you privation to sort,
• b¹ (required) is the archetypal scope oregon array by which you privation the information to beryllium sorted,
• b² (optional) is the benignant bid for b¹, and
• c¹ and c² (optional) are the 2nd array and bid by which you privation to benignant the data, and you tin person up to 64 of these pairings overall.

If you omit the benignant bid arguments oregon benignant 1, the information volition beryllium arranged successful ascending oregon alphabetical bid by the specified scope oregon array. On the different hand, benignant -1 to benignant the information successful descending oregon reverse-alphabetical order.

Arguments a, b¹, c¹, d¹, and truthful connected indispensable beryllium compatible successful presumption of their dimensions. For example, if the scope selected for statement a is 10 rows down, the arrays by which you privation to benignant the information indispensable besides beryllium 10 rows down.

Using SORTBY to Sort By One Column

Let's look astatine however the SORTBY relation tin beryllium utilized to benignant immoderate information by 1 column.

In this example, accidental you've been handed an Excel array named T_Targets, and you've been asked to benignant the information by the Points column. At the aforesaid time, you request to permission the archetypal array unchanged, arsenic the accusation is structured successful an bid that indispensable stay unaltered.

The champion mode to bash this is to usage the SORTBY function.

The archetypal measurement is to hole the country successful your spreadsheet wherever the information volition go. In this example, I've copied and pasted the file headers to cells G1 to K1, and I've adjusted the font size and alignment of cells G2 to K25 (to let for vertical growth) to lucifer the formatting of the archetypal data.

Next, successful compartment G2, type:

=SORTBY(T_Targets,T_Targets[Points],-1)

where

• T_Targets is the array,
• T_Targets[Points] is the sanction of the file containing the information that the array volition beryllium sorted by, and
• -1 tells Excel to benignant T_Targets[Points] successful descending order.

When you property Enter, you'll spot the effect reorder your information by the Points column. A bluish enactment astir the effect reminds you that it's a dynamic array (in different words, the effect has spilled from compartment G2 to the different cells).

You tin past use conditional formatting to the effect to assistance further analysis. For example, you mightiness privation to colour the rows of radical who person met their people green, and those who haven't red.

Using SORTBY to Sort By More Than One Column

One payment of utilizing SORTBY alternatively of SORT successful Excel is that you tin take much than 1 adaptable by which to benignant your data.

Staying with the aforesaid illustration arsenic above, let's present presume that portion you inactive privation to benignant the individuals by their points total, you privation everyone who has met their people to beryllium supra those who haven't successful the table. In different words, you privation to benignant the information by the Met? column, and then by the Points column.

After preparing the cells wherever the information volition go, including duplicating the file headers and deleting immoderate erstwhile SORTBY relation you typed, successful compartment G2, type:

=SORTBY(T_Targets,T_Targets[Met?],-1,T_Targets[Points],-1)

where

• T_Targets is the array,
• T_Targets[Met?] is the archetypal sort-by file successful that array,
• -1 tells Excel to benignant T_Targets[Met?] successful reverse-alphabetical bid (the Ys supra the Ns),
• T_Targets[Points] is the 2nd sort-by column, and
• -1 tells Excel to benignant T_Targets[Points] successful descending order.

Here's the effect you'll get erstwhile you property Enter. Notice however the points totals for radical who have met their people are grouped unneurotic and displayed successful descending order, and the aforesaid goes for those who haven't met their target.

Using SORTBY With FILTER

The SORTBY relation tin beryllium utilized with different functions successful Excel to further refine the result. Specifically, I'll amusement you however to usage SORTBY with FILTER to benignant and filter the information successful a caller array astatine the aforesaid time.

Let's accidental your purpose present is to nutrient a effect that sorts the information by the Points column, but lone shows radical who scored much than 55 points overall.

After creating file headers for the effect successful enactment 1, successful compartment G2, type:

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

and property Enter.

Let's interruption this look down to research successful much item however it works.

The FILTER relation requires 2 arguments: the array and what to see successful the filter. So, the portion of the look that reads:

SORTBY(T_Targets,T_Targets[Points],-1)

is the FILTER function's array argument. However, by nesting SORTBY successful this argument, you're telling Excel that the array is the array named T_Targets, but with the information sorted by the Points file successful descending order.

Then, the portion that reads:

SORTBY(T_Targets[Points],T_Targets[Points],-1)>55

is the FILTER function's inclusion criteria. However, due to the fact that the array statement uses sorted data, you request the inclusion criteria to beryllium sorted arsenic well. In this case, the array containing the information to benignant is the Points column, and the aforesaid file is the array you utilized earlier to benignant the information successful descending order. Then, the last portion of the inclusion criteria tells Excel to lone see values successful this sorted array that are greater than 55.

SORT vs. SORTBY successful Excel

While the SORT and SORTBY functions are some intended to reorganize information based connected definite values successful the data, they enactment successful somewhat antithetic ways:

---

Another mode to make a caller mentation of a dataset that you tin manipulate—all portion leaving the archetypal information intact—is to create a PivotTable. This almighty Excel instrumentality lets you summarize and reorganize your information successful assorted ways, meaning you tin rapidly make calculations and analyse your figures without utilizing analyzable formulas.