Spreadsheet Practice

Lesson 29 of 45, Dynamic arrays

SORTBY, TAKE & SEQUENCE

Quick answer

Build a top-N leaderboard that re-ranks itself.

Key formula
=SORTBY(A2:A11,C2:C11,-1) (Names ranked by revenue, largest first)
Pro9 minutes, 3 exercisesSORTBYTAKESEQUENCE

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

SORT orders a table by one of its own columns. SORTBY(array, by_array, order) is more flexible: it orders any list by another range, so you can return just the names, ranked by a revenue column that is not part of the result.

TAKE(array, rows) keeps the first rows of a list (a negative number keeps the last). Put it around SORTBY for a top 3 or bottom 5 that updates when the data changes.

SEQUENCE(n) returns the numbers 1 to n down a column: perfect for a rank column beside the names.

Syntax

=SORTBY(A2:A11,C2:C11,-1)Names ranked by revenue, largest first
=TAKE(SORTBY(A2:C11,C2:C11,-1),3)Top 3 rows of the table
=SEQUENCE(5)1, 2, 3, 4, 5 down a column
=TAKE(SORTBY(A2:A11,C2:C11,1),3)Three lowest, smallest first

Watch out for

  • The sort key must be the same height as the array: A2:A11 sorted by C2:C11, not C2:C12.
  • TAKE and SEQUENCE spill. Keep the cells beneath and beside the formula empty, or you get #SPILL!.
  • Ties keep their original order. If rank matters, add a tie-breaker as a second key.

The exercises

  1. Names by revenue. In E2, list the reps (column A) ranked by revenue, largest first. Use one formula that spills.
  2. Top three. In G2, return the top 3 reps with their region and revenue (the whole row of A2:C11), ranked by revenue.
  3. Numbered top five. In K2, number the ranks 1 to 5 with SEQUENCE. In L2, list the top five rep names, ranked by revenue.

Questions people ask about this

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.

  1. Names by revenue
  2. Top three
  3. Numbered top five