Skip to content

Top 5 by value

List the five highest rows, for example top products by sales.

The formula

Argument separator
Excel · Excel 365
=TAKE(SORT(A2:B100, 2, -1), 5)
Google Sheets
=SORTN(A2:B100, 5, 0, 2, FALSE)
For Excel 2021
=INDEX(SORT(A2:B100, 2, -1), SEQUENCE(5), {1,2})

How it works

  1. SORT(A2:B100, 2, -1)

    Sort the table by its 2nd column, largest first.

  2. TAKE(…, 5)

    Keep the first five rows. In Sheets, SORTN sorts and limits in one step.

Example

A: ProductB: SalesResult (first rows)
Lamp120Desk · 410
Desk410Chair · 300
Chair300Lamp · 120

Tips

  • To get just the 3rd largest number, use =LARGE(B2:B100, 3).