Cellwise Try it free

Excel guide · 3 min read

How to find the top 5 values in Excel

Quick answerUse =LARGE(B2:B11,1) for the highest value, =LARGE(B2:B11,2) for the second, and so on up to 5. In Excel 365, =LARGE(B2:B11,SEQUENCE(5)) returns all five at once. To list the top 5 rows with names, use =SORT(FILTER(A2:B11,B2:B11>=LARGE(B2:B11,5)),2,-1).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1SellerQ3 salesTeam
2Hannah Lee18450North
3Marco Rossi22900South
4Zoe Patel15300North
5Ben Carter27650South
6Ivy Nguyen19800North
7Leo Martins12750South
8Nora Fischer24100North
9Ravi Shah16900South
10Ella Moore21300North
11Dan Okafor14200South

LARGE: the nth highest value

LARGE takes a range and a rank k, and returns the kth largest number. k=1 is the maximum. Text and empty cells are ignored. SMALL works the same way for the lowest values.

For a top-5 table in any version, put 1 to 5 in a column and refer to it: =LARGE($B$2:$B$11,E2), then fill down.

=LARGE(B2:B11,1)

Result: 27650

27,650, Ben Carter's sales, the highest in the list.

The 5th highest, and all five at once

k=5 gives the cut-off value: anything at or above it is in the top 5. That number is useful on its own, for example in conditional formatting or a SUMIF.

In Excel 365, =LARGE(B2:B11,SEQUENCE(5)) spills the five values down in one formula: 27,650, 24,100, 22,900, 21,300 and 19,800 for this list. The Cellwise practice engine cannot pass an array to LARGE, so the formula below gets the cut-off alone.

=LARGE(B2:B11,5)

Result: 19800

19,800 is the 5th highest (Ivy Nguyen).

Top 5 names and values (Excel 365)

FILTER keeps every row at or above the 5th highest value, and SORT orders them largest first. You get names and numbers together, and the list updates when the sales change.

If two people tie for 5th place, this returns both, so you may see 6 rows. That is usually fair. In Excel 365 you can force exactly five with TAKE(SORT(A2:B11,2,-1),5), which keeps the first five after sorting and drops the tied extra.

=SORT(FILTER(A2:B11,B2:B11>=LARGE(B2:B11,5)),2,-1)

Result: spills 5 rows × 2 columns

Ben Carter27650
Nora Fischer24100
Marco Rossi22900
Ella Moore21300
Ivy Nguyen19800

Spills 5 rows x 2 columns: Ben Carter 27,650, Nora Fischer 24,100, Marco Rossi 22,900, Ella Moore 21,300, Ivy Nguyen 19,800.

The name that goes with a rank (any version)

Feed LARGE into MATCH to find the row, and INDEX to fetch the name. Change the 2 to 1, 3, 4 or 5 (or point it at the rank cell) to fill a top-5 table.

This finds the first row with that value, so if two sellers tie, both ranks show the same name. See the mistakes below for the fix.

=INDEX(A2:A11,MATCH(LARGE(B2:B11,2),B2:B11,0))

Result: Nora Fischer

Nora Fischer, second place with 24,100.

Top values for one team, and the top-5 total

Filter the numbers first, then rank them: this gives the 2nd best seller in the North team. In older Excel, the same idea is =LARGE(IF(C2:C11="North",B2:B11),2) entered with Ctrl+Shift+Enter.

To add up the top 5, SUMIF everything at or above the cut-off. With a tie at 5th place it includes both tied values.

=LARGE(FILTER(B2:B11,C2:C11="North"),2)

Result: 21300

21,300 (Ella Moore). Nora Fischer is first in North with 24,100. For the top-5 total, =SUMIF(B2:B11,">="&LARGE(B2:B11,5)) gives 115,750.

Just highlight the top 5

If you only need to see the leaders in place, conditional formatting does it without any formula.

  1. Select B2:B11.
  2. Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.
  3. Change 10 to 5, pick a format, and click OK.

Common mistakes

Ties make INDEX MATCH repeat a name

MATCH always finds the first matching row. In Excel 365 use the SORT and FILTER version. In older Excel, use a helper column with =B2+ROW()/1000000 to make every value unique, and rank that instead.

Using MAX for the top 5

MAX only returns the single highest value. LARGE with k from 1 to 5 gives the rest.

Numbers stored as text are skipped

LARGE ignores text, so a seller whose sales were imported as text never appears. Convert the column to numbers first.

Relative range when filling down

LARGE(B2:B11,E2) shifts to B3:B12 when copied. Lock it: LARGE($B$2:$B$11,E2).

FAQ

How do I find the top 5 values with duplicates in Excel?

LARGE counts repeats separately, so two values of 22,900 take ranks 3 and 4. To get the five highest distinct values in Excel 365, use =LARGE(UNIQUE(B2:B11),SEQUENCE(5)).

How do I find the bottom 5 values?

Use SMALL instead of LARGE: =SMALL(B2:B11,1) is the lowest. For whole rows, =SORT(FILTER(A2:B11,B2:B11<=SMALL(B2:B11,5)),2,1).

How do I get the top 5 in a PivotTable?

Click the arrow on the Row Labels field, choose Value Filters > Top 10, set it to Top 5 Items by the sales field, and click OK.

What is the difference between LARGE and RANK?

LARGE gives the value at a rank you choose. RANK.EQ goes the other way: give it a value and it tells you its rank in the list.

Functions used

Related guides

Updated