How to Use the SORT Function in Excel
In Excel and Google Sheets, the SORT function is an invaluable tool for organizing a range or array of data. It allows sorting based on the values in one or more columns and can order data either ascendingly or descendingly, based on the criteria you define.
Basic Syntax:
The syntax for the SORT function is consistent across both Excel and Google Sheets:
=SORT(range, [sort_index], [sort_order], [range2], [sort_index2], [sort_order2], ...)range: The data range or array to be sorted.sort_index: Specifies the column number or row number within the range on which to base the sort. The default is 1.sort_order: Determines the sort direction; 1 for ascending or -1 for descending order, with a default of ascending order.range2,sort_index2,sort_order2: Allows for additional ranges, sort indices, and orders for secondary sorting criteria.
Example Usage:
Consider a dataset listing student scores across different subjects:
| Student Name | Math Score | Science Score |
|---|---|---|
| John | 85 | 90 |
| Amy | 78 | 88 |
| David | 92 | 80 |
To sort the student data by Math scores in descending order, the formula is:
=SORT(A2:C4, 2, -1)This results in data arranged with the highest Math score at the top:
| Student Name | Math Score | Science Score |
|---|---|---|
| David | 92 | 80 |
| John | 85 | 90 |
| Amy | 78 | 88 |
Applying Multiple Sorting Criteria:
To sort first by Science scores and then by Math scores, both in descending order, use this formula:
=SORT(A2:C4, 3, -1, 2, -1)This approach first sorts the data by Science scores. Then, ties in Science scores are broken based on Math scores:
| Student Name | Math Score | Science Score |
|---|---|---|
| John | 85 | 90 |
| Amy | 78 | 88 |
| David | 92 | 80 |
This demonstrates how the SORT function can be leveraged to efficiently organize data in Excel and Google Sheets according to specific sorting parameters.
More information: https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c