SORTN Function In Google Sheets

The SORTN function in Google Sheets sorts your data and returns the first n results.

One common use case is to find the top n or the bottom n values in a dataset.

For example, here’s how you use SORTN to extract the top 5 values in a dataset:

SORTN Function In Google Sheets

with this formula:

=SORTN(A2:C21,5,0,3,FALSE)

🔗 Get this example and others in the template at the bottom of this article.

SORTN Function Syntax

=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], [sort_column2, is_ascending2], ...)

It takes a minimum of one argument, but typically 3 or 5 arguments.

range

The range is the data that you want to sort and return the first n rows from.

n

The number of items to return. It must be greater than 0. This argument is optional and has a default value of 1 if omitted.

display_ties_mode

A setting to determine how ties (equal values) are dealt with:

  • 0 – ignore ties and show first n rows only
  • 1 – show first n rows plus any addtional ties with nth row
  • 2 – show n rows but remove duplicates
  • 3 – show first n unique rows and all duplicates of these rows

We’ll look at how these settings work in the examples below.

sort_column1

The index number of the column in your range you want to sort or a range reference to use for sorting. If you input a range, it must have the same number of rows as the main range.

Optional but it must be paired with sort order TRUE/FALSE value.

If a sort column is not specified, the first column is used by default.

is_ascending1

Determines which order you want to sort the column.

TRUE sorts in ascending order. FALSE sorts in descending order.

[sort_column2, is_ascending2]

Optional pairs of sort columns and sort order.

SORTN Function Examples

The following examples use this dataset (available in the template):

sortn Function Data

Example 1: No Optional Arguments

If you only include the range in your SORTN function, it returns a single value and sorts on the first column of your range.

=SORTN(A2:C21)

In this example, it sorts alphabetically from A-Z on column 1 and returns a single value.

The yellow highlighting indicates which cell the SORTN function is in.

sortn Example 1

Example 2: Top 5, No Sort Column Specified

Use the second argument to specify how many rows to return.

This example returns 5 rows:

=SORTN(A2:C21,5)

sortn Example 2

Example 3: Top 10, No Sort Column Specified

Here, this formula returns 10 rows:

=SORTN(A2:C21,10)

sortn Example 3

Example 4: Top 5 & Disregard Ties

The third argument determines how to treat ties, e.g. when there are two equal values in the n-th place.

This formula returns the first 5 values and disregards any ties:

=SORTN(A2:C21,5,0)

which looks like this in our Sheet:

sortn Example 4

Example 5: Top 5 & Disregard Ties & Specify Sort Column

The sort column must be paired with a TRUE/FALSE value that determines whether to sort ascending or descending. I.e. if you add the 4th argument then you must also add the 5th argument.

This formula selects the column at index 3 and sorts it descending, high to low:

=SORTN(A2:C21,5,0,3,FALSE)

which looks like this in our Sheet:

sortn Example 5

Example 6: Top 5 & Include Additional Tied Rows

Setting the third argument — how to deal with ties — to 1 tells the formula to include the additional ties of the nth row:

=SORTN(A2:C21,5,1,3,FALSE)

So in this case, we get the first 5 rows plus a sixth row because it ties with the 5th row:

sortn Example 6

Example 7: Top 5 & Remove Duplicates

However, if we set the third argument to 2, it removes any duplicate rows and shows the first n rows after removing duplicates.

=SORTN(A2:C21,5,2,3,FALSE)

Here you can see the SORTN formula has discarded the duplicate $900 and $740 values from example 6, and instead included a new 5th row with a value $600 that wasn’t in the previous data:

sortn Example 7

Example 8: Top 5 & Show First N Unique Rows & Duplicates

Finally, if you set the third argument to 3, it returns the first n unique rows and any duplicates of these rows.

So in this example, it returns 5 unique rows and 2 duplicate rows:

=SORTN(A2:C21,5,3,3,FALSE)

which looks like this in our Sheet:

sortn Example 8

Example 9: Bottom 5 (Sort Descending)

This example sorts the data from lowest to highest before returning 5 values. It picks the bottom 5 results:

=SORTN(A2:C21,5,0,3,TRUE)

It gives this output in our Sheet:

sortn Example 9

Example 10: Sort On Date Column To Get Most Recent 5

This example sorts on the date column (column index 2) instead of the values column:

=SORTN(A2:C21,5,0,2,FALSE)

This is the output in our Sheet:

sortn Example 10

Example 11: Top 5 & Two Sort Columns

Here’s an example sorting with two columns. Notice how they are both in pairs:

=SORTN(A2:C21,5,0,2,FALSE,3,FALSE)

It gives this output in our Sheet:

sortn Example 11

SORTN Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

It’s part of the Filter family of functions in Google Sheets. You can read about it in the Google Documentation.

See also: How To Highlight The Top 5 Values In Google Sheets With Formulas

2 thoughts on “SORTN Function In Google Sheets”

  1. It seems like Google Sheets and Excel are constantly becoming less and less compatible. If you do anything new and fun (i.e., anything more complicated than a sumif), then, increasingly, you’re using a function that is unavailable in the other spreadsheet. Am I right about that? If so, what does that suggest for the future of the two products?

    1. I’m not sure I agree. Yes, there are differences of course, but in the majority of cases what works in one works in the other.

      Some notable exceptions:

      In Sheets: this SORTN function, QUERY function, plus other Google functions like finance and translate etc.

      In Excel: the new functions like LAMBDA, XLOOKUP etc. although Google generally tries to match Excel closely so we may or may not get some of these new functions in time.

Leave a Reply

Your email address will not be published.