An Easy Way to Sort Data When There is Only One Sort Field
Excel SORT Function
Summary
The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.
Syntax
=SORT (array, [sort_index], [sort_order], [by_col])
Arguments
- array - Range or array to sort.
- sort_index - [optional] Column index to use for sorting. Default is 1.
- sort_order - [optional] 1 = Ascending, -1 = Descending. Default is ascending order.
- by_col - [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.
Usage notes
The SORT function sorts the contents of a range or array in ascending or descending order with a formula. The result from SORT is a dynamic array of values that will "spill" onto the worksheet into a range. If values in the source data change, the result from SORT will update automatically.
The SORT function takes four arguments: array,sort_index, sort_order, andby_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional argumentssort_index andsort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting. For example, to sort by the third column in a range of data, use 3 for sort_index. The optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, the SORT function will sort data vertically by rows. To sort a range horizontally by columns, set the fourth argument, by_col, to TRUE.
Basic Examples
To sort a range by the first column in ascending order:
= SORT (range) // sort by column 1, ascending = SORT (range, 1 , 1 ) // sort by column 1, ascending
To sort a range by the first column in descending order:
= SORT (range, 1 , - 1 ) // sort by column 1, descending
To sort a range by the second column in descending order:
= SORT (range, 2 , - 1 ) // sort by column 2, descending
To sort a range horizontally (by column) using values in row 1 in descending order, set the fourth argument, by_col, to TRUE or 1:
= SORT (range, 1 , - 1 , TRUE ) = SORT (range, 1 , - 1 , 1 )
Example - sort by score
In the example shown above, data includes names in column B and scores in column C. In cell E5, the SORT function is used to sort the data by score in descending order:
= SORT (B5:C14, 2 , - 1 ) // sort by scores in descending order
The SORT function extracts all values, sorted in descending order by score, and results "spill" into the range E5:F14. To sort by score in ascending order, omit sort_order or to 1 like this:
= SORT (B5:C14, 2 ) // sort by score in ascending order = SORT (B5:C14, 2 , 1 ) // sort by score in ascending order
Sort by more than one level
Unlike the SORTBY function, the SORT function does not provide a way to sort data by more than one level. However, by using array constants for sort_index and sort_order, it can be done. This video explains how.
Notes
- SORT returns a #VALUE error ifsort_index is out of range.
SORT is a new function available in Excel 365 only.
SORT formula examples
In this example, the goal is to extract a list of unique rows from the range B5:C15. The easiest way to do this is to use the UNIQUE function. By default, UNIQUE will extract unique values in rows, so there is no...
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B. The FILTER function applies criteria with the...
This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function. The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for...
In this example the goal is to sort the comma separated values in column B in alphabetical order. In Excel 365, the SORT function provides a straightforward way to sort values in individual cells (or in an array), but...
This is a good example of how the SUMPRODUCT function can help in situations where the COUNTIF or COUNTIFS functions do not work. In this case, the goal is to check all dates in a given range and show a check mark (✓)...
In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The...
In this example, the goal is to list the 10 most frequently occurring text values in a range, in descending order by count, as seen in the range in E5:F14. This is an advanced formula that requires a number of nested...
In this example, the goal is to build a simple summary count table with a formula. Once created, the summary table should automatically update to show new values and counts when data changes. The article below walks...
In this example, the goal is to retrieve historical stock price information for a given stock, provided as a ticker symbol like "MSFT", "AAPL", "MMM", etc. over the past n days, where n is a variable that can be...
The SORT function requires very little configuration. In the example shown, we want to sort data in B5:D14 by the third column, Group. For array, we provide entire range, B5:D14. For sort_index, we provide 3: =SORT(...
In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "...
The SORT function sorts a range using a given index, called sort_index. Normally, this index represents a column in the source data. However, the SORT function has an optional argument called "by_col" which allows...
Although FILTER is commonly used to filter rows, you can also filter columns. The trick is to use an include argument that operates on columns instead of rows. In this example, we use a hard-coded array constant to...
In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest...
The FILTER function accepts an array of values and an "include" argument which filters the array based on a logical expression or value. In this case, the array is provided as the named range "list1", which contains...
Related videos
In this video, we'll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
In this video, we'll look at how to sort by multiple columns with the SORT and SORTBY functions.
In this video, we'll look at how to create a two-way summary with dynamic array formulas. The result is a summary table that's very similar to a pivot table.
In this video, we'll build a dropdown list using dynamic arrays to filter data by color. The dropdown list is created with data validation.
In this video, we'll look at a basic example of sorting with the SORT function. Sorting with formulas is a traditionally hard problem in Excel new dynamic array formulas make much easier.
In this video, we'll look at how to nest dynamic array functions together to create powerful and dynamic solutions.
Related functions
The Excel UNIQUE function returns a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.
The Excel FILTER function filters a range of data based on supplied criteria, and extracts matching records.
The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.
The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to appear in results.
The Excel RANDARRAY function generates an array of random numbers between. The size or the array is is specified by rows and columns arguments. Generated values can be either decimals or whole numbers.
The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns arguments.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
Excel video training
Quick, clean, and to the point.
Source: https://exceljet.net/excel-functions/excel-sort-function
Post a Comment for "An Easy Way to Sort Data When There is Only One Sort Field"