SORTN function in Google Sheets is useful when you intend to sort a range of data and return only a number of items from the sorted result.
When working on datasets like rankings or catalogs in Google Sheets, you may have encountered instances wherein you need to sort and filter the top items from a particular category. Of course, you can achieve this through a combination of functions, but the
SORTN function may come in handy to simplify your work.
SORTN lets you reduce what could otherwise require two to three functions down to just one. With it, you’ll be able to save a significant amount of time so you can get your work done more efficiently.
Let’s consider this use case.
Suppose you’re keeping a record of agents with their corresponding sales for three months. Identifying the top agents in terms of sales should be easy if you only have three to five of them. But, it’s a different story if you have more, like hundreds or even thousands of records.
You’re probably already aware of the SORT function, and you might turn to it if you haven’t heard of
SORTN yet. While it’s true that you can easily sort a range of cells in ascending or descending order using the former, you won’t be able to immediately extract the records of, say, your top three agents unless you use another function like
Sounds tedious, right? If only you can combine these functions into one. Well, you’ll be glad to know that
SORTN does just that. This function does the job of sorting and, at the same time, pulling out the top records of a given range.
With this function, you don’t have to worry about writing complicated syntaxes in your Google Sheets just to return a subset of large data.
Now that you have an overview of what
SORTN can do, let’s put it to the test in a real-world setting so that you can realize more of its practical applications. We’ll start by understanding its anatomy.
The Anatomy of the SORTN Function
Writing a function in Google Sheets requires a particular syntax or structure. For
SORTN, this is how we should write it:
Let’s break down the syntax and understand each component:
- = just like other functions, the equal sign is the first character we should type in to use
- SORTN() this is our
SORTNfunction. Writing this basically means that we are commanding Google Sheets to utilize this function for our parameters.
- range is our first required parameter. This is the cell group that
SORTNwill function within.
- [n] is a numeric value that controls the number of records you want the function to return. You can omit this parameter if all you want to do is return one row. Otherwise, you’ll have to specify in here the number of rows.
- [display_ties_mode] is another optional parameter that can be used to specify how to treat ties in the output:
- 0 is the default value, so you don’t have to write it if you intend to use this mode. Using the default mode shows your specified number of top records regardless of ties or duplicates.
- 1 should be the value if there’s a need to show the ties or duplicates of your top record.
- 2 can be used to display only one instance of your top records, assuming they have duplicates.
- 3 is the mode you should use to return all your specified number of top records, including duplicates.
- [sort_column] is an optional parameter utilized especially for cell ranges that include multiple columns. Here, you’ll have to specify the index of the column you are sorting.
- [is_ascending] defines how the range gets sorted -setting it to TRUE means in ascending order, while FALSE value means descending.
A Real Example of Using SORTN Function
Below is an example that shows how to use
SORTN in sorting and displaying only the top records of a given range.
As you can see, we used the
SORTN function to return the top three agents based on their total sales.
Now, let’s try to dissect the parameters of our function to understand how it works.
For our first parameter, we used the agent names and their total sales (A2:B11) as our range for our
SORTN function. We only want the top three agents, so we indicated number 3 right after the range to let the function display only three of the highest records.
Since our cell range contains two columns (A and B), we let the function know that column B is our criterion in sorting and determining the highest total sales of agents. Hence, we specified its column index, 2, as our [sort_column].
Notice that our last parameter, [is_ascending], holds FALSE. We assigned this value so that the records get sorted in descending order.
Let’s wrap up this practical example in a simple explanation.
In this scenario,
SORTN identifies all its parameters and sorts them based on the given criteria. With the sorted values, the function proceeded to display only the top three records. It’s actually pretty simple, isn’t it?
Go ahead and make a copy of the example data so you can play with the
SORTNfunction. Don’t worry because I’ll be guiding you every step of the way. We’ll be using the same data, so make sure to have a copy and open the spreadsheet I’ve provided above.
How to Use SORTN Function in Google Sheets
- Click on the cell where you intend to display the output. For the purposes of this guide, I added new output columns with the headings, Lowest Sale and Highest Sale respectively. Let’s focus first on displaying the agent with the lowest sale, so, click cell F2.
- With the cell already active, type the equal sign ‘=’ immediately followed by the name of our function which is SORTN. You’ll notice that as you type in the function, an auto-suggest box appears below it. You may opt to complete typing SORTN( or click on the
SORTNfunction suggestion to expedite the process.
- Let us now define the parameters of our function. First, let’s set the cell range that contains the records we need to sort. We have to include all the agents and total sales, so our cell range should be A2:B11.In Google Sheets, you can set a cell range parameter in two ways—by typing it manually or by clicking and dragging over the group of cells. Choose whichever method you prefer. You’ll see that the cell range you define gets highlighted on the spreadsheet.
- The next parameter should contain the number of records to return. Our goal is to display only the lowest sale, so we need to define 1 as our [n].
- We’ll need to consider the possibility of having a tie in the total sales of our agents. Therefore, we need to set [display_ties_mode] parameter to 1. As mentioned before, having 1 as the value of this parameter sets the function to show also the ties of the result.
- Of course, the function needs to know what column should be the basis in performing the sorting process. Since column B contains the sales records and is the second column in the range, we will have to enter 2 as [sort_column].
- Our last parameter [is_ascending] defines the sorting process of our function. Remember that we are trying to find out the lowest sale. To achieve this, the records should be from lowest to highest or ascending as we commonly call it.Treat this parameter as a confirmation that asks whether the sort process is ascending or not. Here, there are only two possible answers—TRUE or FALSE. We’ll need to enter TRUE to confirm that the sorting process is indeed ascending.
- The last thing we need to do is to close our function. We need to type in the close parenthesis ‘)’ and hit the Enter key with all the parameters already defined. You should now have an output similar to the image below.
Not difficult at all, right? This time, try to find out the agent with the highest sale and display its result on cell H2. Just follow the same steps discussed earlier, but this time, make sure to set [is_ascending] to FALSE. If your function is correct, you should have this output.
Congratulations for making it this far! Now you know everything there is to know about the SORTN function. You can use it along with other Google Sheets formulas to streamline your work.