SORT And SORTBY Functions – Mastering Excels Array Tools

If you’ve ever tried doing so, you’d know how problematic data sorting in Excel could be.

Doing it using formulas, you’d have to put together an insane combination of functions.

However, the new dynamic array functions make it super-easy for users to sort data.

No matter how long or wide your dataset is – with the SORT and SORTBY functions, sorting data is all about a few clicks.

Excel SORT Function

The Excel SORT function sorts a range of cells in an ascending or descending order based on a defined array. It returns a copy of the sorted data. Users can define the range of cells to be sorted.

The defined range or array needs not to appear in the data necessarily.

Syntax

Syntax of the SORT function reads as follows.

= SORT (array, by_array, [sort_order])

 

Arguments

Let’s break down the above arguments to see how each of these works.

• • Array – This defines the range or array containing the data that needs to be sorted.
  • By Array – This is the array based on which the data needs to be sorted. It can be a single column or row.
  • Sort Order – There are two options to be chosen under this argument i.e. 1 or -1.

1 represents ascending order i.e. you want the ‘by array’ to be arranged in ascending order.

-1 represents descending order i.e. you want the ‘by array’ to be arranged in descending order.

This is an optional argument, and if omitted, Excel would by default set it to 1 (ascending order).

Return Value

The SORT function returns a sorted copy of the source data.

Functions Library

You may access the SORT function from the Functions Library as follows.

Formulas > Functions Library > Lookup & Reference > SORT

Learn more details of formulas and functions in Excel here.

Why would someone want to use it?

The SORT function helps users with sorting data at a single level only. It is of most use where the underlying dataset is a relatively simpler one.

For instance, academic institutions may use the SORT function to compile results. To determine the positions secured by students, the SORT function can be used to arrange the results in descending order.

That way, the one who secured the highest marks gaining the first position would top the list. Others would then be arranged next in the list based on their grades.

Excel SORTBY Function

In contrast to the SORT function, the SORTBY function allows users to sort data at multiple levels. You can define multiple arrays and sort orders within the same formula to have your data organized at multiple levels.

Syntax

Syntax of the SEQUENCE function reads as follows.

=SORTBY (array, by_array1, [sort_order1], [array/order], …)

Arguments

Below is a breakup of each of these arguments.

• • Array – This defines the range or array containing the data that needs to be sorted.
  • By Array1 – This is the first array based on which the data needs to be sorted. It can be a single column or row.
  • Sort Order1 – There are two options to be chosen under this argument i.e. 1 or -1.

1 represents ascending order i.e. you want the ‘by array1’ to be arranged in ascending order.

-1 represents descending order i.e. you want the ‘by array1’ to be arranged in descending order.

This is an optional argument, and if omitted, Excel would by default set it to 1 (ascending order).

• • Array / Order – Here onwards start the optional arguments. You can specify multiple arrays and sort order pairs to sort your data.

Pay a little attention to observing how the last argument differentiates the SORT and SORTBY function. The SORTBY function allows users to specify ‘multiple array and order’ pairs within the same formula.

You can have your data organized at multiple levels using the SORTBY function.

Return Value

The SORTBY function returns a sorted copy of the source data.

Functions Library

You may access the SORTBY function from the Functions Library as follows.

Formulas > Functions Library > Lookup & Reference > SORTBY

 

Why would someone want to use it?

As a business owner, you may want to use the SORTBY function to sort the sales won by your business at multiple levels. The SORTBY function can help you sort group sales made by each product within each country.

Defining the ‘Country’ as the first by_array, you can group the sales country-wise. Next, you can further sort them based on products that were sold the most within each country. The SORTBY function will thus organize your data at two levels i.e. country sales and product-wise highest sales.

SORT function can be helpful for Accounting and Bookkeeping in Excel too.

SORTBY Use Cases

SORTBY function is the go-to tool for a wide range of Excel users as it helps sort data in multiple ways. It is of great help for both professional and non-professional data sorting purposes. Take a look below to see for yourself.

1. If you’ve ever imported sale or purchase records from external sources, you’d know problems that come in the process. Using the SORTBY function you can arrange your data against multiple arrays, be it sale/purchase value or grouping based on location/product.
2. As a data compiler, if you need to sort the names appearing in your data in alphabetical order, you may use the ascending order within the SORTBY function.
3. A pharmacy may arrange all its medicines from the same manufacturing date together in the same batch. The same may be then further arranged in ascending or descending order based on their potency.

There are many more ways how you can smartly nest in multiple formulas and bring the SORTBY function to use.

Learn more about Excel Logical functions here.

Using SORTBY – Simple Example

A quick basic example of the SORTBY function will help us learn better. So let us see through an example how the SORTBY function works.

The screenshot above manifests the age records of people working in an organization. However, the same data now needs to be organized in an ascending order based on the age of each person.

Step 1:

To arrange the data, first copy and paste the headers to the destination cells where you want the sorted data to be populated.

Step 2:

Activate the first cell of the destined location and compose the SORT formula as follows.

= SORT (A2:B13, B2:B13, 1)

Let’s look into each of the arguments configured above.

