If you are a teacher, you might want to list the top 10 students who scored highest at the end of an academic year. Or maybe you are an entrepreneur who wants to list the top n number of items you sold in a particular year. If you have an Excel datasheet you can do this type of listing quickly with the use of a couple of functions. Today, we’ll demonstrate to you how to create a **top 10** list in Excel that contains **duplicates**. We will use **EXCEL 365** for today’s tutorial.

## 📁 Download Excel File

Download this workbook to understand properly and practice yourself.

## Design a Top 10 List with Duplicates in Excel Using These 5 Methods

We will use the grade sheets of 20 students to create a list of the top 10 scorers in Excel which have duplicates. we will use the mark sheet of 20 students. Since some of the students got similar numbers, they will be counted as duplicates. Hence, we will try to create some formulas to find out the top 10 highest scorers easily and we will also try to sort out the duplicates as individual scores. **ROWS**, **LARGE**, **INDEX**, **MATCH**, **COUNT**, **COUNTIF**, **IF**, **SMALL**, **AGGREGATE**, **SORT**, and Finally, **FILTER** are a few of the functions we’ll use. I hope you will be patient enough until the end.

**Method 1**

### 1. Combining INDEX-MATCH Formula with LARGE Function

We will use the **LARGE function** because we want to find the Top 10 scorers. Additionally, we will use the **ROWS function** to create our Ranking and the combination of the **INDEX**–**MATCH** formula will give us our student names who got the highest marks. Finally, in the end, we will explain how the formulas worked in our favor. Before we jumped to our Excel workbook, we should explain the syntax of the functions.

Syntax of **LARGE** Function:

**LARGE(array, k)**

**array** – the data set or array for which you are looking to find the **k**-th biggest value.

**k** – the location of the data to return in the array or cell range, starting from the largest.

Syntax of **ROWS** Function:

**ROWS(array)**

**array **– a reference to the range of cells for which you need the number of rows, an array formula, or both.

Syntax of **INDEX** Function:

**INDEX(array, row_num, [column_num])**

**array** – cells in a certain range or an array constant.

**row_num** – unless column_num is provided, it is necessary. chooses which row in the array to return a value from. **Column_num** is necessary if **row_num **is left out.

**column_num** – chooses the array column that will contain the value to be returned. **Row_num** is necessary if **column_num** is left out.

Syntax of **MATCH** Function:

**MATCH(lookup_value, lookup_array, [match_type])**

**lookup_value** – this is a must. The lookup array value you’re trying to match. A value (a number, text, or logical value) or a cell reference to a value (a number, text, or logical value) can be passed as the lookup_value argument.

**lookup_array** – the number of cells being looked for.

**match_type** – The lookup_value and values in the lookup_array are matched by Excel according to the match type argument. The match types are:

**-1** the smallest value larger than or equal to the lookup_value

**0** the first value that is exactly equal to the lookup_value

**1 ** the greatest number that is less than or equal to the lookup_value

Syntax of **COUNTIF** Function:

**COUNTIF(range, criteria)**

**range** – a collection of cells that you want to count. Range may include numbers, arrays, named ranges, or numbers themselves. Values that are blank or text are ignored.

**criteria** – text string, number, expression, or cell reference that specifies which cells to be counted.

Now that we have familiarized ourselves with the functions let’s create a top 10 list with duplicates in Excel.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, we will create a new table where our first column will be the ranks of the students. To create the ranking select the cell you want to start the ranking. We will choose cell
**E6**. - Next, write the following formula in the
**Formula Bar**.

**=ROWS($E$6:E6) **

- Now, press
**Enter**to get the first rank.

- Then, use the
**Fill Handle**to autofill the rest of the Rows.

- Afterward, we will find the highest marks on the list. So we will use the
**LARGE**function in this column. Select cell**F6**and write this formula.

**=LARGE($C$6:$C$25,E6)**

- Again, press
**Enter**, and you get the highest score which is 95 here.

