Top ten reps
=SORTN(A2:D100, 10, 2, FALSE, 3) returns ten rows sorted by revenue column descending.
What is SORTN in Google Sheets?
SORTN sorts a range and returns only the first N rows. You choose how many rows, which column drives the sort, and ascending or descending order. Leaderboards and best-seller reports use SORTN instead of manual sorting that goes stale.
When to use it
Use SORTN for top ten lists, bottom performers, or largest invoices where the cutoff count is fixed.
When to skip it
Skip SORTN when you need all rows sorted. Use SORT instead. Complex tie-breaking may need QUERY with multiple ORDER BY columns.
How it works
-
1
Pass the full data array including headers if you want them in output.
-
2
Set n to how many rows to return after sorting.
-
3
Choose display_ties_mode when equal values should all appear or be cut off.
-
4
Pick the sort column index and TRUE for ascending or FALSE for descending.
-
5
Leave spill room for n rows plus header if included.
-
6
Refresh source data so SORTN rankings update on dashboards.
Examples in Google Sheets
Lowest inventory
=SORTN(Stock!A:C, 5, 3, TRUE) shows five SKUs with smallest on-hand counts.
Student honors
=SORTN(Grades!A:D, 15, 4, FALSE) lists top fifteen GPA rows for the dean list.
Build this without starting from a blank cell
Use a Better Sheets tool for SORTN, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Using wrong sort column index after inserting a new column left of the key.
- Expecting SORTN to aggregate duplicates before ranking.
- Setting n larger than available rows without handling short lists.
- Including header row in sort range so a text header sorts into results.
- Confusing SORTN arguments with SORT single-key syntax.
Frequently asked questions
- SORTN vs SORT?
- SORT returns all rows ordered. SORTN caps how many rows return.
- How are ties handled?
- display_ties_mode controls whether tied rows all appear or truncate at n.
- Multiple sort keys?
- Add more sort column arguments per SORTN documentation for secondary keys.
- SORTN with FILTER?
- FILTER first to a subset, then SORTN for top rows within that slice.
- Why wrong top ten?
- Check sort column index, text numbers, and blank rows affecting order.
- Can SORTN sort by date?
- Yes when dates are real serial numbers, not text.
- SORTN and QUERY LIMIT?
- Both cap rows. SORTN is native array syntax; QUERY LIMIT uses SQL strings.
Related Tutorials
Watch how SORTN works
How To Format Sheets for Notes
Combine Sheets Into One Sheet As It Happens
How To Sort A Filter
Search Every Function in Google Sheets
Related blog posts
Guides that explain SORTN in more depth.
Figure Out Frequency of Numbers
Discover how to figure out the frequency of numbers, product IDs, text, emails and much more! Essential tips and tricks revealed.
Read post →How to Bring value from other cells | How to Reference an array in a cell
Today’s tutorial will cover how to bring a value from other cells. How do you reference an array in a cell? Anika is a member of Better Sheets and she was the one who asked this question.
Read post →Make It Easier For Others To Use Your Google Sheets
In this tutorial, we're going to orient users better in Google Sheets. We want to help not just ourselves but also anyone who’s going to use a spreadsheet that we have created.
Read post →Related glossary terms
FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →UNIQUE
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
Read guide →QUERY
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
Read guide →SEQUENCE
SEQUENCE returns a grid of sequential numbers. You set how many rows and columns, the starting value, and the step between values. It replaces manual 1, 2, 3 fill patterns and pairs well with MAP, LAMBDA, and date math for generated row labels.
Read guide →Done reading about SORTN?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.