Even More Things You Can DO With Excel’s SORT FunctionSort and filter, extract and sort by rank, extract value in the nth position... Let's try them all!

  |

In my previous article, we talked about Excel’s new SORT function.

We’ve discussed how it works and how you could use it. In case you missed it, here’s a link: Excel’s Sort Function.

In that article, I’ve shown you the basic uses of the SORT function.

There is still more that you can do with this nifty function such as extracting the value in the Nth place or extracting a particular group of data (e.g. the 1st to 3rd highest or lowest).

We’ll be exploring these things here in this article.

Also, we’ll be discussing the common error associated with the SORT function and how to troubleshoot them.

Let’s get started.

Combining the SORT and FILTER Functions in Excel

The SORT function creates a dynamic array that automatically sorts the referenced array in ascending or descending order depending on the parameter that you specified.

The FILTER function extracts an array of values from the referenced array based on the criteria that you define.

If we combine these two functions, we’ll get an array of extracted values based on the defined criteria.

On top of that, this array automatically sorts the extracted values depending on the set parameters.

The formula for these combined functions is as follows:

=SORT(FILTER(array, criteria_range=criteria), [sort_index], [sort_order], [by_col])

Where,

array – refers to an array of values or a range of cells that you’ll be filtering from via the FILTER function and then sorting via the SORT function

criteria_range=critera – this defines the criteria for filtering

[sort_index] – this specifies which column or row the SORT function will sort by; this parameter is optional and has a default value of 1 (if omitted, the SORT function will sort by the first column or row)

[sort_order] – this specifies the sorting order and can have a value of 1 or -1. If its value is 1, the SORT function will sort in ascending order. If its value is -1, it will sort in descending order. This parameter is optional and has a default value of 1.

[by_col] – this specifies whether the SORT function sorts by column or row. It can have one of these two values: FALSE or TRUE. If its value is FALSE, the SORT function will sort by row. If its value is TRUE, the function will sort by column. This parameter is optional and has a default FALSE value.

How to SORT and FILTER

To better understand how the formula works, let’s have an example:

From the table above, we will only be extracting rows that have a score of 70 and above.

Also, we want to sort the extracted values from highest to lowest (descending order).

To do so, we will be using a combination of the SORT and FILTER function.

  • Select an empty cell, preferably with empty cells to it its right and bottom. For our illustration, this will be cell D2.
  • In the empty cell, enter the formula for the combination of the SORT and FILTER functions. For our illustration, the formula will be: =SORT(FILTER(A2:B11,B2:B11>=70),2,-1).

  • criteria_range is set to B2:B11>=70 since these are the cells that contain the scores and we only want to extract those that are at least 70. [sort_index] is set to 2 because we want to sort by Scores, which is the 2nd column of the array. [sort_order] is set to -1 because we want to sort by largest to smallest (descending order). [by_col] is omitted since we’ll be sorting rows, which is the default setting.
  • Press the Enter key. The formula will create a dynamic array based on the parameters we set.

  • We have successfully sorted and filtered the dataset above. Let’s format the new array to make it look like the referenced array.

Extract and Sort the Nth Largest or Smallest Values in Excel

When working with a large dataset, you may sometimes have to extract top or bottom values (largest or smallest).

And to make them easier to analyze, you may also want to sort them from largest to smallest (or vice versa).

For example, you may want to extract the top 3 values and have them sorted from top 1 to top 3. This can be done in Excel with the help of dynamic arrays.

We’ll be using a combination of three functions: SORT, INDEX, and SEQUENCE. Here’s the formula for using this combination:

=INDEX(SORT(array, ), [sort_index], [sort_order], [by_col]),SEQUENCE(n), {col_num1, col_num2, …})

By now, you should already be familiar with the formula for the SORT function. As such, let’s go over the INDEX and SEQUENCE functions.

SEQUENCE(n) – this function returns sequential numbers based on what you define as n.

For example, if you want to extract three values from an array in sequential order, you’ll have to set n to 3.

INDEX – this function returns an array depending on the parameters set.

You can set the columns in the order that you want.

Understanding the formula can be pretty tricky with only words to work with it.

