SORT and SORTBY functions in Excel

Introduction

One of the prominent functions introduced in Excel 2021 is the SORT and SORTBY functions that quickly assist users in sorting the dynamic array either in ascending or descending form. These exclusive functions not only enhance organizational productivity but also improve readability when dealing with lengthy text. When dealing with lists in Excel, using these functions enables you to structure information more logically by sorting the data perfectly as desired. This reduces clutter and confusion while allowing for clearer presentation and understanding.

SORT function in Excel

Step 1

Open the worksheet containing the data cells requiring the sorting of data. Write the two-column heading named “Employee Name” and “Salary” in the given table and enter entries as shown below image −

Step 2

Enter the formula “=SORT(Table2[Employee Name])” in the D1 cell and press the “Enter” tab to sort the employee name in ascending form as highlighted in below image −

Suppose we wish to list the employee name in descending form, in that case, the formula becomes =SORT(Table2[Employee Name]),1,-1).

SORTBY function in Excel

The SORTBY function shortlists the data defined in the provided array that relies on the values in a specified range. In this example, users need to shortlist the employee's name by their Salary.

Step 1

The following datasets are utilized to deploy the SORTBY function −

Step 2

Enter the formula “=SORTBY(Table2[Employee Name],Table2[Salary])” in the D2 cell.

Explanation

• The first parameter represents the Employee name first column range “A2−A8” of Table2.

• The second argument represents the second column excluding its heading.

• The employee names are sorted according to their salary. For example, if the employee ‘Abrahim’ has the minimum salary, then he will come in the first position, and so on.

Step 3

Then press the “Enter” tab to obtain the list of employees

Key points while writing SORT and SORTBY functions

• The user must specify the formula carefully and choose the valid array range inside these functions otherwise Errors may arise.

• These functions consider 1 for ascending order and -1 for descending order. The error may occur if the user specifies other integer numbers in the third parameter.

Conclusion

As we reach our conclusion, it is crucial to acknowledge that these advanced techniques effectively ensure agility when working with data in Excel. The users' time saved through these methods can be allocated toward more critical tasks within spreadsheets.