Hassan Shuvo
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.
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
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
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
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
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.
