Rifat Hassan
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.
An Overview of SORT Function in Excel:
Download Practice Workbook
You can download the following practice workbook from the download button below.
Introduction to SORT Function in Excel: Syntax and Arguments
⦿ Objective:
Sorts a given range of cells according to a specific row or column in ascending or descending order.
⦿ Syntax:
The syntax of the SORT function is:
=SORT(array,[sort_index],[sort_order],[by_col])
⦿ Arguments:
| Argument | Type | 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. The default is 1. |
| [sort_order] | Optional | An integer denotes 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) indicates whether to be sorted through rows or columns. The default is FALSE. |
Notes:
[sort_index] is the number of the row or column according to which you want to sort the whole table. It 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.
⦿ Return Value:
Returns a table of cells sorted according to a specific row or column.
⦿ Available in:
Excel for Microsoft 365 | Excel for Microsoft 365 for Mac | Excel for the web | Excel 2021 | Excel 2021 for Mac | Excel for iPad | Excel for iPhone | Excel for Android tablets | Excel for Android phones.
How to Use SORT Function in Excel
- In the following example, we have sorted the table according to column 3 (CGPA) in descending order.
The applied formula is-
=SORT(B5:D9,3,-1,FALSE)
- [sort_order] denotes whether to be sorted in ascending order or descending order. -1 for descending order, 1 for ascending order. It must be either -1 or 1 or fractions that become -1 or when 1 is 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.
The formula we have used is-
=SORT(B14:D18,1,1,FALSE)
- [by_col] is a Boolean value (the default is FALSE.) 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.
- 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.
The formula used here is-
=SORT(B23:D27,3,1,TRUE)
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.
The formula in this example is-
=SORT(B32:D36,2,1,FALSE)
4 Examples of Using SORT Function in Excel
Let’s see some examples of using the SORT function in Excel.
First, look at the following set of data. We have the names, joining dates, and salaries of some employees of a company named Mars Group.
Example 1: Sorting a Group of Employees According to Their Salaries (Sort by Numeric Values)
- Can you sort the employees according to their salaries this time, in descending order?
- Yes. You are right. The formula will be:
=SORT(B5:D21,3,-1,FALSE)
- See, we have sorted the employees according to their salaries in descending order.
Formula Explanation:
- The array argument is the range B5:C21. Sorts the cells from this table.
- The sort_index argument is 3. That means the table will be sorted according to the 3rd column or the 3rd row (Depending 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 3.
- So the formula SORT(B5:D21,3,-1,FALSE) sorts the array B5:C21 by sorting it through the rows according to column 3 in descending order.
Example 2: Sorting a Group of Employees According to the Names (Sort Alphabetically)
- Now we will sort the employees according to their names in ascending order.
- Select a new cell and enter this formula:
=SORT(B5:D21,1,1,FALSE)
- See, we have sorted the employees according to their names in alphabetically ascending order.
Formula Explanation:
- The array argument is the range B5:D21. 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 (Depending 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(B5:D21,1,1,FALSE) sorts the array B5:D21 by sorting it through the rows according to column 1 in ascending order.
Similar Readings
- How to Find Duplicate Values in Excel using VLOOKUP
- Using Excel to Lookup Partial Text Match [2 Easy Ways]
- VLOOKUP and HLOOKUP combined Excel formula (with example)
- How to Use Excel HYPERLINK Function (8 Examples)
Example 3: Sorting a Group of Employees According to Their Joining Dates (Sort by Date)
- Now we will sort the same set of data from examples 1& 2, but this time with the joining dates in ascending order.
- Select a new cell and enter this formula:
=SORT(B5:D21,2,1,FALSE)
See, we have sorted the employees in terms of seniority, which means in ascending order of their joining dates.
Formula Explanation:
- The array argument is the range B5:D21. 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 (Depending 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(B5:D21,2,1,FALSE) sorts the array B5:D21 by sorting it through the rows according to column 2 in ascending order.
Example 4: Use SORT Function to Sort Data in Multiple Columns
Now, we will see a bit more complex example of the SORT function. Let’s introduce the dataset first.
In the following dataset, we have a salary structure for some positions in different departments in a company. We want to sort these data in such a way that we will accumulate the same departments one after another in the column, then we will sort the salaries of all the positions in each category in descending order (largest to smallest).
How to do that?
- To do this, we apply the following formula.
=SORT(B5:D21,{1,3},{-1,-1},FALSE)- Let’s see the result.
Formula Explanation:
- This formula works in the same manner as all other formulas described in this article. We have just applied a simple technique in addition to that.
- We have applied {1,3} as sort_index and {-1,-1} as sort_order.
- As a result, the SORT function will first count the 1st column as an index to sort data. Also, this sort will be in descending order (descending order has no significance in our case. You could use 1 as sort order too).
- Next to this, the SORT function will sort the data again considering the 3rd column as sort_index in descending order.
- This way, we can sort any number of columns together.
A Suitable Alternative to SORT Function for Earlier Versions of Excel
Unless you are a user of Excel 2021 or 365, you don’t have the facility of using the SORT function. But no worries. Excel Custom Sort is pretty much efficient in sorting data. Let’s see how it works.
- We have the same data this time, which we have sorted in example 4.
- Now, select cells B5:D21 and go to the Sort window using the following keyboard shortcut:
- Now, mark the My data has headers box. Then go to Options to select sort orientation (top to bottom/ left to right). You can relate this with the by_col argument of the SORT function.
- The Sort option has one sort level by default. But you can add more levels. Since we will sort the data twice (first by department column, then by salary column), we will add one more level.
- Now, click on Sort by list, and choose a suitable sort index, in our case department. This parameter is comparable to the sort_index of the SORT function.
- Then choose Order A to Z (Ascending order). This is actually the SORT function’s Sort_order argument. Keep the Sort On option as it is.
- Now, click on the Then by list, and choose the next sort index, i.e. Salary. Choose Sort On value and Sort Order again. We have picked Largest to Smallest as Order here.
- Now, press OK.
- Now, look at the result.
You can also generate this result by the following formula if you have the SORT function available.
=SORT(B5:D21,{1,3},{1,-1})Limitations of Excel SORT Function
- 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.
To solve these types of problems, you can use the SORTBY function of Excel.
- You may lose the formatting after using the SORT function. To regain the formatting follow the path below.
Copy parent cells that have lost formatting in the new destination >> Right-click on the upper-left cell of the destination range >> from Paste options, click on the Formatting (R) Icon.
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.
