While Excel offers the SORT and SORTBY functions for sorting a table, Google Sheets offers only the SORT function for the same purpose. Therefore, you may encounter issues when using the Excel SORT function in Google Sheets, and vice versa.
Additionally, Google Sheets has another sorting function called SORTN, which can sort data. However, its purpose differs significantly from the other two functions. For the sake of this comparison, I won’t include SORTN in our discussion.
Not all Excel versions include the SORT or SORTBY functions. Microsoft introduced these functions in Excel 365, Excel 2021, and newer versions, if any.
Now, let’s examine how the SORT and SORTBY functions in Excel compare with the SORT function in Google Sheets.
Syntax Comparison
Let’s examine the syntax differences between the SORT and SORTBY functions in Excel and the SORT function in Google Sheets.
SORT in Google Sheets:
=SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])SORT and SORTBY in Excel:
=SORT(array,[sort_index],[sort_order],[by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)Now, let’s analyze each parameter to identify the distinctions.
Sorting Data (Parameter 1):
Both functions require specifying the table or array to sort, excluding the header row. In Google Sheets, this parameter is referred to as range, while in Excel, it’s called array. Despite the naming difference, they serve the same purpose.
Column by Which to Sort (Parameter 2):
You can specify a column index or range using the sort_column parameter in the SORT function Google Sheets. In Excel’s SORT function, sorting by the column index is done using sort_index. If you want to sort by range, you should rely on the SORTBY function, where the parameter is called by_array.
The sort_index parameter in Excel’s SORT function can also represent a row index.
Sorting Order (Parameter 3):
Google Sheets allows specifying TRUE or 1 for ascending order and FALSE or 0 for descending order using the is_ascending parameter. In Excel, SORT and SORTBY use 1 for ascending and -1 for descending, with the parameter named sort_order.
Sort by Columns (Parameter 4, Applicable to SORT Function in Excel):
Google Sheets’ SORT function currently lacks the functionality to sort columns in a range by a specified row. In contrast, Excel’s SORT function offers this feature through the by_col parameter.
By understanding these parameter distinctions, you can effectively utilize the sorting capabilities of both Excel and Google Sheets.
SORT Function in Excel vs SORT Function in Google Sheets: Examples
Example 1:
To sort the range A2:C11 by the entries in column A in descending order, you can utilize the following formula in Excel:
=SORT(A2:C11, 1, -1)
This action may format the dates in the sort range to date values. Therefore, you may need to select the date range and format it back to a short or long date format. You can find that option under the Home tab, in the Number group.
In Google Sheets, you should specify the formula as follows:
=SORT(A2:C11, 1, FALSE)The difference lies in the sort order: Excel uses 1 or -1, while Google Sheets uses TRUE or FALSE.
Example 2:
In the Excel SORT function, you can specify a row index instead of a column index to sort the range by entries in a row.
The following formula sorts the range A1:C3 by entries in the first row in descending order:
=SORT(A1:C3, 1, -1, TRUE)
Google Sheets’ SORT function doesn’t have this functionality. However, you can achieve a similar outcome through a workaround.
In Google Sheets, you can follow this workaround to meet this requirement: first, transpose the data, then sort by the entries in the first column, and finally transpose it back.
=TRANSPOSE(SORT(TRANSPOSE(A1:C3), 1, FALSE))That covers the differences between SORT in Excel and SORT in Google Sheets.
SORTBY Function in Excel vs SORT Function in Google Sheets: Examples
Sometimes, you might need to sort a table or range based on another range (column) with the same number of rows as the table. This can be done using SORTBY in Excel or SORT in Google Sheets.
Example:
In Excel:
=SORTBY(A2:A7, D2:D7, 1)The above formula sorts the tasks in column A based on the durations in column D in ascending order.
In Google Sheets:
=SORT(A2:A7, D2:D7, TRUE)
The SORT function in Google Sheets can achieve the same.
Key Takeaways
The SORT function in Google Sheets serves the purpose of both the SORT and SORTBY functions in Excel, but it does not include the option to sort columns (sorting the range by entries in a row).
In Excel, the functions may alter the formatting of dates to date values, whereas the Google Sheets function does not.
In Excel, the sort functions use 1 for ascending or -1 for descending to represent the sort order, whereas in Google Sheets, it’s TRUE or FALSE (1 or 0).
