To sort our data majority of us use the Sort option available on the Home tab or Data tab to sort the data in excel. Do you know that there is a function in excel to sort our data as required. In this article let us learn about the excel SORT function. How to use it with some examples.
What is Excel SORT function
Excel SORT function Sorts the data in a given column or multiple columns or a range in ascending or descending order. This is a dynamic array function. Being dynamic, the function returns an array of values instead of a single value. Also, the results will auto update upon making any change to source data.
Syntax of Excel SORT function
The Syntax of Excel Sort function is as follows
=SORT(array,[sort_index],[sort_order,[by_col])
SORT function takes 4 arguments.
Array – Required – The Column or Range or array to sort.
Sort_Index – Optional – This is a number that determines which row or column to sort by. If this is not entered or left as blank, default index is taken as 1. This means it sorts by the first row or column in the array or data range.
Sort_Order – Optional – This is a number that indicates the Sort Order. Either in Ascending or Descending order.
- 1 – To sort in ascending order
- -1 – To Sort in Descending Order
By_Col – Optional – This is a logical value to indicate the direction of Sort. Whether by Row or by Column. If this is not entered or left as blank, default index is taken as false or 0.
- False or 0 – Sort by Row –
- True or 1 – Sort by Column
How to use SORT function in Excel
Let us now see the different ways to use the SORT function in Excel.
Basic SORT
See the below example to understand the Excel SORT function. We have the Data in Range B2:D17. To sort this data we use the sort function in Cell F3.
B3:D17 is the array or the range of data that we want to sort. Hence we write the sort function as =Sort(B3:D17)
SORT in Ascending and Descending Order
We use the 3rd argument which is the Sort_Order. As mentioned earlier, the default sort option is Ascending order. See the below Images to Understand how to sort the data in Ascending and Descending Order.
In the below image, We use the Sort function in F3 as =SORT(B3:D17,,1). The 2nd argument is left as blank and 1 is used in the 3rd argument. This tells the function to sort the data in Ascending Order.
In the below image, We use the Sort function in F3 as =SORT(B3:D17,,-1). The 2nd argument is left as blank and -1 is used in the 3rd argument. This tells the function to sort the data in Descending Order.
SORT by Specific Column
To Sort the data by a specific column, we use the second argument, which is Sort_Index. Let us see how to sort the data by a column of our choice. Using the same example above, Instead of Column 1 which is Order Priority, let us sort the data by column 2 which is Customer Segment. To do this the Sort formula should be as follows.
=Sort(B3:D18,2). In this 2 refers to the 2nd column in the data range which is Customer Segment. See the below image to understand this.
SORT by Multiple Columns
In order to To Sort by multiple columns, we use the same second argument that is Sort_Index. However, we need to include the columns that we want to sort in the same order in between { }. Lets understand this with an example. See the image below.
In the below image, We want to Sort the data by Column 1 that is Order Priority first and then by Column 3 which is Sales. In order to do this the Sort function should be as follows.
=SORT(B3:D17,{1,3}) – B3:D7 refers to the data range, and {1,3} tells excel to Sort by that is Order Priority first and then by Column 3 which is Sales.
SORT by Multiple Columns in a Specific Order
In order to To Sort by multiple columns in a specific order, we use the second argument and third arguments that is Sort_Index and Sort_order. However, we need to include the columns that we want to sort in the same order in between { } along with the Sort order which is ascending or descending. Lets understand this with an example. See the image below.
In the below image, We want to Sort the data by Column 1 that is Order Priority first in Ascending order and then by Column 3 which is Sales in Descending order. In order to do this the Sort function should be as follows.
=SORT(B3:D17,{1,3},{1,-1}) – B3:D7 refers to the data range, and {1,3} tells excel to Sort by that is Order Priority first and then by Column 3 which is Sales.{1,-1} tells excel that Order Priority Column should be sorted in Ascending Order and Sales Column should be sorted in Descending Order.
Sort Left to Right
By Default Sort is always done on Rows. This means that the position of rows are interchanges when we do a Sort. However, if you have Data in horizontal format, you need to sort from left to right instead of top to bottom. This is called as Sort By Columns.
In order to sort by columns, using the excel sort function, we use the last argument By_Col. Set this argument to True to sort by columns. However, remember that, the 2nd argument which is Sort_Index should be a row number instead of column number.
In Order to Sort the data, we use the sort function as follows. =Sort(C2:N4,3,1,True) – This says Sort the range of C2:N4 by Row 3 in Ascending Order Sorted by Columns. See the below image to understand.
Error Handling while Using Excel SORT Function
Similar to other functions, excel SORT function also returns errors in case if the function is not handled right. Below are some of these errors and the ways to handle them
#Value Error
This is because of invalid Sort_index or Sort_order arguments. In order to rectify this Check if Sort_index number is greater than the number of Columns in the data range. Similarly Sort_Order cannot be anyvalue other than 1 or -1.
#Spill Error
Since Excel SORT function is a dynamic array function, it displays a spill behaviour. This error is because of one or more cells in the spill range are merged or not completely blank. In order to clear this error, remove the filled in cells or clear merge.
#Name Error
SORT function is available only in excel 365 and excel 2021 versions. If you are using a previous version of excel, you will see this error.
#Ref Error
As the name says, this error will come when the source workbook is not open. If you are using the reference from a different workbook, open the source workbook to fix this error. Excel SORT function required both the workbooks to be open.
Conclusion
This article explains and focuses on the basic uses of Excel SORT function. However, there are other advanced ways you can use SORT function(with combination of various other functions.)
What Do you think of the EXCEL SORT function. Do You use the SORT function . Share in the comments.
Do Not Forget to Share this article with your Friends !!!