SORTBY Function in Excel - Dynamic Array Function

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 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

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

Other Dynamic Array Functions I've covered

  1. UNIQUE https://youtu.be/P5QQPvDeFoo
  2. SORT https://youtu.be/_MtUSti6zm8
  3. FILTER https://youtu.be/OHVTyfHQhk4

Microsoft's Office Support Site for SORTBY

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

 

Chris Menard

Chris is a Microsoft Office Master. He trains corporate clients in Microsoft Excel, PowerPoint, Word, and Outlook. Menard is a speaker for the Georgia Society of CPAs and a senior lecturer at the University of Georgia Terry College of Business. Menard's YouTube channel has over 600 technology videos. Chris works full-time as a Training Specialist for a global law firm. His office is in midtown, Atlanta.

Categories