Spreadsheet Practice

How to get the top 3 values with SORTN in Google Sheets

Google Sheets formula

=SORTN(A2:B11,E1,0,2,FALSE)

Give SORTN the range, how many rows, a ties mode, the column and FALSE for descending: =SORTN(A2:B11,E1,0,2,FALSE).

Result on the example
a 3-row list that spills from D3 in D3
Excel
See below

See it work

The formula is already in D3. Change any input and the result updates, or edit the formula to experiment.

How it works

SORTN sorts the range by column 2 (sales), descending because of FALSE, and returns the first E1 rows. The third argument, the ties mode, decides what happens at the cut-off: 0 returns exactly 3 rows, so Mo Diaz, tied with Kim Ortiz on 57,300, is left out.

=SORTN(A2:B11,E1,0,2,FALSE)

Result{3 rows × 2 cols} "Eve Lund", 66400, "Raj Patel", 61750, …

  1. A2:B1110 rows × 2 cols: "Ana Ruiz", $48,200, "Raj Patel", $61,750, …

    A range: 10 rows × 2 columns.

  2. E13

    The value in E1.

  3. SORTN(A2:B11,E1,0,2,FALSE){3 rows × 2 cols} "Eve Lund", 66400, "Raj Patel", 61750, …

    SORTN: The first n rows after sorting. Ties mode 2 keeps one row per distinct value.

    • rangeA2:B1110 rows × 2 cols: "Ana Ruiz", $48,200, "Raj Patel", $61,750, …
    • nE13
    • display_ties_mode0
    • sort_column1, is_ascending12
    • sort_column1, is_ascending2FALSE

In Excel

Excel has no SORTN. In Excel 2024 or Microsoft 365, use =TAKE(SORT(A2:B11,2,-1),E1).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SORTN(A2:B11,E1,1,2,FALSE)spills 4 × 2Different resultTies mode 1 also returns every row tied with the last one. Four rows: Mo Diaz is included with Kim Ortiz.
=TAKE(SORT(A2:B11,2,-1),E1)Excel onlyspills 3 × 2The Excel version.

Common mistakes

  • The order argument is TRUE for ascending and FALSE for descending; leaving it out sorts smallest first, which gives the bottom 3.
  • Ties modes 2 and 3 remove duplicate rows; they are for de-duplicating, not for ranking.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Get the top 3 values with SORTN

In D3, return the E1 reps with the highest sales, with their sales, largest first.

Questions

How do I get the bottom 3?

Sort ascending: =SORTN(A2:B11,3,0,2,TRUE).

How do I return just the names?

Give SORTN the names and a separate sort range: =SORTN(A2:A11,3,0,B2:B11,FALSE).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.