Spreadsheet Practice

How to get the top 5 rows with QUERY in Google Sheets

Google Sheets formula

=QUERY(A1:F13,"select B, D, F order by F desc limit 5",1)

Sort descending and add limit: =QUERY(A1:F13,"select B, D, F order by F desc limit 5",1) returns the five biggest orders.

Result on the example
a 6-row list that spills from H1 in H1
Excel
See below

See it work

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

How it works

order by F desc sorts every order by revenue, largest first, and limit 5 keeps the first five rows of that result. The header row doesn't count toward the limit. Add offset 5 after the limit to get the next five, for a second page.

=QUERY(A1:F13,"select B, D, F order by F desc limit 5",1)

Result{6 rows × 3 cols} "Rep", "Product", "Revenue", "Kim Ortiz", …

  1. A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …

    A range: 13 rows × 6 columns.

  2. QUERY(A1:F13,"select B, D, F order by F desc limit 5",1){6 rows × 3 cols} "Rep", "Product", "Revenue", "Kim Ortiz", …

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
    • query"select B, D, F order by F desc limit 5"
    • headers1

In Excel

Excel has no QUERY function. Sort and take the top rows: =TAKE(SORT(CHOOSECOLS(A2:F13,2,4,6),3,-1),5) in Excel 2024 or Microsoft 365.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SORTN({B2:B13,D2:D13,F2:F13},5,0,3,FALSE)spills 5 × 3Different resultSORTN sorts and keeps the top rows in one function, without a header. No header row.
=QUERY(A1:F13,"select B, D, F order by F desc limit 5 offset 5",1)spills 6 × 3Different resultoffset skips rows first: this is the next five, orders 6 to 10. Orders ranked 6th to 10th.

Common mistakes

  • Without order by, limit just returns the first rows of the data, not the largest.
  • A tie at fifth place is cut off without warning: only the tied row that comes first in the data makes the list. Use SORTN with ties mode 1 if ties should all show.

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 5 rows with QUERY

In H1, use QUERY to return the Rep, Product and Revenue of the five orders with the highest revenue, largest first.

Questions

How do I make the number of rows a cell?

Join it in: "select B, D, F order by F desc limit "&I1.

How do I get the top 5 per region?

QUERY can't do that in one step. Use FILTER with COUNTIFS to rank within each region.

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