How to Create a Dynamic Top 10 List in Excel (8 Methods)

A top list is used to rank the companies or individuals based on values. This rank is set based on performance, quality, etc., or other criteria. In this article, we will learn how to create a Dynamic Top 10 list in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Methods to Create a Dynamic Top 10 List in Excel

We will discuss several methods to create Dynamic Top 10 lists in Excel. The top 10 list gives an idea of leading names in certain fields.

We’ve taken a dataset containing the marks of students here. Now, we will create a Dynamic Top 10 list using this dataset.


1. Excel LARGE Function to Create a Top 10 List

The LARGE function finds the k-th largest number from a given range.

We set 1-10 sequence numbers on the dataset below.

First, we will find out the top 10 marks and then get the students’ names.

Step 1:

  • We will apply a formula combination of LARGE and ROW functions.
  • Go to Cell G5 and put the formula below.
=LARGE($C$5:$C$18,ROW(F5)-ROW($F$4))

Excel LARGE Function to Create a Top 10 List

Step 2:

  • Hit the Enter button and drag the Fill Handle icon.

We get the top 10 marks. We can also use the ROWS function for this purpose.

Alternative Formula:

We can use an alternative formula to get the same result.

Steps:

  • Use the following formula in Cell G5:
=LARGE($C$5:$C$18,ROWS(C$5:C5))
  • Press Enter and drag the Fill Handle all the way.

Excel LARGE Function to Create a Top 10 List

Now, if you want to show the corresponding names of the top 10 list, do as follows.

Steps:

  • Go to Cell F5 and put the following formula.
  =INDEX($B$5:$B$18,MATCH(G5,$C$5:$C$18,0))

Excel LARGE Function to Create a Top 10 List

  • Press Enter and pull the Fill Handle icon.

Here, we get the top 10 names with their marks.


2. Combine INDEX, MATCH, and LARGE Functions to Create a Top 10 List

In this section, we will use the combination of the INDEX, MATCH, and LARGE functions. We will directly get the top 10 names by using this method.

Step 1:

  • Go to Cell F5.
  • Put the following formula on that cell.
=INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))

Combine INDEX, MATCH, and LARGE Functions to Create Top 10 List in Excel

Step 2:

  • Hit the Enter button and pull the Fill handle icon.

Here, we directly get the top 10 names without the help of another column.

Formula Breakdown:

  • LARGE($C$5:$C$18,E5)

It produces the largest number from the range based on the value of Cell E5.

Result: 96

  • MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0)

It represents how many results will be shown.

Result: 10

  • INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))

This shows the top 10 results from the range.

Result: {Jose, Henderson, Gerg, Josef, Aaron, Jose, Jessica, Joe, Rogers, Allisa}


3. Use an Array Formula to Create a Dynamic Top 10 List in Excel

We will use an array formula for this method. Both the top 10 names and marks of students will show after applying this formula. This formula is a combination of INDEX, SORT, and SEQUENCE functions.

Step 1:

  • Go to Cell F5.
  • Copy and paste the formula below.
=INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})

Use an Array Formula to Create a Dynamic Top 10 List in Excel

Step 2:

  • Press the Enter button and drag the Fill Handle icon.

We get both the name and marks simply by applying the formula. No need to drag the formula.

Formula Breakdown:

  • SEQUENCE(10)

It gives a sequence of numbers of 1 to 10.

Result: {1,2,3,4,5,6,7,8,9,10}

  • SORT(B5:C18,2,-1)

It sorts the data based on the second column.

Result: {Jose 96, Henderson           95, Gerg         89, Josef         84, Aaron       80, Jose          71, Jessica            70, Joe            65, Rogers     61, Allisa        60, Joe            57, Allisa        49, John         45, Mitchel            40}

  • INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})

It gives the top ten sorted list.

Result: {Jose 96, Henderson           95, Gerg         89, Josef         84, Aaron       80, Jose          71, Jessica            70, Joe            65, Rogers     61, Allisa        60}


4. Apply Excel XLOOKUP Function to Design a Top 10 List

