Make a Top 5 List With SORT and FILTER

A top 10 list in Excel takes one formula: LARGE finds the 10th-highest value, FILTER keeps every row at or above it, and SORT puts them in order. The same formula makes a top 5 or top 3 list when you change one number. This guide is for anyone building a leaderboard, a best-sellers table or a "top performers" report, and it covers ties, a variable N, top N per region and older versions of Excel.

How to make a top 5 list with SORT and FILTER#

Combine three functions, each doing one job. Here are eight sales reps:

In E2, type:

=SORT(FILTER(A2:C9,C2:C9>=LARGE(C2:C9,5)),3,-1)

The result spills five full rows into E2:G6:

Change 5 to 10 and you have a top 10 list formula. These functions need Excel 2021, Excel 2024 or Microsoft 365.

How the top N formula works#

Read it from the inside out:

1. LARGE(C2:C9,5) returns the 5th-largest sales figure, 55,000. That is the cut-off. Our guide to LARGE and SMALL explains how k works.
2. C2:C9>=55000 tests each row and returns TRUE for the five reps at or above the cut-off.
3. FILTER(A2:C9, …) keeps only the TRUE rows. The FILTER function guide covers its arguments.
4. SORT(…,3,-1) sorts those rows by their 3rd column (Sales), largest first.

FILTER keeps rows in their original order, so SORT is what turns the result into a ranking. The sort options are covered in our guide to SORT and SORTBY.

What happens with ties?#

The FILTER method includes every row that ties with the cut-off. If Gia's sales were also 55,000, both she and Ella would be "at least the 5th-largest", and the list would show six rows. That is often fair: you don't want to drop someone at random.

If you need exactly five rows, sort the whole table and take the first five:

=INDEX(SORT(A2:C9,3,-1),SEQUENCE(5),{1,2,3})

SORT ranks all eight rows, SEQUENCE(5) asks INDEX for rows 1 to 5, and {1,2,3} asks for all three columns. Our SEQUENCE function guide explains the numbering. In Microsoft 365 and Excel 2024 there is a shorter version:

=TAKE(SORT(A2:C9,3,-1),5)

TAKE returns the first five rows of an array. With ties, both formulas keep whichever tied row comes first after sorting.

How to make N a cell you can change#

Put the number in a cell, such as I1, and point the formula at it:

=SORT(FILTER(A2:C9,C2:C9>=LARGE(C2:C9,$I$1)),3,-1)

Type 3, 5 or 10 in I1 and the list resizes. Add a drop-down with Data > Data Validation > List and the source 3,5,10 so users can pick.

If I1 is larger than the number of values, LARGE returns #NUM!. Guard against that with LARGE(C2:C9,MIN($I$1,COUNT(C2:C9))).

How to make a top N list for one region#

Filter by the region in both places: inside LARGE, so the cut-off comes from that region only, and in FILTER's test.

=SORT(FILTER(A2:C9,(B2:B9="East")*(C2:C9>=LARGE(FILTER(C2:C9,B2:B9="East"),2))),3,-1)

FILTER(C2:C9,B2:B9="East") gives the East sales only, and LARGE finds the 2nd-highest of those, 48,200. The outer FILTER keeps rows that are East and at or above that value; multiplying the two tests means both must be TRUE. The result is Finn (66,800) and Ava (48,200).

Put the region in a cell and you can switch between them. To build that list of regions, UNIQUE gives each region once.

How to make a bottom N list#

Swap LARGE for SMALL, flip the comparison, and sort smallest first:

=SORT(FILTER(A2:C9,C2:C9<=SMALL(C2:C9,3)),3,1)

This returns the three lowest sellers, Cara, Gia and Ava, starting with the lowest. It is useful for finding slow-moving stock or accounts that need attention.

Variations: names only, ranks and a horizontal list#

You often need a smaller version of the list:

E2# refers to the whole spilled list in E2, so the rank numbers grow with it. To flip a ranked list into a header row, see TRANSPOSE in Excel.

How to keep the list up to date as data grows#

The formula only sees the rows you give it. If new reps are added below row 9, extend the ranges or convert the data to an Excel table (Insert > Table, or Ctrl+T on Windows) and use the table's column names, which grow automatically. Keep the formula itself outside the table, because spilled results can't sit inside one.

Watch out for blanks if you use longer ranges such as A2:C500. LARGE ignores empty cells, but FILTER's test treats them as 0, so they only appear when your cut-off is 0 or less. Text in the Sales column is ignored by LARGE too, so a number stored as text silently drops out of the ranking.

How to make a top N list in older Excel#

Excel 2019 and older have no FILTER or SORT, so build the list one rank at a time.

1. In E2:E6, type the ranks 1 to 5.
2. In F2, get the value for that rank: =LARGE($C$2:$C$9,E2).
3. In G2, get the name with INDEX and MATCH: =INDEX($A$2:$A$9,MATCH(F2,$C$2:$C$9,0)).
4. Fill both down to row 6.

This works everywhere, but MATCH always finds the first match, so tied values return the same name twice. Fixing that needs a tie-breaker, such as adding ROW()/100000 to each value in a helper column.

Practise it#

Try building the top 5 list in the exercise above. Then practise sorting by any column and combining SORT with FILTER in the SORT lesson, and finding the second-highest value and handling ties in the LARGE and SMALL lesson.