Learn the SORTN Google Sheets Function in 6 Easy Steps

Google Sheets is a great online software tool that allows you to create and sort your spreadsheets efficiently.

One function that allows such sorting is the SORTN Google Sheets function, which will enable you to sort an “n” number of items. This article will discuss the function, how it works, and how you can use it in Google Sheets. Read on to master this vital aspect of Google Sheets.

What Is SORTN in Google Sheets?

The Google Sheets SORTN formula allows you to sort a specified range of data. It returns the first “n” number of items in that range. So, it’s quite literally “sort n.” If no number is specified, the formula will display the smallest value (1).

Using this formula significantly reduces the steps it needs to find a value in a table and sort your data.

SORTN is a lot similar to the FILTER function in nature. However, SORTN returns the highest “n” amount while the FILTER function will return the matching rows.

What Is the Google Sheets Sort Formula?

The formula for SORTN is a fairly long one as it contains significantly more references than many other functions. To help you understand the formula, let’s look at the syntax first. Here is the Google Sheets SORTN structure:

=SORTN(range, [n], [display-ties-mode], [sort-column1, is-ascending1], ...)

The arguments used in this formula are:

• range – This is the data you want to sort.
• n – This argument is optional, and it’s used to represent the item number in the dataset you wish to return. It is 1 by default and should always be greater than zero.
• display-ties-mode – This is an optional argument used to represent which items are returned when there is a tie in the data being sorted. This can be between 0 and 3. This is what the options do:
  1. 0 – Only show the first “n” number of rows in the range at the most.
  2. 1 – This shows the first “n” number of rows. Additional rows similar to the nth number of rows will also show.
  3. 2 – This shows the first “n” number of rows after removing the duplicates.
  4. 3 – This shows the first “n” number of rows and their copies.
• sort-column1 – This is also an optional argument. These are the cells of the column in a range or an outside range that has the values to sort it with.
• is-ascending1 – This optional parameter allows you to choose whether you wish to sort your data in an ascending or descending order. Setting this parameter as TRUE sorts the data in ascending order while positioning it as FALSE sorts data in descending order.
• sort-column2 – This is an optional argument and works the same way as sort_column1. You can keep placing additional sort_columns.
• is-ascending2 – This optional argument allows you to specify whether the sort-column2 is ascending or descending.

How to Use SORTN in Google Sheets

To better understand the SORTN function in Google Sheets, it’s best to demonstrate it with an example. Here is a sample set of data we will be using for this demonstration:

We want to sort the items with the most units sold, and we only wish to show the top 3 items.

Here is what the formula will look like in this particular case:

=SORTN(A2:C8,3,0,C2:C8,FALSE)

The end result after using the formula should look something like this:

Let’s take a look at what each argument in the formula does in this example:

1. A2:C8 – This is the range we wish to include in the SORTN formula.
2. 3 – This number signifies how many units we want to show after sorting. We only want to show the top three items. So, the number here is 3.
3. 0 – This argument specifies how ties should be handled. We have set it to 0, which means only the first “n” rows will be shown at most.
4. C2:C8 – This range specifies the column used to sort the data. We wish to sort our data by the highest sales, selecting the data under the Units column.
5. FALSE – This argument controls how the function sorts the data. We are sorting the data from the highest sold item to the lowest, so we want our data to be in descending order. So, we used the FALSE argument here.

Now that we have gone over the example, we can go over how you can utilize the function. Let’s use the SORTN formula on another set of data. Here is a step-by-step process on how to sort with this function:

Step 1: Type =SORTN( in the first cell you want your sorted data to show. Google’s formula suggestion feature is a great way to help you out here as it will start suggesting the formula as soon as you start typing it. Clicking on it will autofill the formula in the box.

Step 2: Once you input =SORTN(, the parameter will be shown, which you can use to fill in your data. Here, the data range goes from A2 to C13. You can manually type it or click and drag it.

Step 3: You must enter the number of values you wish to display. We enter the value of n separated by a comma to do this. In this example, let’s find the top 4 months with the highest number of sales by entering 4.

Step 4: Now, we have to write in the value that defines how Google Sheets will handle the ties in your sorted data. In this particular case, we are setting it to 1 to show also show similar data, in case there are any months with the same sales. Then add another comma.

Step 5: Input the cell range through which we wish to sort the data. In this case, the cell range used is from C2 to C13. Simply input it and add in a comma. Finally, we add whether the data will be sorted in ascending or descending order.

Step 6: The is_ascending parameter specifies if the data is sorted in ascending or descending order. If the parameter is TRUE, the data will be in ascending order. If FALSE, then the data is sorted in descending order. In this case, we used false. Press Enter to execute the formula, and you will see the information sorted neatly in the cells.

The data is now sorted, and we can see that most sales are in April. You can also add headings to ensure the data has proper labeling and is sorted neatly.

How to Deal With Errors With the SORTN Function

Here are a few errors you can encounter when using the SORTN function. And ways in which you can fix them.

#REF! – The error displayed says, “Array result was not expanded because it would overwrite data in…”. This error can occur when the cell doesn’t have enough space to display the data. Either the cells below or the cells to the right are blocking the output from being displayed. Consider deleting the data in the blocking cells or moving the data to another cell to fix this error.

It may also be a circular dependency error from incorrectly entering your data into the formula.

#VALUE! – This error is displayed when the input is not in a continuous range of cells. You may have some data in cell ranges A1:A4 and A6:A9. Consider changing so that all the data exists in a single cell range.

SORTN Function Google Sheets FAQ

What Is SORTN in Google Sheets?

The SORTN function allows you to sort a data range and return the first “n” number of items from that range. SORTN reduces the number of steps needed to get the data out of the table. This formula allows you to create a new range of data with the sorted output.

How Do You Use SORTN?

You can use the SORTN fucntion in Google Sheets by using the following formula:

=SORTN(range, [n], [display-ties-mode], [sort-column1, is-ascending1], ...)

You can also access this function by selecting the cell, clicking on Insert in the top bar, and then clicking on Function in the dropdown menu. Then, click on Filter and SORTN.

The Bottom Line

Many people work in finance fields where they work with a lot of data that needs to organize. But they’re not the only users that would benefit. The SORTN Google Sheets function can be very helpful for many users in a number of different situations.

Using this function can be a beneficial way to manage data efficiently. You can also easily use the Google Sheet SORTN to sort data by the number of appearances.

We hope this article helped you understand the formula and how it works. If you have any questions please feel free to ask in the comments.