
Sort & Sort by function
ππ Master Excel data organization with the right sorting function: Sort π½ vs Sort ByΒ π
π βSorting Large Datasets in Excel,π Which Sorting Function Should You Use? In this blog post, Lets learn the difference between sort and sort by function in Excelπ
Introduction
Sorting data in Excel is a common task, but it can be time-consuming and complicated. The βSortβ and βSort Byβ functions are two useful Functions that can make data sorting easier and more efficient. By understanding the differences between these functions, Excel users can choose the right function for their data sorting needs and streamline their workflow.β
Sort Function
The Sort function in Excel is a tool that arranges data by organizing a range of cells based on the values in a single column. It can sort data in ascending or descending order.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
Array: range or array of data that you want to sort
Sort_index: (Optional) The index of the column in the range that you want to sort by. If not specified, the first column is used by default.
Sort_order: (Optional) The order in which you want to sort the data. You can use β1β for ascending or β-1β for descending. If not specified, β1β is used by default.
by_col: (Optional) That indicates whether to sort by column or by row. The default is to sort by row.
How itΒ works:
The table lists employee information for a company, including their employee ID, name, job title, and salary.
- Select an empty cell where you want the sorted data to appear.
- Enter the following formula: =SORT(B3:E23,4,-1)
B3:E23 is the range of cells containing the table, including the header row.
4 is the number of the column you want to sort by (the 4th column).
-1 specifies that you want to sort the column in descending order.Β
- Press Enter to apply the formula. The sorted table will appear in the selected cell and will include the header row.
- Note that this method creates a new sorted table, rather than sorting the original table in place. If you want to sort the original table, youβll need to copy and paste the sorted data back into the original table.
Sort byΒ Function
The SORTBY function allows you to sort a range or array of data based on the values in another range or array. It is useful for sorting complex data sets by one or multiple columns.
Syntax
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2],β¦)
array: The range or array of data to sort.
by_array1: The range or array of values to sort the array by.
sort_order1:[Optional] The sort order for by_array.
by_array1: [Optional] The range or array of values to sort an array by if there are ties in by_array1.
sort_order2: [Optional] The sort order for by_array2 The default value is 1 (ascending order). Use -1 to sort in descending order.
How itΒ works:
This table lists various products along with their product code, type, brand, and total sales.
With the βSort byβ function in Excel, we can easily identify which product types have the highest sales by sorting them in descending order based on their total sales.
- Select an empty cell where you want the sorted data to appear.
- Enter the following formula: ==SORTBY(B3:F24, D3:D24, 1, F3:F24, -1)
B3:F24 is the range of cells containing the table
D3:D24 is the product type column, and 1 is the sort order (ascending order).
F3:F24 is the Total Sales column, and -1 is the sort order (descending order).
- This formula will sort the βProduct Nameβ column based on the values in the βTotal salesβ column in descending order. If there are ties, it will sort the tied rows based on the original order of the βProduct Nameβ column.
Differences Between Sort and Sort By Functions:
Sorting Capability: The SORT function can sort a range of cells based on the values in a single column, while the SORTBY function can sort a range of cells based on the values in one or multiple columns.
Arguments: The SORT function requires only one argument (the range to be sorted), while the SORTBY function requires at least two arguments (the range to be sorted and the range to sort by).
Flexibility: The SORTBY function is more flexible than the SORT function because it allows sorting by multiple columns, and you can specify the sort order for each column individually
Complexity: The SORTBY function is more complex than the SORT function because it requires more arguments, and you need to specify the correct arguments in the correct order to get the desired result.
Compatibility: The SORT function is available in all versions of Excel, while the SORTBY function is only available in Excel 365, Excel 2019, and Excel 2016 for Windows, and Excel for Mac 2019 and Excel for Mac 2016 (version 16.10 or later).
Conclusion:
When it comes to sorting data in Excel, you have two powerful tools at your disposal: βSortβ and βSort Byβ. The βSortβ function is great for basic sorting based on one column, while the βSort Byβ function is like a sorting ninja that can handle multiple columns like a pro. By knowing the differences between these two functions, youβll be able to quickly and easily sort your data to perfection. So, why not give them a try and sort your data like a pro today?