SORTBY Function in Excel - Dynamic Array Function

Posted on: 09/14/2019

The SORTBY Function is one of six new dynamic array functions in Microsoft Excel. SORTBY function allows you to sort one range, based on another range. Example: You have a list of employees with the following fields: Name, Department, and Salary. You want to sort it on a separate workbook or file, but those users don't need to see salary. You wish to sort by department in ascending order and then salary in descending order. You can do that with the SORTBY function. You are using Salary as a sort field but other users can't see salary.

SORTBY Syntax

SORTBY (array, by_array, [sort_order], [array/order], ...)

SORTBY Syntax Example

SORTBY(A2:B10,C2:C10,-1)

SORTBY Arguments

array - Range or array to sort.
by_array - Range or array to sort by.
sort_order - [optional] Sort order. 1 = ascending (default), -1 = descending.
array/order - [optional] Additional array and sort order pairs.

Example 1 of SORTBY

In this first example, I have a range from A1 to C10 showing employee's name, department and salary. I want to sort and only display the names and department, but I want to sort by salary in descending order. In the results, salary will not be shown, but it is the sort order. To accomplish this, I'll use the SORTBY function.

The formula in cell G2 shows A2 to B10 as the range, followed by the sort column which is salary, C2 to C10. In the formula, the negative 1 means descending order. A positive one would be ascending.

I did this on the same worksheet, but I could have done this on another worksheet or workbook.

Example 2 of SORTBY

With SORTBY, you can sort by multiple columns. I have an Excel table named Reps with the four fields: Rep, Customer, State, and Revenue in A2 to D14. I want to sort by state in ascending order then by revenue in descending order, but revenue will not show in the sorted data. To accomplish this, I used SORTBY in cell O2.

The formula is =SORTBY(Reps[[Rep]:[State]],Reps[State],1,Reps[Revenue],-1)

The first argument tells you what I'm sorting: I have a table Reps Table and the fields Rep through State. The second argument is the first sort priority which is State, and it is ascending. Notice the number 1 after it. The final argument is the next sort priority which is Revenue and it is descending which is a -1 (negative 1).

Latest YouTube videos

XLOOKUP - Excel's new awesome function https://youtu.be/1sSje27jnTA
Dots in your Gmail address don't matter https://youtu.be/2FolZ1Q9bYw
SUMIFS Function in Excel https://youtu.be/qrYim5WZhqk
Word's new Rewrite feature https://youtu.be/UQ2yul_4GAI

Other Dynamic Array Functions I've covered

Microsoft's Office Support Site for SORTBY

https://support.office.com/en-us/article/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.