In this tutorial, you will learn how to use the SORTN function in Google Sheets.
How to Use SORTN Function in Google Sheets
Quick Navigation
The SORTN function in Google Sheets is an efficient way to sort your data without having to manually rearrange the rows yourself. It’s especially useful if you have a large dataset that you need to sort, as you can do it in just a few clicks.
The SORTN function takes two arguments: the range of data you want to sort and the number of items you want to return. The function then sorts the data in either ascending or descending order and returns the specified number of items. The function also includes additional options that specify how the function handles ties.
In this guide, we will provide a step-by-step explanation of how to use the SORTN function in Google Sheets.
How to Return the First N Values After Sorting in Google Sheets.
Here’s how to return the first N values after sorting in Google Sheets.
Step 1
First, select an empty cell to place the SORTN function.
In this example we want to determine the best performing months in our dataset according to Metric A. We’ll place our SORTN function in cell A16.
Step 2
Next, double-click on the cell and type the string “=SORTN(“ to start the SORTN function.
Step 3
The first argument of SORTN should be the range you want to sort.
In this example, we want to sort the range A2:E13.
Step 4
Next, indicate the number of rows you want the formula to return.
Since we only need the top 3 months, we’ll indicate ‘3’ as our second argument.
Step 5
The third argument determines what the formula should do when there is a tie.
A value of ‘0’ shows at most the first n rows in the sorted range.
A value of ‘1’ shows at most the first n rows including any additional rows that are equal to the nth row.
A value of 2 removes duplicate rows before showing the results.
Finally, a value of ‘3’ shows the first n unique rows and includes each duplicate in the result.
Step 6
Next, the user must indicate the index of the column to use for sorting.
In this example, we’ve chosen ‘2’ since the values for Metric A are in the second column of the target range.
Step 7
The next argument determines how you want to sort the data. A value of ‘0’ or FALSE sorts the rows in descending order. A value of ‘1’ or TRUE uses an ascending order.
Users may also add additional columns to sort with after this argument. For example, the formula =SORTN(A2:E13,3,0,2,0,3,0) sorts data by Metric A first and then sorts by column B in case a tie occurs.
Step 8
Hit the Enter key to evaluate the SORTN function.
In our example, we were able to determine that the months of August, February, and September are the best performing months according to Metric A.
Summary
This guide should be everything you need to use the SORTN function in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.