SORTBY Formula in Excel

 

What does it do?

Sorts a table based on the column(s) specified

Formula breakdown:

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

What it means:

=SORTBY(data to be sorted, by which column to sort first, [by which column to sort afterwards], …)

---

Did you know that you can now sort your table data with an Excel Formula? Yes you can! It is definitely possible now with Excel’s SORTBY Formula. It also allows you to sort by multiple columns as well. It is a new formula introduced in Office 365 released in 2018!

We have a person list that we want to sort by Gender (ascending order) and then by Age (ascending order).

Do take note that in specifying the sorting order, 1 represents ascending order, -1 represents descending order.

I explain how you can do this below:

STEP 1: We need to enter the SORTBY function in a blank cell:

=SORTBY(

 

STEP 2: The SORTBY arguments:

array

What is the data to be sorted?

Select the cells containing the person data, do not include the headers:

=SORTBY(B9:D14,

by_array1, sort_order1

Which column will be used to sort first?

Select the cells containing the gender column, then type in 1 for it to be ascending order.

=SORTBY(B9:D14, B9:B14, 1,

by_array2, sort_order2

Which column will be used to sort next?

Select the cells containing the age column, then type in 1 for it to be ascending order.

=SORTBY(B9:D14, B9:B14, 1, D9:D14, 1)

Now it gets sorted magically!

 

How to Use the SORTBY Formula in Excel