- Subsequently, use
**Fill Handle**to fill up the rest of the Rows. You can see, we have our top 10 marks listed in descending order.

- Now we only need the ‘Students Name’ who got the corresponding marks and for that, we will use the
**INDEX-MATCH**Select the adjacent cell of the Marks column which is cell**G6**. Then write this formula.

**=INDEX($B$6:$B$25,MATCH(F6,$C$6:$C$25,0))**

- Again, press
**Enter**and you will find the student who got the highest number.

- Finally, use the
**Fill Handle**tool to autofill.

As you can see, some of the duplicate marks were not assigned to the individuals by the **MATCH** function. Rather it kept the first person with the number in every case. So we want to make sure duplicate values will be assigned to their corresponding students. In that case, we will incorporate the **LARGE **and **COUNTIF **functions in the last formula like this.

**=INDEX($B$6:$B$25,MATCH(1,($C$6:$C$25=LARGE($C$6:$C$25,E6))*(COUNTIF(G$5:G5,$B$6:$B$25)=0),0))**

- Now, press
**Enter**and apply**Fill Handle**to autofill the column.

Evidently, you will see the duplicate marks are reassigned to the corresponding students.

**🔨 Formula Breakdown**

`👉`

Let’s break down what we did in our formulas. In the first formula where we initialized ranking, we used **ROWS($E$6:E6)**. Here,**$E$6** acts as the starting cell from where the **ROWS** function will start ranking.

`👉`

Next, For the marks, using **LARGE **we found the highest marks.

`👉`

Lastly, In the final formula, we did a couple of things. First, the **INDEX** function will look for **$B$6:$B$25 **as the array and In the **MATCH** function we provided **1** for **lookup_value** and in the **lookup_array** section, we included** ($C$6:$C$25=LARGE($C$6:$C$25,E6))** which will return** TRUE **or** FALSE **and with** COUNTIF(G$5:G5,$B$6:$B$25)=0 **we also get** TRUE **or **FALSE**. Together with multiplication, these arrays will return** 1 **or **0**. Finally, the lookup value **1 **returns the relative position when it matches this array.

**📕 Read More: Find Duplicate Rows Based on Multiple Columns in Excel**

**Method 2**

### 2. Employing SORT & FILTER Functions

The problem with the last shown method is that it wasn’t interactive enough. What if after getting the top 10 list, you want the top 5 list? Do you have to create another table or write the same formula again? No, you can easily work with the current table with this method and it will be interactive as well. This time we will use **SORT** and **FILTER** functions to achieve our goal. Besides these, we will also use the **IF**, **COUNT**, and **SEQUENCE** functions. Let’s learn about the syntax of these functions first.

Syntax of **SORT** Function:

**SORT(array,[sort_index],[sort_order],[by_col])**

A sorted array of an array’s items is what **SORT** returns. The length of the returned array matches that of the array parameter.

The arguments are

**array** – the dataset, or collection, to sort. This argument is mandatory.

**sort_index** – number of rows or columns to sort by

**sort_order** – a number specifying the preferred sort order, with 1 being the default ascending order and -1 being the descending order.

**by_col** – a logical value that represents the preferred sort direction; **TRUE **to sort by column, **FALSE **to sort by row.

Namely, the last three arguments are optional.

Syntax of **FILTER** Function:

**FILTER(array,include,[if_empty])**

**array** – the data range to filter

**include** – a boolean array with the same height or width as the array

**If_empty** – a value that will be returned if the included array is empty of all values.

Syntax of **COUNT** Function:

**COUNT(value1, [value2], …)**

**value1** – The first thing, cell reference, or range you want to count numbers within.

**Value2**,…. – Additional items, cell references, or ranges totaling a maximum of 255 can be used to count numbers.

Syntax of **SEQUENCE** Function:

**SEQUENCE(rows,[columns],[start],[step])**

**rows** – The volume of rows to be.

**column** – how many columns to return.

**start** – the first number of the series.

**step** – The amount by which each succeeding value in the array will be increased.

