Using the SORT Function in Microsoft Excel
The SORT function in Excel allows you to sort data without altering the original dataset. It’s a flexible way to manipulate data, and it can be used to sort items in a different location from where the data is originally stored.
Excel SORT Formula Syntax
The formula syntax for the SORT function is:
excelCopy codeSORT(range, index, order, by_column)
Where:
- range: The range of cells to sort (required).
- index: The row or column number to sort by (optional, default is 1).
- order: The sort order (optional, 1 = ascending, -1 = descending; default is ascending).
- by_column: Determines whether to sort by rows or columns (optional, False = rows, True = columns; default is rows).
Basic Examples of the SORT Function
Here are some common examples of how to use the SORT function:
1. Sort a Single Column (Default Ascending Order)
To sort the data in cells A2:A6:
excelCopy code=SORT(A2:A6)
This will sort the data in A2:A6 in ascending order.
2. Sort a Range of Columns (Default Ascending Order)
If you want to sort a wider range, say A2:B6, you can use:
excelCopy code=SORT(A2:B6)
This will sort by the first column (column A) in ascending order, while keeping the rows paired.
3. Sort by a Specific Column (Index Argument)
To sort by the second column (column B), use:
excelCopy code=SORT(A2:B6, 2)
This sorts the range A2:B6 based on the values in the second column (column B) in ascending order.
4. Sort in Descending Order (Order Argument)
To sort in descending order by default (based on the first column), use:
excelCopy code=SORT(A2:B6,, -1)
The -1 specifies descending order, and since we leave the index argument empty, it defaults to sorting by the first column in descending order.
5. Sort by a Specific Column in Descending Order
To sort by the second column in descending order:
excelCopy code=SORT(A2:B6, 2, -1)
This will sort the range A2:B6 by the second column in descending order.
6. Sort by Multiple Columns or by Rows (Using the by_column Argument)
To sort a larger range A2:C6 by the third column (column C), in ascending order, and by rows (default):
excelCopy code=SORT(A2:C6, 3, 1, FALSE)
This will sort based on the “Rating” column (column C), keeping the data rows intact.
Additional Notes:
- INDEX: You can sort by any column or row by specifying the index number. The default is 1 (the first column or row).
- ORDER: Use 1 for ascending order and -1 for descending order.
- BY_COLUMN: Use TRUE to sort by columns (vertical sorting), or FALSE (default) to sort by rows (horizontal sorting).
The SORT function is a versatile tool in Excel for quickly organizing data without modifying the original dataset. It can be particularly useful for data analysis and presenting data in a more readable format.