How To Use SORTN Function In Google Sheets (With Examples)

Whether you’re a spreadsheet novice or a seasoned pro, the SORTN function in Google Sheets can be a game-changer when it comes to data manipulation.

In today’s spreadsheet tutorial, we’ll dive into the world of Google Sheets and explore how to harness the potential of the SORTN function.

We’ll start with the basics, making it easy for beginners to understand, and then move on to real-world scenarios where the SORTN function can become your go-to tool for data organization.

Let’s kick off the journey by reviewing a scenario that helps convey the message better.

Scenario/Use Case

Imagine you’re a small business owner and have been using Google Sheets to keep track of your monthly expenses. Your spreadsheet contains a list of expenses, including the date, category, description, and cost. As your business grows, your expenses start piling up, making it challenging to quickly identify critical trends or pinpoint the most significant expenses.

This is where the SORTN function in Google Sheets comes to the rescue. Instead of manually sifting through your expense list, you can use SORTN to create a clear, organized view of your expenses.

Let’s say you want to identify the top 5 highest expenses in your business for the past year, you can leverage the SORTN function in Google Sheets to quickly generate a new table that lists these expenses, allowing you to prioritize where to cut costs or allocate resources more efficiently.

Understanding The SORTN Function Syntax

Before we delve into the practical aspect of this guide, let’s quickly break down the SORTN function syntax. After reading this, you’ll be confident using the SORTN function in Google Sheets to filter and sort your data.

For starters, here is what the SORTN function looks like.

=SORTN(range, num, [display_ties_mode], [sort_column], [sort_order])

That said, here’s what each part of the syntax means:

range: This is where you specify the range of cells that contains the data you want to sort and filter. Think of it as telling Google Sheets, “Hey, this is the data we’re working with.”

num: This number represents how many items or rows you want to retrieve after sorting. It’s like saying, “I want the top 5 (or any number you choose) items from this data.”

[display_ties_mode] (optional): Square brackets mean this part is optional. You can skip it if you don’t need it. If you do use it, you have three options:
- 0 (Zero): This means if there are tied values (values that are the same), it will display all of them.

1 (One): If there are tied values, it will display only one of them.

-1 (Minus One): If there are tied values, it will display the first one it encounters.

[sort_column] (optional): Another optional part is enclosed in square brackets. This is where you specify the column number you want to sort by within your range. It’s like saying, “Sort this data based on the values in column 3 (or any column you choose).”

[sort_order] (optional): The final optional part. Here, you decide how you want your data sorted:

TRUE or 1: This means sorting in ascending order (from smallest to largest).
FALSE or 0: This means sorting in descending order (from largest to smallest).

Using The SORTN Function In Google Sheets to Find The Highest Value Orders In A Spreadsheet

Now that we’ve gained a solid understanding of the SORTN function and explored a real-world scenario where it proves invaluable, it’s time to roll up our sleeves and dive into the practical application of this powerful tool in Google Sheets.

In this first example, we will continue with our small business owner’s journey and explore a more complex use case. Imagine you have a vast database of customer orders and need to efficiently extract critical information, such as the highest-value orders or the most recent ones. The SORTN function can make this seemingly daunting task a breeze.

For this example, we will use the following sample data:

With our sample data now put together, let’s quickly go over the step-by-step process of using the SORTN function in Google Sheets to filter and sort the data in our spreadsheet.

Copy the Example Google Sheet

To practice using the SORTN function, either copy our sample sheet provided in the link below

Click Here to Copy

Or apply the steps outlined in your own Google Sheets document.

Step 1: Choose a Blank Cell

Let’s begin by choosing a blank cell where we want the sorted result to be generated. For this example, we will use cell G2. So, let’s quickly select that cell in our spreadsheet.

Step 2: Applying the SORTN Function

After choosing the cell where you want the sorted result generated, it’s time to apply the SORTN function. To do that, navigate to the formula bar and type the following formula.

=SORTN(A2:D13, 5, , 4, FALSE)

Step 3: Hit Enter

After typing in the SORTN formula above as instructed, press the Enter button on your keyboard. Google Sheets should automatically generate the result in the selected section of your spreadsheet.

Here, take a look at what hours looks like:

From the screenshot above, you can clearly see that we have been able to use the SORTN function in Google Sheets to neatly sort our spreadsheet to display the 5 highest-value customer orders.

Armed with this information, we can quickly identify customers who have made the most significant purchases, allowing us to tailor our marketing efforts or provide special incentives to these high-value clients.

How to Use the SORTN Function to Find the Lowest Value Orders In A Spreadsheet

In our journey of mastering the SORTN function in Google Sheets, we’ve already learned how to find the top values. But what if you need to identify not only the highest values but also the lowest ones? Fear not, because SORTN is a versatile tool that can easily handle this task.

In this section, we’ll walk you through how to use SORTN to find the lowest values in your data.

Imagine you’re managing a list of monthly sales for your online store. You want to identify the top 3 months with the lowest sales. This information can help you understand your sales trends and make strategic decisions.

Here is how to use the SORT function to get it done.

For this example, we will use the following sample data:

Step 1: Choose A Blank Cell

As we did with the previous example, we need to choose a blank cell in our spreadsheet. This is where we want our results to be generated. For this example, we will select cell E2.

Step 2: Apply The SORTN Formula

Once we have selected the cell where we want our result generated, we only need to apply the SORTN formula. To do it, navigate to the formula menu and type in the following formula:

=SORTN(A2:B13, 3, , 2, TRUE)

Step 3 Hit Enter

Finally, press the “Enter” key on your keyboard, and Google Sheets will work its magic. It will display the result in the cell you selected.

Here’s what your spreadsheet will look like once you’ve used the formula:

The screenshot above shows that we have successfully used the SORTN function to filter our data set to only display three months with the lowest sales.

Now, that’s how to use the SORTN function in Google Sheets.

Final Thoughts

In your journey to harnessing Google Sheets’ potential, mastering the SORTN function is a valuable step. Here is what’s interesting: the SORTN function simplifies the complex task of data sorting and filtering. It empowers you to swiftly find your dataset’s top or bottom values, making your spreadsheet tasks more efficient.

With SORTN in your toolkit, you’ve gained the ability to make informed decisions, simplify data analysis, and uncover insights, whether you’re a spreadsheet novice or an aspiring data professional.