In Excel, while the basic sort feature is useful for straightforward tasks, there are scenarios where a more formulaic approach is necessary. This article will delve into how to sort values using Excel sort functions like SMALL, ROW, SORT, and SORTBY. The SORT function offers a simple method to arrange data in ascending or descending order, especially beneficial when the original data order must remain unchanged. The SORTBY function, on the other hand, is perfect for sorting a range based on values in a different range, allowing for more complex sorting criteria. Meanwhile, functions like SMALL, combined with ROW, provide an alternative method to create custom sorting formulas, useful in scenarios where standard sorting functions may not suffice, such as sorting a list based on a secondary criterion or applying unique sorting logic.
How to Sort Values Using SMALL and ROW Functions
Below is a step-by-step guide on how to sort numbers using SMALL and ROW functions for optimal results:
=SMALL( column of list, ROW()-ROW(row of list's title))
- Start with SMALL( function
- Select the whole column that contains the unsorted list B:B,
- Continue with ROW() function without arguments
- Add minus character (-)
- Add ROW($2:$2) function with the row number one above the list
- Type ) to close the SMALL function and finish the formula
- Copy and paste down the formula
By following these steps, you can efficiently employ the Excel sort function along with SMALL and ROW functions, enhancing your spreadsheet sorting capabilities.
How to Sort Values In Excel Using SORT Function
Sorting numbers in Excel using the SORT function is straightforward. Here's a step-by-step guide on how to do it:
- Select the Data Range: First, you need to select the range of numbers you want to sort. This can be a column, a row, or a block of cells containing your numbers.
- Insert the SORT Function: Click on the cell where you want the sorted data to begin. This is typically adjacent to or below your selected data range. Type =SORT( to start your formula.
- Specify the Range in the SORT Function: After typing =SORT(, click on the first cell of the data range you want to sort, then drag to select the entire range. Alternatively, you can type the range manually (like A1:A10). Your formula will look something like =SORT(A1:A10). Close the parenthesis and press Enter. The sorted list will appear starting in the cell where you entered the formula.
The Excel SORT function also allows for optional parameters, such as specifying the sort direction (ascending or descending) and sorting by a specific column in a multi-column range. For a simple number sort, you usually don’t need these. But if you do, the formula can be expanded, for example, =SORT(A1:A10, 1, TRUE) where 1 refers to the first column, and TRUE specifies an ascending sort.
Excel’s dynamic array functions, like SORT, will automatically spill the sorted range into adjacent cells. Ensure there's enough empty space for the sorted data to populate.
The Excel SORT function is available in Excel for Office 365 and Excel 2019 and later versions. If you're using an older version of Excel, you might not have this function, and you'd have to use the sort feature from the Data tab or use other functions like SORTBY.
How to Sort Values In Excel Using SORTBY Function
Sorting numbers in Excel using the SORTBY function can be particularly useful when you want to sort a range based on the values in another range. Here’s how you can use the SORTBY function:
- Identify the Data Range and the Sort By Range: Data Range is the range of numbers or cells you want to sort. Sort By Range is the range of values that you want to use as the basis for sorting the Data Range.
- Insert the SORTBY Function: Click on the cell where you want the sorted data to begin. This is typically adjacent to or below your data range. Type =SORTBY( to start your formula.
- Specify the Ranges in the SORTBY Function: First, enter the Data Range you want to sort. For example, A2:A10. After a comma, enter the Sort By Range. This could be another column or row that determines the order. For example, B2:B10. Your formula now looks like =SORTBY(A2:A10, B2:B10). Close the parenthesis and press Enter. The sorted list will appear starting in the cell where you entered the formula.
The SORTBY function allows for additional parameters, such as specifying the sort direction (ascending or descending) and sorting by multiple levels. For instance, to sort in descending order, add -1 as an additional argument: =SORTBY(A2:A10, B2:B10, -1). The default is ascending order, which is equivalent to 1.
The SORTBY function offers an advanced approach to sorting data by allowing the use of multiple columns or rows as sorting criteria. Essentially, this function lets you organize data not just based on one criterion but several, in a prioritized manner. For instance, consider a dataset with employee names, departments, and performance scores. With SORTBY, you can first sort the data by department in alphabetical order, and then, within each department, sort by performance scores in descending order. This is achieved by specifying the department column as the primary sorting key and the performance scores as the secondary key in the function, like =SORTBY(A2:C10, B2:B10, 1, C2:C10, -1). Here, A2:C10 represents the data range, B2:B10 is the first sorting key (departments) in ascending order, and C2:C10 is the second key (performance scores) in descending order. This multi-tier sorting mechanism ensures that the data is organized first by department and then by scores within each department. Such a feature proves especially useful in detailed data analysis and reporting, where a clear and precise data arrangement is crucial.
Like other dynamic array functions in Excel, SORTBY will spill the results into adjacent cells. Ensure there's enough space for the sorted data.
The SORTBY function is a powerful tool for advanced sorting scenarios and is available in Excel for Office 365 and Excel 2019 and later versions. Remember that the size of the "sort by" array must match the size of the array to be sorted.
