SORT vs SORTN in Google Sheets
SORT sorts everything. SORTN sorts everything and then returns only the top N rows. The difference matters more than it sounds — SORTN's tie-handling modes make it the go-to function for leaderboards, top-10 lists, and any situation where you need "just the winners" instead of the full ranking. This guide draws the line.
⚡ Quick Answer
Use SORT when you want the entire range sorted — for full leaderboards, alphabetized directories, or dashboards where users scroll through everything. Use SORTN when you want just the top N rows after sorting — top 10 customers by revenue, worst 5 performing days, latest 20 orders. SORTN also has tie-handling modes SORT lacks.
📋 What's in this comparison
SORT — the basics
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
Returns the range with all rows sorted by the specified column. Multiple sort keys let you specify tiebreakers.
=SORT(A2:C100, 3, FALSE) // sort by col 3 descending
=SORT(A2:C100, 3, FALSE, 1, TRUE) // then by col 1 ascending as tiebreaker
SORT returns EVERY row of the input, just in a new order. If your input is 100 rows, the output is 100 rows. That's the key trait — SORT is exhaustive.
When SORT is right
- You need the complete data sorted for browsing or export
- You're building a full leaderboard where every entry matters
- You're chaining sorting into another formula (like FILTER + SORT)
- You want the original range's data reorganized without dropping any rows
SORTN — sort and trim
=SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], ...)
Same as SORT, but with two extra arguments up front:
- n — how many rows to return. Default: 1.
- display_ties_mode — how to handle ties at the boundary. Default: 0.
=SORTN(A2:C100, 10, 0, 3, FALSE) // top 10 by column 3 desc, ignore ties
SORTN returns AT MOST N rows (or exactly N depending on tie mode). Perfect for top-N reports.
When SORTN is right
- Top 10 customers by revenue
- Latest 20 support tickets
- Worst 5 performing days
- Any dashboard tile that shows "top N of..."
- Leaderboards where only the top places matter
SORTN's four tie-handling modes
The third argument controls what happens when the Nth and (N+1)th rows have the same sort value. This is unique to SORTN — SORT has no equivalent because it always returns every row.
| Mode | Behavior | Best for |
|---|---|---|
0 | Return exactly N rows. Ties at boundary broken arbitrarily. | Fixed-size lists where exact count matters |
1 | Return N + all rows that tie with the Nth. May return more than N. | Fair leaderboards — "everyone tied for 10th place gets shown" |
2 | Return N with duplicates removed based on sort column. | Distinct top-N by rank (fewer rows if many ties) |
3 | Return top N distinct values in the sort column. | Top N distinct scores/prices/categories |
Concrete example — tie handling
Data: 8 rows with scores 100, 90, 90, 80, 75, 70, 70, 65. You ask for top 3.
| Mode | Returns | Explanation |
|---|---|---|
| 0 | 100, 90, 90 | Exactly 3 rows. The other 90 is dropped arbitrarily. |
| 1 | 100, 90, 90 | All 90s at the boundary are included. Same result here (2 ninetys). |
| 2 | 100, 90 | Distinct sort values. Both 90s collapse to one row. |
| 3 | 100, 90, 80 | Top 3 DISTINCT values, not top 3 rows. |
Side-by-side comparison
| Aspect | SORT | SORTN |
|---|---|---|
| Rows returned | All input rows | Up to N (or exactly N) |
| N argument | N/A | Yes (2nd argument) |
| Tie handling modes | None | 4 modes (0-3) |
| Multiple sort keys | Yes (pairs after 3rd arg) | Yes (pairs after 5th arg) |
| Ascending flag | 3rd argument | 5th argument |
| Speed | Fast for full-range sort | Slightly faster for top-N (early termination possible) |
| Available in Excel | Yes (2019+/M365) | No — Sheets exclusive |
| Chains with FILTER | Yes | Yes |
| Best for | Full sorted ranges | Top-N reports |
Same problem, both approaches
Top 10 salespeople by revenue
SORT — need to trim
=ARRAYFORMULA(
QUERY(
SORT(Sales!A2:C100, 3, FALSE),
"LIMIT 10"))
Wraps SORT in QUERY to trim. Works but awkward.
SORTN — one clean formula
=SORTN(Sales!A2:C100,
10, 0, 3, FALSE)
Second argument = 10. Direct and readable.
Leaderboard where ties get equal ranking
SORT — hard
-- No native tie handling.
-- Would need complex helper
-- columns or manual trimming.
SORTN — mode 1
=SORTN(Scores!A2:B100,
10, 1, 2, FALSE)
Mode 1 = include ties at boundary. Fair leaderboard.
Top 5 distinct product categories
SORT + UNIQUE
=ARRAYFORMULA(
QUERY(
UNIQUE(SORT(A2:B100, 2, FALSE)),
"LIMIT 5"))
Combine three functions.
SORTN — mode 2
=SORTN(A2:B100,
5, 2, 2, FALSE)
Mode 2 collapses duplicate sort values.
Complete leaderboard for browsing
SORT — right choice
=SORT(Scores!A2:B100,
2, FALSE)
Show all rows sorted. Standard leaderboard.
SORTN — overkill
=SORTN(Scores!A2:B100,
99, 0, 2, FALSE)
Works but why? Just use SORT.
The verdict
🎯 Bottom line
Default to SORT for full-range sorting. Simpler syntax, universal (works in Excel too), and does the job for any case where you need the complete data reorganized.
Reach for SORTN whenever "top N" appears in the requirement. One function does what SORT + LIMIT + tie-handling would take three functions to accomplish. And the tie-handling modes are genuinely useful for leaderboard fairness.
Learn both. They're not competing — they're complementary. SORT for full sorts; SORTN for trimmed sorts. The moment you catch yourself wrapping SORT with QUERY to add a LIMIT, switch to SORTN.
Frequently asked questions
What's the difference between SORT and SORTN in Google Sheets?
SORT returns the ENTIRE range sorted. SORTN returns only the top N rows after sorting — perfect for leaderboards or top-10 lists. SORTN also has a tie-handling mode that SORT lacks, controlling whether ties push you over your N limit or get truncated.
Does Excel have SORTN?
No. SORTN is Google Sheets-exclusive. Excel has SORT (Excel 2019+/M365) but no SORTN equivalent. To get top N in Excel, wrap SORT with INDEX or use LARGE/SMALL for numeric top-N. SORTN is a Sheets convenience that Excel users have to replicate manually.
How do I get top 10 in Google Sheets?
Use SORTN: =SORTN(A2:C100, 10, 0, 3, FALSE) returns the top 10 rows sorted by column 3 descending. The second argument (10) is how many rows to return. The third argument controls tie handling; the fourth and fifth specify sort column and direction.
What does the 'display ties' mode do in SORTN?
SORTN's third argument controls tie behavior. Mode 0 = show top N regardless of ties (may exclude tied values). Mode 1 = show top N plus additional rows with duplicate values at the boundary. Mode 2 = show top N with ties combined into one row (deduplicated). Mode 3 = show all distinct values in the top N. Choose based on whether ties should count as one or many.
Can I use SORT with multiple sort keys?
Yes. Both SORT and SORTN accept multiple column/direction pairs. =SORT(A2:C100, 2, TRUE, 3, FALSE) sorts by column 2 ascending, then by column 3 descending as the tiebreaker. Add as many sort keys as needed. This is a common pattern for leaderboards (sort by score descending, then by name ascending).