SortN in Google Sheets: A Practical Top-N Sorting Guide
A comprehensive, developer-friendly guide to using SORTN in Google Sheets for top-N data extraction, with examples, keyboard tips, and best practices for dashboards and reports.
SortN in Google Sheets returns the top-n rows from a data range, sorted by one or more columns. It’s ideal for dashboards, leaderboards, and quick analyses where you need a compact view of the best or latest items. This guide covers syntax, practical examples, and how to combine SORTN with FILTER and other functions. According to How To Sheets, using SORTN can simplify top-N extraction and keep your formulas maintainable.
What is sortn google sheets and when to use it
SORTN is a Google Sheets function designed to return a subset of rows from a dataset, keeping only the top or bottom n results based on one or more sort keys. It’s particularly useful for dashboards, leaderboards, and quick analyses where you want a compact view of the best performers, newest records, or highest-valued entries. The key advantage is that you specify how many rows you want and which column (or columns) determine the order, all in a single formula. For long-running worksheets, SORTN can be more efficient than layering multiple SORT and FILTER calls.
=SORTN(A2:C100, 5)This basic usage returns the top 5 rows from A2:C100 using the default sort order derived from the first column. If you want to define explicit criteria, you can add the sort keys and order after the n parameter, just remember that each additional key extends the sort criteria. As you adopt SORTN in your sheets, you’ll discover stable results even as your data grows.
tip1RoomNote:anythingableNoteReadOnlyForCodeBlock
wordCountSection1Obsitory?0
Steps
Estimated time: 15-25 minutes
- 1
Open your dataset in Google Sheets
Navigate to the sheet that contains the data you want to sort. Identify the range (e.g., A2:C100) and determine which column will drive the sort order.
Tip: Label your columns clearly to avoid confusion when selecting sort keys. - 2
Decide the top-n you need
Choose how many rows you want to return (n). This number should balance readability with the dataset size. Start with 5 or 10 for dashboards.
Tip: If the data grows, you can adjust n without changing other parts of the formula. - 3
Choose sort criteria
Decide which column(s) determine the sort order. For multi-criteria sorts, you’ll add more keys after the n parameter.
Tip: Prefer stable columns (e.g., dates or IDs) to ensure deterministic results. - 4
Apply SORTN with the chosen parameters
Insert the SORTN formula, starting with your range and n, then add any sort keys as needed. Validate that the output spills correctly into adjacent cells.
Tip: Use IFERROR around SORTN to handle empty or non-n data gracefully. - 5
Optionally filter before top-n (dynamic lists)
If you want only a subset of rows, apply FILTER first, then wrap with SORTN to get the top-n of the filtered data.
Tip: FILTER reduces the data size prior to sorting for faster results. - 6
Place results on a dedicated sheet
Output SORTN results to a dashboard area or a separate sheet to avoid overwriting source data.
Tip: Consider using named ranges for readability. - 7
Test with data updates
Add new rows to your dataset and verify that the SORTN results update automatically.
Tip: Avoid hard-coding values; rely on dynamic ranges. - 8
Validate edge cases
Test with ties, blank rows, or negative values to confirm stable output.
Tip: Use - if applicable - explicit sort orders. - 9
Document the formula for others
Add a short note next to the formula describing what it does and its parameters.
Tip: Clear documentation saves time for teammates.
Prerequisites
Required
- Required
- Basic knowledge of Google Sheets formulas (SORT, FILTER, and, optionally, QUERY)Required
- A sample dataset in Google Sheets for hands-on practiceRequired
Optional
- Familiarity with array formulas for advanced patternsOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells or formulas | Ctrl+C |
| PastePaste to the target location | Ctrl+V |
| Paste values onlyPaste without formatting to preserve data types | Ctrl+⇧+V |
FAQ
What is SORTN and how does it differ from SORT?
SORTN returns a subset of rows (top or bottom n) from a range, based on one or more sort keys. SORT sorts the entire range and returns a full, ordered dataset. Use SORTN when you only need a compact top-N view and SORT when you want a full sorted list.
SORTN gives you just the top rows; SORT sorts everything. Use SORTN for compact outputs and SORT to view the full sorted dataset.
Can SORTN sort by multiple columns?
Yes. You can provide additional sort keys after the initial n parameter to define multi-column sorting. The first key defines the primary order, and subsequent keys refine the ranking.
You can set more sort keys after the n value to sort on multiple columns.
How to handle ties when using SORTN?
SORTN uses the specified sort keys to break ties. For deterministic results, include additional stable keys (e.g., date or unique IDs) after the primary keys.
Ties are broken by extra sort keys you provide; include stable keys for consistency.
Can I use SORTN with IMPORTRANGE or FILTER?
Yes. You can combine SORTN with IMPORTRANGE to pull top-N rows from another sheet, or wrap FILTER inside SORTN to top-N a filtered subset.
SORTN plays nicely with data pulled from other sheets and with filtered data.
What happens if the data has blanks or non-numeric values?
SORTN may produce unexpected results if the sort keys contain blanks or non-numeric values. Normalize data types or filter out blanks before sorting.
Blanks and wrong data types can affect results; clean your data or filter first.
Is SORTN available in all Google Sheets versions?
SORTN is a standard function in Google Sheets and should be available in current versions. If you don’t see it, ensure you’re signed into a Google account with Sheets access.
SORTN should be available in standard Google Sheets. Ensure you’re logged in and using the latest Sheets.
The Essentials
- Master SORTN to extract top-N rows with a single formula
- Compare SORTN with SORT to choose the simplest approach for a given task
- Combine SORTN with FILTER/QUERY for dynamic dashboards
- Be mindful of data size and potential ties when sorting