The XLOOKUP function searches objects from a given range or array and gives output based on matching.

We will use a combination of the XLOOKUP and LARGE functions in this section. We just want to get the top 10 name list here.

Step 1:

  • Go to Cell F5 and put the following formula.
=XLOOKUP(LARGE($C$5:$C$18,E5),$C$5:$C$18,$B$5:$B$18)

Apply Excel XLOOKUP Function to Design a Top 10 List

Step 2:

  • Hit the Enter button.
  • Then, pull the Fill Handle icon.


5. Combination of SORT and FILTER functions to Build a Top 10 List

The FILTER function allows filtering a range of values from our given criteria.

In this section, we will combine the SORT and FILTER functions with the LARGE function to get a top 10 list in Excel.

Step 1:

  • Go to Cell F5.
  • Put the following formula.
=SORT(FILTER(B5:C18,C5:C18>=LARGE(C5:C18,10)),2,-1)

Combination of SORT and FILTER functions to Build a Top 10 List in Excel

Step 2:

  • Now, just hit the Enter button.

We get both the name and mark of students by applying this method.

Read More: How to Create Dynamic List in Excel Based on Criteria (Single and Multiple Criteria)


6. Design a Dynamic Top 10 List for Duplicate Data

When we have duplicate data, we may use this method to design a top 10 list in Excel.

Step 1:

  • Go to Cell F5.
  • Put the formula below on that cell.
=INDEX($B$5:$B$18, MATCH(1, ($C$5:$C$18=LARGE($C$5:$C$18, E5)) * (COUNTIF(F$4:F4, $B$5:$B$18)=0), 0)) 

Design a Dynamic Top 10 List for Duplicate Data

Step 2:

  • Press the Enter button.

We get only the names of students here.


7. Conditional Formatting, Filter, and Sort Tools to Create a Dynamic Top 10 List

We can combine multiple tools to get a top 10 list. Those are Conditional Formatting, Filter, and Sort.

Step 1:

  • Select Cells C5 to C18.
  • Click Conditional Formatting from the ribbon.
  • Choose Top 10 Items from the Top/Bottom Rules.

Conditional Formatting, Filter, and Sort Tools to Create a Dynamic Top 10 List

Step 2:

  • A dialog box will appear. Notice 10, because of the top 10
  • Then, press OK.

Step 3:

  • Select Cells C4 to C18.
  • Press the right button of the mouse.
  • Choose Filter by Selected Cell’s Color from the list of Filter.

Conditional Formatting, Filter, and Sort Tools to Create a Dynamic Top 10 List

Step 4:

  • Click the Filter by Color option.

Conditional Formatting, Filter, and Sort Tools to Create a Dynamic Top 10 List

Here are the top 10 data. Now we will sort them.

Step 5:

  • Again, press the Right button of the mouse.
  • Choose Sort Largest to Smallest from the Sort list.

Conditional Formatting, Filter, and Sort Tools to Create a Dynamic Top 10 List

Step 6:

  • Choose Expand the selection.
  • Then press Sort.

Now, our top 10 data with well sorted.

Read More: How to Make a Dynamic Data Validation List Using VBA in Excel


8. Form a Top 10 List in Excel Using Pivot Table

We will form a top 10 list in Excel using the Pivot table.

Step 1:

  • Go to the Insert tab.
  • The choose From Table/Range from the PivotTable list.

Form a Top 10 List in Excel Using Pivot Table

Step 2:

  • Choose our range from the dataset.
  • Then, press OK.

Form a Top 10 List in Excel Using Pivot Table

Step 3:

  • Tick on Name and Mark of the PivotTable Fields.

Step 4:

  • Now, go to the Row Labels
  • Choose Top 10 from the Value Filters.

Form a Top 10 List in Excel Using Pivot Table

Step 5:

  • A dialog box will appear. Click OK on that box.

Our top 10 list here. But data is in unsorted form.

Form a Top 10 List in Excel Using Pivot Table

Read More: Excel Create Dynamic List from Table (3 Easy Ways)


Conclusion

In this article, we discussed how to create a dynamic top 10 list in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo