When you only need the top few rows from a sorted list, SORTN is faster than sorting everything and then trimming. The SORTN function in Google Sheets pulls the first N rows after sorting, with handy options for ties.
This article walks through four SORTN examples, from picking the top 3 to handling ties cleanly and grabbing a single best row.
SORTN Function Syntax in Google Sheets
SORTN sorts a range, then keeps only the first N rows of the result.
=SORTN(range, [n], [display_ties_mode], [sort_column1], [is_ascending1], ...)
- range – the data range to sort
- n – the number of items to return after sorting, [optional, defaults to 1]
- displaytiesmode – 0 shows no ties, 1 shows all ties with the last row, 2 keeps only unique sort keys, 3 keeps the first unique rows, [optional]
- sort_column1 – the column number to sort by, [optional]
- is_ascending1 – TRUE for ascending, FALSE for descending, [optional]
When to Use SORTN Function
- You want the top 3 (or top N) rows from a longer list without sorting the source data.
- You need the lowest or smallest N values from a column.
- You want a dynamic leaderboard that updates as values change.
- You need a way to show all rows tied with the cutoff position, not just an arbitrary slice.
Example 1: Get the Top 3 by Score
Let’s pick the three highest-scoring rows from a small list of names and scores.
Below is the dataset, six rows with names in column A and scores in column B.
You want only the top three rows ordered by score, highest first.
Here is the formula:
=SORTN(A2:B7, 3, 0, 2, FALSE)
SORTN sorts the range by column 2 (Score) in descending order (FALSE = not ascending), then keeps the top three rows. Eva comes out on top, with Bob and Carla right behind.
Pro Tip: If you only need a single value (not the whole row), LARGE and SMALL are simpler. SORTN earns its keep when you want the row, not just one cell.
Example 2: Get the Bottom 3 in Ascending Order
Flip is_ascending to TRUE and SORTN pulls the smallest values instead of the largest.
Below is the dataset, the same six-row Name/Score table.
You want the three lowest scores in ascending order.
Here is the formula:
=SORTN(A2:B7, 3, 0, 2, TRUE)
Same shape as Example 1, only the last argument changed. David’s score is the lowest, then Alice, then Frank. The result spills into a 3-row by 2-column block.
Example 3: Show All Ties When Ranking Top Values
Setting display_ties_mode to 1 tells SORTN to include every row tied with the final cutoff row. This is the safer choice for leaderboards.
Below is the dataset, five rows where two people are tied at the same score.
You ask for the top 2, but two rows share the score at position 2.
Here is the formula:
=SORTN(A2:B6, 2, 1, 2, FALSE)
With ties mode 1, SORTN includes both Bob and Carla because both are tied with the second-place score. The spill comes out three rows instead of two so no tied row gets dropped arbitrarily.
Pro Tip: Ties mode 0 is “ignore ties,” 1 is “show all ties,” 2 is “unique sort keys only,” and 3 is “first unique rows.” Pick 1 for fair leaderboards.
Example 4: Return Just the Single Best Row
Set n to 1 when you only need the winner, like top seller or highest score.
Below is the dataset, five products with sales numbers in column B.
You want the product with the highest sales.
Here is the formula:
=SORTN(A2:B6, 1, 0, 2, FALSE)
SORTN sorts by sales descending and keeps just the top row. Notebooks come out on top here. This pattern is great for dashboards where you only have room for one line.
Tips & Common Mistakes
- Tie mode matters. Mode 0 silently drops tied rows beyond the cutoff. Use mode 1 if your data has ties and fairness matters.
- Sort column is 1-based inside the range. Counting starts from the first column of the range you pass in, not from column A of the sheet.
- Pair with FILTER for conditional top-N. Combine SORTN with FILTER, SUMIFS, or MAXIFS to rank rows that meet specific criteria.
SORTN is the quiet workhorse for top-N and bottom-N reports. Once you have it in your toolbox, you’ll stop building helper columns to rank and trim.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: