HomeGoogle DocsSpreadsheetHow to Use SORTN Function in Google Sheets to Extract Sorted N...

How to Use SORTN Function in Google Sheets to Extract Sorted N Rows

Published on

I was very rarely using the SORTN function in Google Sheets. But things have changed very much lately. I realized later that it can be a killer combination with other functions to solve many problems in Google Sheets.

Personally, I think Google Sheets SORTN function is not much popular like the SORT function. I seriously doubt whether many of the Google Doc users are aware of this function.

At the time of writing this post, the SORTN function is not available in Excel. So those who have made a switch from Excel to Google Sheets may be unaware of the availability of this extraordinary function in Sheets. But I hope, sooner or later, this function will be made available in Excel too.

I am using functions like Query and sometimes a combination of Unique and Sort as a replacement to SORTN. But not always the said alternatives will work.

For example, the SORTN function in Google Sheets is useful for specific types of jobs like fetching rank holders from a long list. But for that purpose using this function is not a must.

The SORTN has an unmatching capability and that is duplicate removal. If you follow the below link, you can find the details.

Must Read: Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet]

So I advise you to learn how to use the SORTN Function in Google Sheets. I personally of the opinion that this function deserves more attention than SORT as it’s more capable of sorting datasets.

To some extent, you can use a SORTN formula in Google Sheets to replace the functions UNIQUE and Array_Constrain too.

Purpose of Google Sheets SORTN Function

The purpose of the SORTN function is to return the first ‘n’ rows in a data set after performing a sort.

The sort can be in ascending or descending order. So you can filter the highest or lowest ‘n’ number of items from a range using SORTN.

Further, you have better control over the output when there are duplicate entries in the range. You can decide whether to include or exclude duplicate rows.

Similar: Using Query how to extract top N Number of Items in Google Sheets

SORTN Function in Google Sheets – How to

Though the syntax of SORTN function in Google Sheets seems a little complex to understand, it’s not so in the real sense.

I hope I can well explain it with the help of a few formulas. See below how to use SORTN formulas in Docs Sheets. First, see the syntax.

Syntax and Arguments

SORTN(RANGE, [N], [DISPLAY_TIES_MODE], [SORT_COLUMN1, IS_ASCENDING1], ...)

range – The data to sort.

n – The number of rows you want in the sorted output (optional and 1 by default, but must specify if you use the below arguments in the formula).

display_ties_mode – The numbers 0 to 3 representing the ties (related to duplicates in sort column). I have included these 4 modes in the below examples. Further, there is a very detailed tutorial related to ties mode – SORTN Tie Modes in Google Sheets – The Four Tiebreakers.

sort_column1 – It’s the column index number or range reference to sort. The data gets sorted based on this index and also the above ties mode work based on this.

is_ascending1 – Use the Boolean TRUE or FALSE to indicate the sort order. Also, you can use 1 or 0 to specify whether to sort in ascending or descending order.

Formula Examples to Google Sheets SORTN Function

Here are some examples to the use of the SORTN Function in Google Sheets.

In the screenshot below, the sample data range is only A1:C6. After row 6, I’ve applied a few SORTN formulas.

SORTN Formula Examples

The Formula 1 in Cell A8 (Sorted Two Rows)

=SORTN(A2:C6,2, 0 ,C2:C6,FALSE)

Display Tie Modes:

As mentioned under the syntax part above, there are four display ties mode in Google Sheets SORTN function. They are 0, 1, 2 and 3. In the above formula, I’ve used display tie mode 0. What does that mean?

Nothing concrete here. Simply put this number for sorting like the SORT function.

In this formula;

A2:C6 is the range, 2 [n] indicates the number of rows to return and 0 (neutral) indicates the display ties mode.

Useful Tip: In a large set of data (growing), if you are unsure about how many numbers of rows are to be returned (infinite), you can use 9^9 (an arbitrary number) as [n].

Now the balance is C2:C6 and it is the column to sort (you can also put 3). Finally, you can use TRUE to sort the data in ascending or FALSE to Sort it in descending order.

About the formula output:

This formula sorts the range A2:C6 based on the range C2:C6 (“Match 2 Point”) in descending order and returns the first two rows. I have sorted the data in descending order to get the highest two values in Column C.