Now. let’s get to Excel to create an interactive top 10 list with duplicates.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, we will create an Interactive ranking column that will automatically reduce or expand to show the quantity of top scorers as we change the number of top scorers. Therefore we entered 10 as the desired number of highest scorers in cell
**F5**. Next in the ‘Rank’ column, in cell**E8**, we put the following formula.

**=IF(COUNT($C$6:$C$25)>F5,SEQUENCE(F5),””)**

- Again, press
**Enter**and Total Ranks will be shown.

- Then in the ‘Students Name’ column, in cell
**F8**, put the following formula.

**=SORT(FILTER($B$6:$C$25,$C$6:$C$25>=LARGE($C$6:$C$25,F5)),2,-1)**

- Finally, press
**Enter**to see both ‘Students’ Name’ and ‘Marks’ for the top 10 listed in descending order.

One of the cool things about this method is that if you change the number 10 to 5 in cell **F5**, the list will automatically reduce to show the top 5 scorers.

**🔨 Formula Breakdown: **

`👉`

In the above formula, cell **F5** will act as the search box. You can change it to any number to see that number of top scorers.

`👉`

Again, in the Rank column, we used the **COUNT** function nested in the **IF **function. The **COUNT **function will count the number of cells in the range **$C$6:$C$25**.If the counted number gets larger than the number provided in cell **F5**, the **SEQUENCE **function will print that number. Otherwise, the IF function will return **FALSE **where we didn’t write anything because we don’t want anything to print after the given number.

`👉`

Again, in the second formula, the **LARGE **function will get the 10th large value which is 65 and the **FILTER **function will collect those greater than or equal to 65.

`👉`

Finally, the **SORT **function will sort the values in descending order. We specified 2 in the sort index as we want the second column to sort by and -1 in the sort order for descending order.

**📕 Read More: 5 Ways to Find Duplicates in Column and Delete Row in Excel**

**Method 3**

### 3. Collaboration of SMALL and INDEX Functions

Other than the **LARGE** function we can also use the **SMALL function** with the conjunction of **INDEX** and **IF** functions to create a top 10 list with duplicates in Excel. The syntax of the **SMALL** function is similar to the **LARGE **function but returns the **k-th** smallest value in a data range. Let’s show step by step how to formulate our functions. We are keeping the same formula for the ‘Rank’ column described in the previous method.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, in the Marks column, to get the largest value in marks, in cell
**F8**, we will type the following formula.

**=IF(E8=””,””,LARGE($C$6:$C$25,E8))**

- Second, press
**Enter**and you will get 95 as the largest value.

- Next, use the
**Fill Handle**tool to copy the formula throughout the entire column.

- Again in the associated ‘Students Name’ column, in cell
**G8**write this formula.

**=IF(E8=””,””,INDEX($B$6:$B$25,SMALL(IF($C$6:$C$25=F8,ROW($B$6:$B$25)-ROW($B$6)+1),COUNTIF($F$8:F8,F8))))**

- Afterward, hit
**Enter**to run the formula.

- Finally, use
**Autofill**for the rest of the Rows.

**🔨 Formula Breakdown**

`👉`

In the first formula, we used the **IF **function to print the highest value based on the return of the **LARGE **function. Whatever number will be found in the **E8 **cell, the **IF **function will print that term of value.

`👉`

Here in the second formula, the row number for **INDEX **function is located here using **SMALL **function.

`👉`

Then, the **IF **function in the **SMALL **function then determines if the lookup_value is present in the data or not.

`👉`

Additionally, the row numbers are generated starting with 1 using the **ROW **functions.

`👉`

**COUNTIF **function is also used to check the value.

`👉`

Last but not least, combining the output of these functions yields a row number that must be obtained from the array.

**📕 Read More: ****7 Ways to Find Similar Text in Two Columns in Excel**

**Method 4**

### 4. Applying AGGREGATE Function

