Excel SORT Function is a powerful tool that enables users to sort data in ascending or descending order based on a specified criterion. Sorting data helps to better organize and analyze data, making it easier to understand and draw insights from. The SORT Function eliminates the need for manual sorting, saving users a considerable amount of time and effort.
Excel SORT Function Summary
Excel’s SORT function is a powerful tool for sorting data in ascending or descending order within a range or array. It can sort values by one or more columns, and returns a dynamic array of sorted results. By default, the SORT function sorts by the first column in ascending order, but it can be customized to fit specific sorting needs.
SORT Function Purpose
The purpose of the Excel SORT Function is to sort data in a specified range or array based on one or more columns, rows, or criteria.
SORT Function Arguments
The SORT Function accepts the following arguments:
- array: the range of cells or array to be sorted.
- [sort_index]: the column or row to be used as the basis for sorting. This argument is optional if the array is one-dimensional.
- [sort_order]: the order in which to sort the data (ascending or descending). This argument is also optional, and the default value is ascending.
SORT Function Return Value
The SORT Function returns an array of sorted data.
SORT Function Syntax
=SORT(array,[sort_index],[sort_order])Excel SORT Function Examples
The Excel SORT function is a powerful tool that can help you sort data in ascending or descending order with ease. Whether you’re dealing with simple or complex datasets, the SORT function can help you rearrange your data quickly and efficiently.
Sorting data in a single column
Suppose we have a list of student grades in column A, and we want to sort them in descending order.
We can use the following formula in another column to achieve this:
=SORT(A1:A10,1,-1)Sort a list of names alphabetically
Suppose you have a list of names in column A of a spreadsheet, and you want to sort them alphabetically. You can use the SORT function to do this.
Here’s the formula you would use:
=SORT(A2:A5)
This will sort the names in ascending order, from A to Z. If you want to sort them in descending order, from Z to A, you can add a second argument to the function:
=SORT(A:A, , FALSE) Note that the second argument is left blank, indicating that the function should sort by the first column. The third argument, FALSE, specifies that the sorting should be in descending order.
Sort a table by multiple columns
Suppose you have a table with three columns: Name, Age, and Gender. You want to sort the table first by Age in ascending order, and then by Name in descending order.
Here’s how you can do that:
=SORT(Table1, 2, 1, -1) - The first argument is the range of the table you want to sort, which in this case is named “Table1”.
- The second argument, 2, specifies that you want to sort by the second column, which is Age.
- The third argument, 1, specifies that the sorting should be in ascending order.
- The fourth argument, -1, specifies that the sorting for the next column, Name, should be in descending order.
Sort a range based on another range
Suppose you have two columns of data: one with names, and one with corresponding scores. You want to sort the names alphabetically, but you want the scores to stay with their corresponding names.
Here’s how you can do that:
=SORT(A2:B10, 1, TRUE) - The first argument is the range of the data you want to sort, which includes both columns A and B.
- The second argument, 1, specifies that you want to sort by the first column, which is the names.
- The third argument, TRUE, specifies that the sorting should be in ascending order.
- This will sort the names alphabetically, and since the scores are in the second column, they will move with their corresponding names.
Sort a range by the absolute value of the numbers
Suppose you have a list of numbers in column A, some of which are negative. You want to sort the list by the absolute value of the numbers.
Here’s how you can do that:
=SORT(A:A, ABS(A:A), 1)- The first argument is the range of the data you want to sort, which is just column A.
- The second argument is the array of values to sort by, which is the absolute value of the numbers in column A.
- The ABS function returns the absolute value of a number.
- The third argument, 1, specifies that the sorting should be in ascending order.
Sort by score With SORT
Let’s say we have a table of student scores in Excel.
To sort our table by score in descending order, we can use the following formula in a new range of cells:
=SORT(A2:B6, 2, -1)This sorts the range A2:B6 (our original table) by the second column (score) in descending order. The result is a new table with the same headers, but the rows are sorted by score:
As you can see, the SORT function makes it easy to sort tables in Excel by any column or combination of columns. This can be very useful for analyzing data or presenting it in a more readable format.
These are just a few examples of how you can use the SORT function in Excel. With its flexibility and power, the SORT function can make data analysis and organization much easier and efficient.
Sorting by Multiple Levels in Excel Using the SORT Function
While the SORTBY function in Excel allows for sorting by multiple levels, the SORT function doesn’t offer this functionality directly. However, by utilizing array constants for sort_index and sort_order, you can sort data by more than one level using the SORT function.
Excel SORT Function Notes
- The SORT Function can only sort up to 10,000 rows.
- If the range includes empty cells, the SORT Function will treat them as having a value of zero.
- The SORT Function is only available in the latest version of Excel.