How to get the top 3 rows by value in Excel
Excel formula
=TAKE(SORT(A2:B8,2,-1),3)Sort the table by the value column, largest first, and take the first rows: =TAKE(SORT(table,2,-1),3). In Google Sheets use QUERY with limit.
- Result on the example
- a 3-row list that spills from D2 in
D2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
SORT orders the whole table by sales, highest first, and TAKE keeps the first 3 rows. In Google Sheets, QUERY does it in one step: order by B desc limit 3.
=TAKE(SORT(A2:B8,2,-1),3)Result{3 rows × 2 cols} "Miami", 66400, "Boston", 61750, …
A2:B87 rows × 2 cols: "Austin", $48,200, "Boston", $61,750, …A range: 7 rows × 2 columns.
-1-1Makes the value negative.
SORT(A2:B8,2,-1){7 rows × 2 cols} "Miami", 66400, "Boston", 61750, …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
A2:B87 rows × 2 cols: "Austin", $48,200, "Boston", $61,750, … - sort_column
2 - is_ascending
-1-1
- range
TAKE(SORT(A2:B8,2,-1),3){3 rows × 2 cols} "Miami", 66400, "Boston", 61750, …TAKE: First (or last, if negative) rows or columns.- array
SORT(A2:B8,2,-1){7 rows × 2 cols} "Miami", 66400, "Boston", 61750, … - rows
3
- array
In Google Sheets
Google Sheets needs a different formula: =QUERY(A2:B8,"select A, B order by B desc limit 3",0). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:B8,B2:B8>=LARGE(B2:B8,3)) | spills 3 × 2Different result | Keeps the rows at or above the third-largest value. Rows stay in their original order, and ties can return more than 3. Original order: Boston, Miami, Seattle. |
Common mistakes
- TAKE needs Excel 2024 or Microsoft 365. In Excel 2021 use
=INDEX(SORT(A2:B8,2,-1),SEQUENCE(3),{1,2}). - Ties at the cut-off are broken by the original order; decide whether that's fair for your list.
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 rows by value
D2, return the three stores with the highest sales, with their sales, largest first.Questions
How do I get the bottom 3?
Sort ascending: =TAKE(SORT(A2:B8,2,1),3).
How do I make N a cell?
Replace 3 with the cell: =TAKE(SORT(A2:B8,2,-1),F1).
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.