There can be a number of ways to sort the data in excel. We can do it either by using Excel sorting features or we can use the new SORT Function in Excel. The SORT function is a type of Dynamic Array Functions that returns a range of sorted values.
Let us see how the function works.
What is SORT Function in Excel?
As the name of the function must have suggested to you, the SORT Function of excel can sort the range of cells either alphabetically or in ascending or descending order. It can sort the range based on any one row or column.
The function returns a sorted range of cells which is why it is one of the most useful Dynamic Array Functions. The SORT formula is compatible with Microsoft office 365 and Excel 2022.
Syntax and Arguments
The following points will explain to you, the function arguments to pass to the SORT Function in Excel.
- array – It is the range of cells that we want to sort.
- [sort_index] – This is the column or row number on the basis of which we want to sort our entire array of cells. Since the arguments in square brackets are optional arguments, if we do not specify the sort index then the default value would be 1.
- [sort_order] – It is 1 for ascending order and -1 for descending order, If we want to sort alphabetically, then the sort index must specify the column or row containing text values. The default value of sort_order is 1(ascending order).
- [by_col] – The argument asks whether we want to rearrange and sort the rows or columns. By default, it is 0 ( rows will be arranged ).
The usage of this function is better explained with the help of some examples. So let’s move to practical usage of the SORT Function of excel.
Example 1 – Sorting in Ascending order based on the first Column
Let us say we have a list containing student roll numbers, names, and marks. The problem is with roll numbers as they are not sorted correctly as follows:-
We want to sort this student marks list based on the Roll Numbers. Copy the three column headings in range E1:G1and type the following formula in cell E2.
When you type the formula in cell E2:-
and then hit the enter key. The sorted list spills into the range E2:G7 as follows:-
Explanation – We have passed the array of cells A2:C7 to the SORT Function so as to sort the entire list of students. We supply the sort_index as 1 (default value) which means that the first column ( r.no ) will become the base for sorting. The third argument sort_order is also 1 (default value) to get the roll numbers sorted in ascending order. The last argument is 0 (default value) as we need to rearrange the rows based on the roll number.
Since we used the default values of three optional arguments, the formula can also be shortened to:-
This formula will give the same sorted list. The range returned by this SORT Function is dynamic and we can
Example 2 – Sorting Alphabetically based on Second Row
This example explains the flexibility of the SORT function. Let us say we have the following set of data.
So you can see that there are three rows namely Emp ID, Emp Name, and Salary. We want to sort the Employees data based on the Employee names (alphabetically)
Copy the row headings in the range A5:A7 and use this formula in cell B5.
Hit the enter key and you will see that the employee data has been arranged in alphabetical order of Employee names as follows:-
Explanation – We have passed the range B1:G3 as an array argument to sort this range. sort_index is 2 as we want to sort the data based on Emp name which is the second row. sort_order is 1 as this is an alphabetical sort and by_col is 1 as the columns (B, C, D, E, F, G) are shuffled to get the sorted order of employee names.
This brings us to the end of the blog.
Thank you for reading.