How to Use SORT Function in Excel (4 Examples)

SORT Function Quick View

While working in Excel, we often have to sort a range or a table according to some specific values. Excel provides a function called SORT which you can use to sort any range of cells according to a specific row or column.

Today I will be showing how you can use the SORT function of Excel to sort a range of cells according to a specific column.

SORT Function of Excel (Quick View)

SORT Function Quick View

Download Practice Workbook

Excel SORT Function: Syntax and Argument

Summary

  • Sorts a given range of cells according to a specific row or column in ascending or descending order.
  • Available in Office 365 only.

Syntax

SORT Function Syntax

The syntax of the SORT function is:

=SORT(array,[sort_index],[sort_order],[by_col])

Argument

Argument Required or Optional Value
array Required The range of cells that is to be sorted.
[sort_index] Optional The number of the specific row or column according to which you want to sort the table. Default is 1.
[sort_order] Optional An integer denoting whether to be sorted in ascending order or descending order. -1 for descending order, 1 for ascending order. Default is 1 (Ascending order)
[by_col] Optional A boolean value (TRUE or FALSE) indicating whether to be sorted through rows or columns. Default is FALSE.

Notes:

  • [sort_index] is the number of the row or column according to which you want to sort the whole table.

(Must be a number. If it is a fraction, Excel will convert it to an integer, but will raise #VALUE! error if it is not a number.)

For example, in the following example, we have sorted the table according to column 3 (CGPA) in descending order.

Sort_index of SORT Function

  • [sort_order] denotes whether to be sorted in ascending order or descending order. -1 for descending order, 1 for ascending order.

(Must be either -1 or 1 or fractions that become -1 or when 1 converted to integers, like 1.3 or -1.7, etc. If it is not, Excel will raise #VALUE! error.)

For example, here we have sorted the table according to column 1 (Student ID) in ascending order.

Sort_order of SORT Function

  • [by_col] is a boolean value indicating whether to be sorted through rows or columns.

(You can use numbers in place of Boolean values. In that case, only 0 will denote FALSE, all other numbers will denote TRUE.)

Default is FALSE.

That means, by default the table is sorted through rows. But if you set it as TRUE, the table will be sorted through columns.

For example, in the following figure, we have sorted the table in ascending order through the columns, according to row number 3.

By_col Argument of SORT Function

[The ascending order of row number 3 is 3.87… 177… Joseph Hick. The whole table is sorted accordingly].
  • The SORT function can sort both in alphabetical order and in numerical order.

For example, in the following figure, we have sorted the table according to the student names in alphabetically ascending order.

SORT Function in Alphabetical Order

Return Value

Returns a table of cells sorted according to a specific row or column.

Excel SORT Function: 4 Examples

Let’s see some examples of using the SORT function in Excel.

1. Sorting a Group of Students According to Their Marks

Look at the data set below. We have the names of some students and their marks in Mathematics of a school named Sunshine Kindergarten.

Data Set of Marks of Students

Now we will sort the students according to their marks in descending order.

Select a new cell and insert this formula:

=SORT(B4:C20,2,-1,FALSE)

SORT Function with Marks of Students

See, we have sorted all the students according to their marks in Mathematics in descending order.

Explanation of the Formula

  • The array argument is the range B4:C20. Sorts the cells from this table.
  • The sort_index argument is 2. That means the table will be sorted according to the 2nd column or the 2nd row (Depends on the by_col argument).
  • The sort_order argument is -1. That means the table will be sorted in descending order.
  • The by_col argument is FALSE. That means the table will be sorted through rows, not through columns, according to column number 2 (Marks in Mathematics).
  • So the formula SORT(B4:C20,2,-1,FALSE) sorts the array B4:C20 by sorting it through the rows according to column 2 in descending order.

2. Sorting a Group of Employees According to the Names

Now, look at this new set of data. We have the names, joining dates and salaries of some employees of a company named Mars Group.

Employee Data Set for SORT Function

Now we will sort the employees according to their names in ascending order.

Select a new cell and enter this formula:

=SORT(B4:D20,1,1,FALSE)

SORT Function with Names

See, we have sorted the employees according to their names in alphabetically ascending order.

Explanation of the Formula

  • The array argument is the range B4:D20. Sorts the cells from this table.
  • The sort_index argument is 1. That means the table will be sorted according to the 1st column or the 1st row (Depends on the by_col argument).
  • The sort_order argument is 1. That means the table will be sorted in ascending order.
  • The by_col argument is FALSE. That means the table will be sorted through rows, not through columns, according to column number 1 (Employee Name).
  • So the formula SORT(B4:D20,1,1,FALSE) sorts the array B4:D20 by sorting it through the rows according to column 1 in ascending order.

3. Sorting a Group of Employees According to Their Joining Dates

Now we will sort the same set of data from example 2, but this time with the joining dates in ascending order.

Select a new cell and enter this formula:

=SORT(B4:D20,2,1,FALSE)

SORT Function with Dates

See, we have sorted the employees in terms of seniority, which means in ascending order of their joining dates.

Explanation of the Formula

  • The array argument is the range B4:D20. Sorts the cells from this table.
  • The sort_index argument is 2. That means the table will be sorted according to the 2nd column or the 2nd row (Depends on the by_col argument).
  • The sort_order argument is 1. That means the table will be sorted in ascending order.
  • The by_col argument is FALSE. That means the table will be sorted through rows, not through columns, according to column number 2 (Joining Date).
  • So the formula SORT(B4:D20,2,1,FALSE) sorts the array B4:D20 by sorting it through the rows according to column 2 in ascending order.

4. Sorting a Group of Employees According to Their Salaries

Now the final task. Can you sort the employees according to their salaries this time, in descending order?

Yes. You are right. The formula will be:

=SORT(B4:D20,3,-1,FALSE)

SORT Function with Salaries

See, we have sorted the employees according to their salaries in descending order.

Limitations of the Excel SORT Function

  • You can not use the SORT function to sort some columns or rows which are not adjacent.

For example, in the data set of example 2, you can not sort only the employee names and the salaries because they are not adjacent.

But you can sort the employee names and the joining dates, or the joining dates and the salaries.

If you ever need to sort any columns that are not adjacent, you can follow the procedure described in this article.

  • You can not use the SORT function if you want to sort any range of cells according to a row or column outside that range.

For example, in the data set of example 2, you can not use the SORT function if you want to sort only the employee names and the joining dates, according to the salaries.

To solve these types of problems, you can use the SORTBY function of Excel. Or use the procedure described in this article.

Common Errors with Excel SORT Function

Error When They Show
#VALUE! This shows when an argument is of the wrong data type, or if any argument is out of the range. For example, if the sort_index is a character rather than a number. Or the sort_order is anything other than -1 or 1.

Conclusion

Thus you can use the SORT function of Excel to sort any range of cells according to a specified row or column according to your wish. Do you have any questions? Feel free to ask us.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo