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.

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:

• 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.

• 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.

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.

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.

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.

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.

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.

Related Article

• How to Create Dynamic Drop Down List Using Excel OFFSET (3 Ways)