Formulas

SORT AND SORTBY Function

Pinterest LinkedIn Tumblr

Sorting organizes our data and makes it much easier to find information. In this article, we will learn how to use the Sort and SortBy Function to sort the range or array in a preferred order.

The Sort and SortBy Function- A glimpse

We will first dive into learning what is sort function how to use it. Followed by understanding the SortBy Function in detail. The Sort and SortBy Functions are available only in Microsoft 365.

Sort Function

        The Sort Function sorts the data in a range or array. The result is a dynamic array of values. Sorting data in MS Excel rearranges the rows based on the contents of a particular column. We can arrange data in alphabetical order or numerical order. Like we can arrange names in ascending order or the amount from smallest to largest or largest to smallest.

        Sorting data in Excel has been made quite easy with all the in-built options. The fastest way to access sorting options is by using the sorting buttons in the ribbons where we have various criteria to sort on. But the Sort Function is best useful when you have large datasets and you wish to sort only in ascending or descending order.

SYNTAX OF SORT FUNCTION :

  =SORT(array, [sort_index], [sort_order], [by_col])

Let’s look at an example to understand better. Below is our data. We have ‘Names’ column which we will be sorting in Ascending order.

SORT AND SORTBY Function

The steps are as follows:

  1. Type =SORT(
  2. Then select the range we want to sort. In our example, we will select the range as B8: B17 which is the Name column.
  3. Hit Enter and the data will be sorted in ascending order.
SORT AND SORTBY Function

NOTE: If any changes are made to the source list after sorting, the same will reflect in the array of values.

SortBy Function

      The SortBy Function allows you to sort the data based on the values in a corresponding array or range. The SortBy function was announced by Microsoft on 24 September 2018 and is one of Excel’s new dynamic array functions.

     SortBy makes use of the changes made to the calculation engine, enabling Excel to return  the results of a single formula into multiple cells.

SYNTAX OF THE SORTBY FUNCTION :

=SORTBY(array,sorting_array1,[order],…)

Let’s understand with an example. Consider the below data which we wish to first Sort by the ‘Date’ followed by ‘Amount’.

SORT AND SORTBY Function
  • Type =SORTBY(
  • The first argument is selecting the entire array or the data. In our case, it is B8: E16
  • The next argument is to select the array or range to sort on. In this example, we wish to sort the Date first, so we select the range B8: B16
  • The next argument is the sort order denoted by 1 or -1 for ascending or descending order.
  • Similarly, we add the data range E8: E16 and sort it in ascending order by writing 1.
  • Similarly, we can add more arguments.

The output is as below.

SORT AND SORTBY Function

ERROR CONDITIONS OF SORTBY OPTION:

  • The sortby_array arguments must either be one row high, or one column wide.
  • All of the arguments must be the same size.
  • If the sort order argument is not -1, or 1, the formula will result in a #VALUE! error. If you leave out the sort order argument, Excel will default to ascending order.

Note– If you change any data in the original source, it will result in #SPILL! Error. The entire range will give an error stating #SPILL! in both Sort and SortBy Functions.

Conclusion :

        Excel’s Sort and SortBy Function sort the contents of a range or array as we have seen in the above examples. Values can be sorted by one or more columns. SORT returns a dynamic array of results.

More information is available on Sort Function and SortBy Function.

To know more, subscribe to our Youtube channel. To subscribe – Click Here.

Related Posts

Write A Comment