Google Sheets lesson 16 of 17, Sheets power tools
SORTN, SEQUENCE and TRANSPOSE
Quick answer
Take the top rows, number a list, and flip a table on its side.
- Key formula
=SORTN(A2:B7,3,0,2,FALSE)(The top 3 by column 2)
Watch it done
1 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
SORTN(range, n, ties_mode, sort_column, ascending) sorts a range and returns only its first n rows: a top-N list in one function. Use ties_mode 0 to return exactly n rows.
SEQUENCE(rows, columns, start, step) builds a list of numbers. =SEQUENCE(5) gives 1 to 5, handy as a rank column next to a top-N list.
TRANSPOSE turns rows into columns and columns into rows, so a vertical list becomes a header row.
Syntax
=SORTN(A2:B7,3,0,2,FALSE)The top 3 by column 2=SEQUENCE(3)1, 2, 3 down a column=TRANSPOSE(A2:A7)A column turned into a rowWatch out for
- SORTN's ascending argument is TRUE or FALSE, not 1 or -1 like SORTBY.
- Leave room for the result to spill.
- ties_mode 0 returns exactly n rows even when there are ties at the cut-off.
The exercises
- Top three teams. In D2, return the three teams with the most points from A2:B7, highest first, using SORTN. Return exactly three rows.
- Rank numbers. In C2, use SEQUENCE to number the six teams 1 to 6 down the column.
- Teams across the top. In D1, use TRANSPOSE to lay the team names in A2:A7 across a single row.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Top three teams
- Rank numbers
- Teams across the top