What is the difference between SORT() and SORTBY() in Excel?

Among the dynamic array functions of Excel are two that sound awfully familiar — SORT() and SORTBY(). Their titles make it pretty clear what they do… they sort data. But how do they do it differently? That’s what this post will explore.

Another way to say sort is to order a dataset. And we will demonstrate this on data of those who bring order to the galaxy… superheroes! This superheroes dataset from Kaggle contains information about hundreds of superheroes from various publishers.

Let’s bring “order” to this dataset and understand SORT() vs SORTBY().

It’s similar to SUMIF() and SUMIFS().

SORT() orders the entire dataset by one criteria

The SORT() function is the one-trick pony. The syntax is as follows:

=SORT(array,[sort_index],[sort_order],[by_col])

For our example, let’s sort the heroes dataset by height, descending. Go ahead and create a new worksheet to display the results.

SORT() and SORTBY() return the results of a table, but not the corresponding column labels. To do that, go ahead and place this formula on the first row of your worksheets:

=heroes[#Headers]

Now it’s time to sort the data.

Probably the trickiest part of SORT() is the second argument where you pass an index number a la “how many columns to look over” in VLOOKUP(). The thing is, I don’t know how many columns into the dataset the Height column is, and I don’t want to hard-code it in and have the formula break later.

So I am going to use the INDEX() function to dynamically return the column position of the header called Height. I also want the results ordered descendingly, so that makes the third argument -1.

=SORT(heroes, MATCH("Height", heroes[#Headers]), -1)

You should now see something like this:

You can continue experimenting with the SORT() function. In fact, you’d be my hero to do so (pun intended!). But they are all going to look pretty much the same. SORT() will always order a range of data by one of the columns/rows in that range.

SORTBY() orders an array by another array

SORTBY() is far more dynamic and in my opinion, easier to use. The SORTBY() syntax looks kind of like SUMIFS() with its multiple criteria:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) 

Let’s see what our height, descending formula would look by in SORTBY()ese:

=SORTBY(heroes, heroes[Height], -1)

You can take a look at the exercise file, but the results of this function are the same as SORT() and we didn’t have to go wild with indexing.

Sorting by multiple criteria

What’s more, SORTBY() makes it possible to sort the data by multiple criteria. We can choose whether each is ascending and descending separately, as well. Let’s not only order the data by Height descending, but also Gender ascending:

=SORTBY(heroes, heroes[Height], -1, heroes[Gender], 1)

Rinse and repeat!

Sorting by another column without printing it

Where things get really interesting with SORTBY() is you can use to sort a range by another range, without even including the sort range! Here’s what I mean: let’s say we want a list of superhero names by height, descending. We don’t care about anything else but the list of names.

Rather than passing the entire heroes table into that first argument, we just keep the name column. The rest should look familiar:

=SORTBY(heroes[name], heroes[Height], -1, heroes[Gender], 1)

Here you will just the one column returned to you:

Like the other dynamic array results, this does not come with the column header. If you’d like to have one, and keep it dynamic, I’d suggest an input cell that controls both the column header along with the SORTBY() result.

Conclusion

While sharing similar purposes, SORT() offers a more single-minded purpose: sorting a dataset by one and only one index number. On the other hand, SORTBY() lets you sort by multiple criteria without even printing those criteria columns in the results.

At the time of writing I am looking for some examples that can only be done in SORT(), follow the conversation here or let me know yourself!

What questions do you have about SORT() and SORTBY()? Have you used dynamic arrays yet? What do you think? Let me know in the comments.