Hello Excellers. Welcome back to another #Excel #FormulaFriday blog post in my 2021 series. Today let’s explore the SORTfunction in Excel. This newly released function is part of the DYNAMIC Array functions announced by Microsoft back in 2018. At that time these array functions were only beta release available to a portion of Office insiders. A while ago Microsoft announced these awesome new Excel functions are now available to the Excel monthly Channel. So without any delay let’s get down to using the SORT function.
What Does The Sort Function Do?
This new function in Excel will sort the contents of a range or an array. The result is a dynamic array of values. If the result of the function is the last step (that is, if it is not passed to another function), or final result then the array will ‘spill’ into a range on the worksheet. This automatically updates when new unique values are added or removed from the source range, or when source values change.
By default the values are sorted in ascending order and by the use of the first column. The ar
Syntax Of The Sort Function.
=SORT (array, [sort_index], [sort_order], [by_col])Arguments
- array – This is the range or array to sort.
- sort_index – [this is optional] Column index to use for sorting. Default is 1.
- sort_order – [this is optional] 1 = Ascending, -1 = Descending. Default is ascending order.
- by_col – [this is optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.
Let’s Work An Example Of The Sort Function.
I always find it is easier to use an example. My sample data set is below.
So, it is a very simple example. Just two columns. Sales Items and their associated cost.
Sort In Ascending Order.
Here is how to sort in ascending order by Cost. The result ‘spills’ into a new array on the worksheet.
=SORT(B3:C13,2,1)
Sort In Descending Order.
So, it is just as easy to sort in descending order with this function.
=SORT(B3:C13,2,-1)
How cool is that?. Have you used the new Excel array function available with 365?.