If you have a massive dataset—such as a list of 1,000 sales transactions—and you want to extract just the top 5 highest-value deals, your first instinct might be to sort the sheet and manually copy the top five rows.
However, manual sorting is static. When a new, massive deal is entered tomorrow, your manual list is immediately outdated. To build automated dashboards that dynamically pull the “Top 10” or “Bottom 5” performers from raw data, you must use the SORTN function.
In this guide, you will learn how to use the SORTN function in Google Sheets to instantly extract ranked lists.
Understanding the SORTN Syntax
The SORTN function is a specialized version of the standard SORT function. It sorts a range of data and then truncates the result, returning only the first N number of rows.
=SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])- range: The data you want to extract and view.
- n: The number of rows you want to return (e.g., 5 for a Top 5 list).
- display_ties_mode: How to handle identical numbers (usually set to 0).
- sort_column: The specific column index (1, 2, 3) to base the sorting on.
- is_ascending: Set to FALSE to sort largest to smallest (Top performers), or TRUE to sort smallest to largest (Bottom performers).
Use Case 1: Finding the Top 5 Sales Reps
Assume you have raw data in Columns A, B, and C. Column A is the Date, Column B is the Rep Name, and Column C is the Revenue Amount.
You want to create a clean table elsewhere on the dashboard that dynamically shows the Top 5 largest sales deals, displaying the Rep Name and the Revenue.
Click on an empty cell in your dashboard and type:
=SORTN(A2:C1000, 5, 0, 3, FALSE)Here is how Google Sheets interprets this command:
- Look at the raw data range (A2:C1000).
- Sort the data based on Column index 3 (the Revenue column).
- Sort it in Descending order (FALSE) so the massive numbers are at the top.
- Chop off the rest of the data, outputting only the top 5 rows.
The formula will instantly “spill” a clean, 5-row table into your dashboard. If a new $100,000 deal is added to the raw data tomorrow, the SORTN formula will automatically recalculate and push the new deal to the top of the leaderboard.
Use Case 2: Removing Duplicates (Display Ties Mode)
The third argument in the formula (display_ties_mode) is incredibly useful for managing messy data. By default, if you set it to 0, the function simply returns exactly N rows, regardless of ties.
However, what if you want a list of your Top 5 highest-selling products, but your raw data has the same product listed hundreds of times (one for each transaction)?
Change the tie mode to 2. This instructs SORTN to remove all duplicate rows after sorting.
=SORTN(A2:C1000, 5, 2, 3, FALSE)This ensures your leaderboard actually displays 5 distinct, unique products, rather than the same wildly popular product taking up all 5 slots.
By integrating the SORTN function into your workflow, you can build dynamic, real-time leaderboards that require absolutely zero manual maintenance.