To make it easier, let’s have a visual example.

How to Extract the N top or bottom values

Suppose we want to get the top 3 scores from this dataset.

We also want to arrange the results from highest to lowest.

To do so, let’s use a combination of the SORT, INDEX, and SEQUENCE functions.

  • Select an empty cell, preferably with empty cells to it its right and bottom. For our illustration, this will be cell E2.
  • In the empty cell, enter the formula for the combination of the SORT, INDEX, and SEQUENCE functions. For our illustration, the formula will be: =INDEX(SORT(A2:B11,2,-1),SEQUENCE(3),{1,2})).

  • [sort_index] is set to 2 because we want to sort by Scores, which is the 2nd column of the array. [sort_order] is set to -1 because we want to sort by largest to smallest (descending order). [by_col] is omitted since we’ll be sorting rows, which is the default setting. n for SEQUENCE is set to 3 because we want to extract three sequential values. Finally, the column numbers are set to {1,2} because we want the INDEX function to show the 1st column of the array first, then the 2nd
  • Press the Enter key. The formula will create a dynamic array based on the parameters we set.

  • We have successfully extracted and sorted the top 3 scores from the dataset above. Let’s format the new array to make it look like the referenced array.

Extract the Value in the Nth Position in Excel

Suppose you only want to extract the nth value from an array. For example, you only want to get the 5th highest or 7th lowest value from a dataset.

This can be done by modifying the formula we made above. We only need to remove the SEQUENCE function from the equation.

=INDEX(SORT(array, ), [sort_index], [sort_order], [by_col]),(n), {col_num1, col_num2, …})

By removing the SEQUENCE function, the formula will only return the value specified by n.

For example, if you set ­n to 5, the formula will extract the value in the 5th position.

The role of the SORT function here is to determine whether the nth position is from the top or the bottom.

If it’s from the top, [sort_order] must be set to -1. If it’s from the bottom, [sort_order] must be set to 1.

How to Extract the Value in the Nth Position

Here we have a sheet that’s set up to extract the value in the nth position from the top or bottom.

Let’s enter the formulas to make this sheet functional.

  • To extract the value in the nth position from the top, select cell D4. In this cell, enter the formula: =INDEX(SORT(A2:B11,2,-1),E1,{1,2}). We set n to E1 so that it returns the position that we specify in cell E1. If E1 is empty, the formula will always return the 1st value from the top.

  • Let’s try the newly set formula. In cell E1, let’s enter 5 so that we get the value in the 5th position from the top.

  • To extract the value in the nth position from the bottom, select cell G4. In this cell, enter the formula: =INDEX(SORT(A2:B11,2,1),H1,{1,2}). We set n to E1 so that it returns the position that we specify in cell H1. If H1 is empty, the formula will always return the 1st value from the bottom.

  • Let’s try the newly set formula. In cell H1, let’s enter 3 so that we get the value in the 3rd position from the bottom.

Common Errors Associated with the SORT Function

Sometimes, your SORT formula might not work and instead show error values.

Here are some of the errors associated with the SORT function (and how to address them).

#NAME – this error may appear because of two reasons (1) you misspelled the function (e.g. SROT instead of SORT), or (2) you’re using a version Excel that does not support the SORT function. To fix this issue, either spell the function correctly or use a version that supports it

#SPILL – this error occurs when there are not enough empty cells to accommodate the shape of the resulting array (even a space in a cell can make this error appear). Merged cells could be another reason for this error. To fix this issue, make sure that the cells where the resulting array will show are empty

#VALUE – this error occurs if there are invalid values in the formula. For example, [sort_order] can only have a value of 1 or -1. If its value is set to 2, the formula will return a #VALUE error. To fix this, just make sure that there are no invalid values in the formula

#REF – this error occurs when the workbook where the referenced array is located is closed. For dynamic arrays to work, the source workbook (where the referenced array is located) must be open.

Conclusion

And those are the additional things you can do with Excel’s SORT function.

I’ve also included a section on the common errors associated with the SORT function and how to fix them.

I hope that you’ll be able to use your learnings here in your future endeavors.