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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Dynamic Top 10 List in Excel



How to Create a Dynamic Top 10 List in Excel: 8 Quick Methods

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}

Read More: How to Create Dynamic Drop-Down List Using Excel OFFSET 


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 


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 Create Dynamic List From Table 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: How to Make Dynamic Drop Down List from Another Sheet in Excel


Download Practice Workbook

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


Conclusion

In this article, we discussed how to create a dynamic top 10 list in Excel. I hope this will satisfy your needs.


<< Go Back to Dynamic List Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

4 Comments
  1. Your article was very useful. But in the case of 2 or 3 persons had the same mark. How could you sort in the list? Thank for your time.

    • Thank you for your query, DUONG. The third, fifth, and seventh approaches mentioned above can be used safely when numerous people share the same mark. However, their rankings on the Top 10 list are based on where they actually stand on the unsorted list. For instance, Jessica, Henderson, and Aaron will be in positions 5, 6, and 7, respectively, in the Top 10 list, if they all receive 70 marks. I believe Hope you got your answer. Please ask on our ExcelDemy forum if you have any additional questions.
      Regards
      Aniruddah
      Dynamic List With Multiple Persons Having Same Mark

  2. this is the best workaround I’ve seen ever!! thanks a lot, it helped me heaps!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo