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.

  1. Select an empty cell where you want the sorted data to appear.
  2. 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. 

  1. Press Enter to apply the formula. The sorted table will appear in the selected cell and will include the header row.
  2. 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.

  1. Select an empty cell where you want the sorted data to appear.
  1. 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).

  1. 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?

Write a comment

Your email address will not be published. Required fields are marked *