We can also use the **AGGREGATE** **function** to achieve the same result. We will also use the **IF** and **COUNTIF** **function** here. Excel’s **COUNTIF **function can count the number of cells in a range that satisfy a single condition. Dates, numbers, and text can all be counted in cells with **COUNTIF**. The **COUNTIF **criteria allow for partial matching using wildcards (*,?) and logical operators (>,>,=).

Syntax of **COUNTIF** function:

**COUNTIF(range, criteria)**

The arguments are as follows:

**range **– the range of cells to which a condition is applied.

**criteria **– the condition to apply, along with any necessary logical operators.

With the option to ignore hidden rows and mistakes, the Excel **AGGREGATE **function outputs aggregate calculations like **AVERAGE**, **COUNT**, **MAX**, etc.

Syntax of **AGGREGATE** function:

**AGGREGATE(function_num, options, ref1, ref2)**

The arguments are explained below:

**function_num** – There are 19 available operations, which are identified by function number in the first argument (we have included some of the notable functions in the below table).

**options** – This** **function has many options for ignoring errors, hidden rows, and other functions. Options are set with the *options* argument. Possible values are 0-7, as shown in the table below.

**ref2** is the “second parameter” for the function being called, while **ref1** is the array of items to be processed.

The function numbers:

Function | Function Number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

MEDIAN | 12 |

LARGE | 14 |

SMALL | 15 |

Possible options are:

Option | Behavior |
---|---|

0 | Disregard the functions SUBTOTAL and AGGREGATE |

1 | Disregard SUBTOTAL and AGGREGATE functions and hidden rows. |

2 | Disregard error values, AGGREGATE and SUBTOTAL functions |

3 | Disregard hidden rows, error values, AGGREGATE and SUBTOTAL functions |

4 | Disregard nothing |

5 | Disregard hidden rows |

6 | Disregard error values |

7 | Disregard error values and hidden rows |

Now, let’s use the **AGGREGATE **function in the formula. Keep in mind We used this function only in the ‘Students Name’ column. The first 2 columns will follow the same formula described in Method 2 and Method 3.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, in the Marks column, to get the largest value in marks, in cell
**F8**, we will type the following formula.

**=IF(E8=””,””,LARGE($C$6:$C$25,E8))**

- Press
**Enter**and you will get 95 as the largest value.

- Next, use the
**Fill Handle**tool to duplicate the formula throughout the entire column.

- Again in the associated ‘Students Name’ column, in cell
**G8**write this formula.

**=IF(E8=””,””,INDEX($B$6:$B$25,AGGREGATE(15,6,(ROW($B$6:$B$25)-ROW($B$6)+1)/($C$6:$C$25=F8),COUNTIF($F$8:F8,F8))))**

- Subsequently, hit
**Enter**to see the First student who got the highest mark.

- Finally, use the
**Autofill**tool to copy the formula to the entire column.

**🔨 Formula Breakdown**

`👉`

In the first formula, we used the **IF **function to print the highest value based on the return of the **LARGE **function. Whatever number will be found in the **E8 **cell, the **IF **function will print that term of value.

`👉`

In the final formula here, we used the **AGGREGATE **function in exchange for the **SMALL **function described in Method 3. Other than that the function remains the same. In the **AGGREGATE **function, we provided 15 as the function number to do the functionality of the **SMALL **function and 6 as the option to ignore any error value that the function may find.

**📕 Read More: 9 Unique Cases to Compare Rows for Duplicates in Excel**

**method 5**

### 5. Using Pivot Table

We have come to the final method of our article today. The last method is using the Pivot Table. A **PivotTable** is a user-friendly tool for quickly summarizing vast volumes of data. A **PivotTable **may be used to study numerical data in depth and to answer unexpected queries about your data. It can also be used to summarize, analyze, explore, and show summary data. Let’s get to Excel to show how you can create a top 10 list with duplicates using **PivotTable**.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select your entire data range as we selected
**B5:C20**. - Next, go to the
**Insert**tab and click**PivotTable**.

