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.
Watch Video – Create a Dynamic Top 10 List in Excel
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))
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.
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))
- 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))
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}
Read More: How to Create Dynamic Drop Down List Using VBA in Excel
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})
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: List of Dynamic Array Formulas in Excel (7 Practical Examples)
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)
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)
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))
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.
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.
Step 4:
- Click the Filter by Color option.
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.
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.
Step 2:
- Choose our range from the dataset.
- Then, press OK.
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.
Step 5:
- A dialog box will appear. Click OK on that box.
Our top 10 list here. But data is in unsorted form.
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.
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
this is the best workaround I’ve seen ever!! thanks a lot, it helped me heaps!
Dear Abel,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy