SheetsdrillSORT and SORTN
SORT(range, sort_column, is_ascending) returns a range sorted by a column without changing the source, and SORTN(range, n, display_ties_mode, sort_column, is_ascending) returns only the first n rows of that sorted result. Both update automatically when the data changes, unlike the Data menu sort.
Syntax
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2]) and =SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])| Part | What it is |
|---|---|
range | The rows to sort, without the header row, for example A2:C100. |
sort_column | The column number within range (1 for the first column) or a separate range of the same height to sort by. |
is_ascending | TRUE for A to Z or smallest first, FALSE for largest first. |
n | SORTN only: how many rows to return, default 1. |
display_ties_mode | SORTN only: 0 returns exactly n rows (default), 1 includes extra rows tied with the nth, 2 and 3 handle duplicates differently. |
Examples
=SORT(A2:C100, 3, FALSE)Result: All rows ordered by column C, largest first
=SORT(A2:C100, 1, TRUE, 3, FALSE)Result: Rows sorted by name A to Z, then by amount high to low within each name
=SORTN(A2:C100, 3, 0, 3, FALSE)Result: The three rows with the highest amount in column C
A formula-based top 3 that updates itself.
Common mistakes
- Including the header row in the range sorts the header into the data; start the range at row 2.
- sort_column is the position inside the range, not the sheet column letter: for B2:D100, column D is 3.
- #REF! appears because the output would overwrite filled cells; SORT needs empty cells below to spill into.
Practice SORT and SORTN
These drills check your answer on live data, free, no account needed.
- Best seller with SORTReturn the name of the product with the most units sold. Sort the table by units, largest first, and take the first name with INDEX. Answer in E1.
- Top three deals with SORTNAdd up the three largest deal values. Use SORTN to get the top 3, then SUM them. Answer in F1.
Questions
How do I sort by two columns?
Add a second pair of arguments: =SORT(A2:C, 2, TRUE, 3, FALSE) sorts by column 2 ascending, then column 3 descending.
How do I get the bottom 5?
Use SORTN with is_ascending TRUE: =SORTN(A2:C, 5, 0, 3, TRUE) returns the five smallest by column 3.
Related
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- UNIQUEHow the UNIQUE function in Google Sheets returns a list of distinct values or rows, how to sort and count them, and the errors people run into.
- QUERYHow the Google Sheets QUERY function works: the SQL-like syntax, select and where, group by with sum and count, order by, limit, and the errors people hit.
- SUMIFHow SUMIF works in Google Sheets: add up values where another column matches text, a number, a date or a wildcard, with examples and the mistakes that give 0.