Sort And Clean Your Data With SORT and SORTBY [Complete Guide]

 

Sorting and cleaning your Excel data is difficult.

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

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

This is a complex feature for confident users, and you can learn about it on our advanced Excel training.

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

The syntax of the SORT function reads as follows.

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.
• Sort Order – There are two options to be chosen under this argument, i.e. 1 or -1.

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

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

This is an optional argument, and if omitted, Excel will 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.

 

The Functions Library has countless useful formulas and functions for users, as well as useful descriptions of what they can do.

Why Should You Use SORT?

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-promo-1

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

The syntax of the SEQUENCE function reads as follows.

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, you want the ‘by array1’ to be arranged in ascending order.

-1 represents descending order, 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.

 

Why Should You Use SORTBY?

As a business owner, you can use the SORTBY function to sort the sales of 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 organize your data at two levels, i.e. country sales and product-wise sales.

Combining it with logical functions can enable you to generate powerful insights from your data

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.

The SORT function is also great for Accounting and Bookkeeping, which you can do in Excel!

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

 

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

• The first argument i.e. array is set as A2:B13. This consists 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 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.

If there are outliers or data points you don’t want to include, use an Advanced Filter to cut them out!

Key Points

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, 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.