The Formula 2 in Cell A11 (Sorted Two Rows + One More Row Identical to the Second Row)

Note: In all the example formulas from 2 to 4 that under proper titles below, I am going to use the same formula which I’ve used under the title “The Formula 1 in Cell A8”. The only change is in the use of display ties mode.

Here is the formula 2 to the use of the SORTN Function in Google Sheets.

=SORTN(A2:C6,2,1,C2:C6,FALSE)
Example to the SORTN Function in Google Sheets

Here the tie mode is 1 and in the previous example, it was 0.

Please refer the screenshot above to see the results in cell A11. Here also the number of rows to return is 2. But the formula returns 3 rows. Why?

This’s because of the use of the display tie mode 1 in the formula. Here in this example, similar to the formula # 1, I have sorted the third column in descending order.

As a result the formula returned the rows that containing the highest two values, i.e. 50 and 40, in the range plus one additional row.

The additional row is ‘identical’ to the second row in the formula result. Here identical means identical to the value in cell C12, which is 40.

In other words, player B and C tops if we select two persons as winners based on “Match 2 Point” (column C) score. But with this formula, we can return one more winner who has the same score of player C.

What would be the result if there are two players with the same top score?

For example, if B and C have the score 50, the formula would return these two rows only. There won’t be any additional rows.

If B, C, and E have the score 50, then the formula would extract these three rows. See the sentence in bold above to understand this.

The Formula 3 in Cell A15 (Sorted Two Rows Without Duplicates)

Tie mode 2 use in SORTN Function in Google Sheets.

Note: It’s a killer feature (tie mode 2) that you should explore. In my above-shared tutorial on duplicate removal (at the beginning of this post, just above the Purpose…), I’ve explained/used this feature.

=SORTN(A2:C6,2,2,C2:C6,FALSE)

In this formula, I’ve used the SORTN tie mode 2. This formula returns 2 rows but after removing the duplicates if any.

SORTN - Two rows without any duplicates

Here duplicate means not by comparing the entire rows but by comparing the values in the column range C2:C6.

The [N] in the above formula is 2. The first value in the output is 50 (cell C15) which has no any duplicates to remove in that column.

I think, in this case, I should provide you another example. The below values are in F3:G5.

SORTN to remove duplicates in toppers

See the author’s name and book price. It’s just for example. I haven’t even given any book name. It was there between the name and price columns. I simply removed that to make this data suitable for our test.

Here a SORTN formula with tie mode 2 that is set to sort the column 2 containing the book price would return the following entries.

=sortn(F3:G5,2,2,2,true)
Agatha Christie$100.00
Agatha Christie$300.00

If the sorting column is column index one, that is the column containing the author’s name, the result would be as below.

=sortn(F3:G5,2,2,1,true)

or

=sortn(F3:G5,2,2,F3:F5,true)
Agatha Christie$100.00

Hope you could understand how to use the SORTN function in Google Sheets to eliminate duplicates.

The Formula 4 in Cell A18 (Sorted 2 Unique Rows + Its All Duplicates)

This final formula shows the tie mode 3 use in SORTN Function in Google Sheets.

=SORTN(A2:C6,2,3,C2:C6,FALSE)

Here is the use of tie mode 3. It’s almost similar to the above example formula 2 (tie mode 1).

There the formula returns one duplicate of the last n sorted row, here all duplicates of all n sorted rows.

Sorted 2 unique rows retaining duplicates

Our sample data in A2:C6 has not enough values to detail this formula. So I’m taking you to another example below.

Here I’ve changed the values in the column range C2:C6 and see the returned values.

Mode 3 in SORTN Function in Google Sheets

Hope you could learn how to use the SORTN function in Google Sheets.

I suggest you make a sample sheet as above and try all the formulas. You may change the values in column C and see how that affect the results.

More SORTN Formula Examples (Also use the search field on the footer or top navigation bar to find more tutorials)

  1. How to Apply Unique in Selected Columns in Google Sheets.
  2. Vlookup Last Record in Each Group in Google Sheets.
  3. How to Find the Last Row in Each Group in Google Sheets.
  4. How to Find All Lowest Price Items in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here