- Concurrently, a dialogue box will appear before you. As we already selected our data range it will be automatically filled up. Next, we have to decide where our PivotTable be placed. We will select the
**Existing Worksheet**. If you want, you can make the**PivotTable**in another worksheet. Next, we will provide our location which we gave**$M$6**.Then we will press**Ok**to apply changes.

- As a result,
**PivotTable Fields**will open up. You can see that our column names have been included in the field.

- Now to make the table, We will drag ‘Students’ Name’ to the
**Rows**section and ‘Marks’ to the**Values**section.

- You can see the
**PivotTable**automatically calculated the**Grand Total**. If you don’t want that you can go to the**Design**tab and under**Grand Totals**click**Off for Rows and Columns**.

Therefore, no grand total anymore.

- Now that we made our table, to rearrange the data to show the top 10 marks we will go to the table and In
**Rows Label**click the drop-down button. Next, go to**Value Filters**and select**Top 10**.

- By this time, another dialogue box will show up. You just have to click
**Ok**.

Finally, you will see your top 10 list with duplicates will show up in Excel.

- Now, to show the result in descending order we will once again select
**More Sort Options**from the drop-down menu.

- Subsequently, another dialogue box will appear where we will select
**Descending (Z to A) by:**and choose**Sum of Marks**.

Finally, we got our Pivot Table containing the top 10 list.

## How to Find Top 5 Values and Names with Criteria in Excel

In Excel, we can use **IF**, **COUNT**, **SEQUENCE**, **SORT**, **FILTER**, and **LARGE** functions to create an interactive table to find the top 5 values of a data range. Using the same formula you can find the top 3 values if you want but you won’t have to create a new table. You can do this on the existing table for any amount of top values you want. Let’s show you the steps.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, we will create an Interactive ranking column where if we change the number of top scorers, it will automatically reduce or expand to show the number of top scorers. So in cell
**F5**, we put 5 as the number of top scores we want to find. Next in the ‘Rank’ column, in cell**E8**, we put the following formula.

**=IF(COUNT($C$6:$C$25)>F5,SEQUENCE(F5),””)**

- Additionally, press
**Enter**and Total Ranks will be shown.

- Then, in the ‘Students Name’ column, in cell
**F8**, put the following formula.

**=SORT(FILTER($B$6:$C$25,$C$6:$C$25>=LARGE($C$6:$C$25,F5)),2,-1)**

- Finally, press
**Enter**to see both ‘Students’ Name’ and ‘Marks’ for the top 5 listed in descending order.

**🔨 Formula Breakdown**

`👉`

First, in the above formula, cell **F5** will act as the search field. You can change it to any number to see that number of top scorers.

`👉`

Secondly, In the Rank column, we used the **COUNT** function nested in the **IF **function. The **COUNT **function will count the number of cells in the range **$C$6:$C$25**.If the counted number gets larger than the number provided in cell **F5**, the **SEQUENCE **function will print that number. Otherwise, the IF function will return **FALSE **where we didn’t write anything because we don’t want anything to print after the given number.

`👉`

Again, in the second formula, the **LARGE **function will get the 5th large value which is 85 and the **FILTER **function will collect those greater than or equal to 85.

`👉`

Finally, the **SORT **function will sort the values in descending order. We specified 2 in the sort index as we want the second column to sort by and -1 in the sort order for descending order.

## 📄 Important Notes

`🖊️`

The **FILTER **function can only be found in **EXCEL 365**.

`🖊️`

The **AGGREGATE **function works for Excel versions higher than in 2017.

## 📝 Takeaways from This Article

`📌`

Different important functions along with **PivotTable **have been discussed.

`📌`

The Formulas have been explained in depth.

`📌`

Readers will feel comfortable using the formulas following the article.

## Conclusion

So that’s it today. I hope I explained the methods easily enough to follow and understand. I also hope from now on you will face no issues to create a top 10 list in Excel with duplicates in the future. For more articles like this one, feel free to visit **Excelden.com**.Thank you.