• • The first argument i.e. array is set as A2:B13. This constitutes of the range of cells containing the data that we want to be sorted.
  • The second argument i.e. By_array is set to B2:B13. This column contains age-related details. As we want the data to be sorted in an ascending order based on age, we have set it as the by_array.
  • The third argument i.e. sort_order is set to 1 as we want the data in column B2:B13 arranged in ascending order. You may also choose to leave it vacant, and Excel would assume it 1.

Step 3:

Once you’ve devised the formula, as above, hit ‘enter’ to yield results as follows.

Excel has arranged the ages in ascending order, and the names against each age are accordingly arranged.

Looking for more Excel guides? Read our guide to Calculating Age From A Date Of Birth In Excel here.

Using SORTBY With Arrays – Complex Example

After we’ve seen the basic application of the SORTBY function in Excel, it’s time we explore ways how it may be used in a complex manner. The SORTBY function stands out for its capability to sort data at multiple levels.

The example that follows will help us learn the application of the SORTBY function at multiple levels.

The above dataset displays the roll numbers and grades secured by different students from different classes. However, the data comes off to be jumbled.

We need to sort data by grouping students from the same class and then by arranging the students from each class by their roll number.

To sort students by class and in ascending order by their roll number, follow the steps below.

Step 1:

To arrange the data, first copy and paste the headers to the destination cells where you want the sorted data to be populated.

Step 2:

Activate the first cell of the destined location and compose the SORTBY formula as follows.

= SORTBY (A2:D13, B2:B13, 1, C2:C13, 1)

Makes little sense? Let’s break it down into individual arguments to understand how the formula is composed.

• • The first argument i.e. array is set as A2:D13. The said range of cells contains the data that we want to be sorted.
  • The second argument i.e. By_array1 is set to B2:B13. This column contains the section names. As we want students from the same section grouped, this column is set as the first array for data arrangement.
  • The third argument i.e. sort_order1 is set to 1 as we want the data in column B2:B13 arranged in ascending order. Excel would arrange the same in alphabetical order. You may also choose to leave it vacant, and Excel would assume it 1.

Here onwards, start the arguments for the next array/order as we want the data to be arranged based upon multiple arrays. We want the data to be first organized in an ascending order based on the sections.

Next, we want the data to be further organized in an ascending order based on the roll numbers of students. To extend the formula to the next sorting order, the following arguments are defined.

• • The fourth argument i.e. by_array2 for the second sort order is set to C2:C13 as we want the data to be sorted based on the roll numbers.
  • The fifth argument i.e. sort_order2 is set to 1 as we want the data in column C2:C13 arranged in ascending order. If left vacant, Excel would assume it 1.

Step 3:

This step is the easiest. After the formula is all configured, hit ‘enter’ to have your data sorted as follows.

What has Excel done to the source data?

The data is grouped based on Column B first. All the students from the section ‘Yellow’ are put together and so on for the other sections.

Within each section, let’s say the Yellow grouping, students are arranged in an ascending order based on their roll numbers.

A Quick Brainteaser:

What if you identify an error made to the source data? For instance, if the roll number of a student is mistakenly entered as 3 instead of 5?

Simple. Make the desired changes to the source data and Excel would re-sort the data automatically.

Want to filter out some data before it is sorted out? Learn Excel Advanced Filters here.

Points to Note:

Here are a few pointers to help you through your Excel operations involving the SORT and SORTBY functions.

1. The by_array column necessarily needs not to be a part of the source range.
2. If not a part of the source range, the by_array column wouldn’t be copied to the sorted data by Excel.
3. The by_array arguments can only be one column long or one row wide.
4. The SORTBY function can be worked out based on arrays or array constants too.
5. Different sorting orders can be defined against different arrays. To put it in another way, the sort_order1 against by_array1 can be 1 (ascending order). Whereas, the sort_order2 against by_array2 can be -1 (descending order).

Troubleshooting the SORTBY Function

Without a second thought, the SORTBY function is a very smart one from the Excel dynamic array functions library. However, with smart functions, come smart problems.

No worries, here is how you can cope with all the problems that might keep your SORTBY function from working as hoped.

1.   Incompatible Dimensions

To make the SORT and SORTBY functions work, the dimensions of your by_array should be strictly equal to that of your range.

For example, if your array or range consists of 5 rows, the by_array that you define should also be 5-rows long. If both these arrays are inconsistent, Excel would fail to function the SORT and SORTBY function.

The by_array argument must be at least one column or row wide and compatible with the range.

2.   By_array argument

The By_array argument can only take the form of a single column or row that is compatible with the overall range or array. If the by_array argument consists of multiple columns or rows, the SORT or SORTBY function would fail to yield valid results.

3.   Invalid Sort_Order

Excel only offers two options to sort data i.e. in an ascending or descending order. To command data organization in ascending order, use 1 and for data organization in descending order, use -1.

If omitted, the sort_order is by default set to 1. It is to be noted that anything other than 1 or -1 configured into the SORT or SORTBY formula would return a #VALUE! Error.

Conclusion

The dynamic array tools, SORT, and SORTBY will not give you a tough time as both of them are logically understandable.

You can define an array or multiple arrays alongside a sorting order to sort your rows or columns’ long data in seconds.

This topic can be very technical, but learning how to use arrays will save you hours of grunt work!

Practicing the same using smaller datasets can help you master the same quickly.

Want to learn how to Convert Currencies Using Excel? Read here.