How to Use the SORTN Function in Google Sheets

If you want to sort a dataset, but only return a certain number of items in Google Sheets, the SORTN function will come in handy.

Not many people are aware of this function, however, it definitely has its uses.

In this tutorial, I will show you how to use the SORTN function in Google Sheets

SORTN Syntax

The SORTN function will sort a dataset and only return “n” number of items from the original dataset according to the number that you place within the parameters of the function.

So if you want to sort a dataset, but only return the top ten rows, that could be done very easily with this function.

The syntax of SORTN is:

=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], …)

• range – this is the data that you want sorted and only n number of items returned from
• n – this is an optional argument and will be set to 1 by default. This is the number of items that you want to be returned from your sorted range. It has to be greater than zero
• display_ties_mode – this is an optional argument and will be set to 0 by default. It is a number representing which items are returned when there are ties in the sorted data. These are your options:
  • 0: show the first n rows in the sorted range at most
  • 1: show the first n rows, and any additional rows that are identical to the nth row at most (will show rows that tie the nth row)
  • 2: Show the first nth rows after removing duplicates
  • 3: Show the first nth unique rows, and show duplicates of these rows
• sort_column1 – this is an optional argument. This is the index of the column in range or a range outside the range that contains the values to sort by. This must be a single column that is the same size as the range
• is_ascending1 – this is an optional argument that controls how the data is sorted. TRUE sorts in ascending order, FALSE sorts in descending order
• sort_column2 – this is an optional argument, you can continue to place additional sort columns here
• is_ascending2 – this is an optional argument, you can continue to place additional sort orders here

Using the SORTN Function

To show you how to use SORTN, I will be setting up a SORTN formula with this example data:

What I want to do is sort by the items with the highest sales and only return the top 5 items.

The formula I would use to do this is:

=SORTN(A2:C11,5,0,C2:C11,FALSE)

Here is how this looks in my spreadsheet:

Let’s breakdown what each parameter in this formula is doing:

• A2:C11 – this is the range that I want to sort by, it is my entire dataset
• 5 – this number represents how many items I want to return from the sorted range. I only want to return the top five items, so my number is 5
• 0 – this number controls how ties will be dealt with if there are ties in my sorted data. I have set it to zero which will only show the first n rows at most
• C2:C11 – this is the column that my data will be sorted by. I want to sort by sales so I put here the column that contains the sales
• FALSE – this controls how my data is sorted. I am sorting by C2:C11 and I want the highest number to be on top, so I set it to FALSE for descending order

Now that we have gone over an example of the function is used, let’s go over step by step how to use it:

1. In the cell where you want the sorted data to appear, type =SORTN and press Tab to begin entering into the formula. In this first argument place the range that contains the data that you want sorted. When you are done with this step add a comma

2. Next place a number that represents how many items you want to be returned from the sorted data. In this example, I will only return 3 items so I place a 3 here. Enter a comma when you are done

3. Next place a number that represents how ties in the sorted data will be dealt with. I have set this to 1. Add a comma when you are done

4. Next put in the column that you want to sort by. I am sorting by sales in this example, so my column is C2:C10. When you are done with this step add a comma

5. Next you will place either TRUE or FALSE depending on how you want your data sorted. I want to sort by descending order (largest number on top) so I use FALSE.

6. Add your closing parenthesis and press Enter to calculate the formula. In my example the range was sorted by sales from largest to smallest and only the top 3 items were returned

Closing Thoughts

SORTN is a great function that not many users know about.

With this function, you can sort a dataset and only return however many items you want to deal with. Normally doing this would take multiple functions, but with this one function, you can perform this all in one step.

More Google Sheets Tutorials:
How to Use the SORT Function
How to Auto Sort