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

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

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

**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.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Product | Units | Revenue |
| 2026-07-03 | Ana Ruiz | East | Widget | 40 | 2080 |
| 2026-07-06 | Raj Patel | West | Gadget | 25 | 2225 |
| 2026-07-10 | Lee Chen | East | Gizmo | 12 | 1740 |
| 2026-07-14 | Ana Ruiz | North | Gadget | 30 | 2670 |
| 2026-07-17 | Kim Ortiz | West | Widget | 55 | 2860 |
| 2026-07-21 | Raj Patel | East | Widget Pro | 18 | 1422 |
| 2026-07-24 | Lee Chen | South | Gadget | 22 | 1958 |
| 2026-07-29 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| 2026-08-04 | Mo Diaz | North | Gizmo | 14 | 2030 |
| 2026-08-07 | Raj Patel | South | Widget | 20 | 1040 |
| 2026-08-12 | Kim Ortiz | North | Gadget | 16 | 1424 |
| 2026-08-18 | Mo Diaz | East | Gizmo Mini | 28 | 1092 |

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

Result (spills 6 × 3): Rep, Product, Revenue; Kim Ortiz, Widget, $2,860; Ana Ruiz, Gadget, $2,670; Raj Patel, Gadget, $2,225; Ana Ruiz, Widget, $2,080; Mo Diaz, Gizmo, $2,030

## 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.

## Other ways

- `=SORTN({B2:B13,D2:D13,F2:F13},5,0,3,FALSE)`: SORTN 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)`: offset 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/top-rows-with-query-limit