SORTBY Function in Excel - Dynamic Array Function

Posted on:  09/14/2019
SORTBY Function in Excel - Dynamic Array Function

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 (array, by_array, [sort_order], [array/order], ...)

SORTBY Syntax Example



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

  1. XLOOKUP - Excel's new awesome function
  2. Dots in your Gmail address don't matter
  3. SUMIFS Function in Excel
  4. Word's new Rewrite feature

Other Dynamic Array Functions I've covered

  2. SORT

Microsoft's Office Support Site for SORTBY


Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 15 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at or on YouTube at