For different purposes, you may need to find the top n number of values in Excel, n can be 5 or 10, and many more. Now, today we are going to show you how to find the top 10 values from a list with duplicates in Excel. Here, for conducting the session, we are using the Microsoft 365 version. Furthermore, you can choose your preferred version.

**Table of Contents**hide

## How to Make Top 10 List with Duplicates in Excel: 5 Handy Methods

Before diving into the session, let’s learn about today’s workbook which is the base of our examples. Here, we have a basic table, consisting of the **sites** and their respective number of **visits per month**. Now, using this relationship, we will see how to find the top **10** values from a **list with duplicates **in Excel.

Moreover, there are a couple of duplicate values in the second column, they are stored on purpose. However, this is a basic table to show you simple examples, you may encounter a much larger data set in a real-life scenario.

### 1. Incorporating LARGE Function with INDEX–MATCH Formula

Whenever you see the word ‘**top**’, you might understand that one function we are going to use is the** LARGE **function.

Since our agenda is to find the **top 10** values, let’s set the rank numbers (**1 to 10**). The steps are given below.

**Steps:**

- Firstly, you have to select a new cell
**F5**where you want to keep the result. - Secondly, you should use the formula given below in the
**F5**cell.

`=ROWS($F$5:F5)`

Here, the **ROWS **function will give the relative row number from a given array.

- Thirdly, press
**ENTER**.

- Now, you can drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**F6:F14**.

As a result, you will see the rank.

Actually, our scenario is such that we will fetch the **top 10 **visited sites.

Now, to do the task we need to use the** LARGE** function. Basically, the** LARGE** function returns numeric values based on their position in a list when sorted by value.

**LARGE(array, k)**

**array: **The array from which you want to select the** kth** largest value.

**k:** An integer that specifies the position from the largest value.

Here, we will provide the position using the** k**. When we need the** top 10 **value, then the value of **k** will be **1 to 10**.

- Now, you have to select a new cell
**G5**where you want to keep the result. - Then, let’s write the formula given below in the
**G5**cell on the Excel sheet.

`=LARGE($C$5:$C$20,F5)`

- Subsequently, press
**ENTER**.

Here we have inserted the** visits **amount column as the array and the **Rank **as the k. Here we have found the **most visited site.**

- Similarly, write the formula for the rest.

So, we have found the** top 10 **most visited sites from our list.

Now, do you think, if we conclude our task here will that match an ideal standard?

No, in most cases it will not. We need to find the** items/names **which is at the top.

So, to find the names we need to use the combination of the **INDEX**–**MATCH** functions.

**INDEX(find_array,MATCH(lookup_value,lookup_array, 0))**

Here, the **MATCH** function is used to locate the position of a lookup value in a row, column, or table.

Additionally, the** INDEX** function returns the value at a given location in a range or array.

- Now, write the following formula in the
**H5**cell.

`=INDEX($B$5:$B$20,MATCH(G5,$C$5:$C$20,0))`

**Formula Breakdown**

- Firstly, inside the
**INDEX**function, we have inserted the**Site Name**column as our**find_array**. - Secondly, the
**MATCH**function for declaring the row number. - Here, within the
**MATCH**function, we have inserted the**lookup_value**and**lookup_array**. Our**lookup_value**is the**visit**value we have derived.**0**for stating the**Exact**Match.

After pressing **ENTER** the formula gave the result.

- Now, write the formula for the rest of the rows.

__Oh! Not the items we were looking for.__

As our table has a number of **duplicate visits**. Here, a couple of sites have the same number of **visits**. That’s fine. Actually, our formula should derive both names. But this formula is unable to do that.

So, what can we do? Do we need to modify the formula?

Obviously yes. Basically, we need to modify the formula.

- Here, our modified formula will be as follows.

**INDEX(find_array,MATCH(1, compare_value*check_already_matched,0))**

- So, rewrite the formula in the
**H5**cell again.

`=INDEX($B$5:$B$20,MATCH(1,($C$5:$C$20=LARGE($C$5:$C$20,F5))*(COUNTIF(H$4:H4,$B$5:$B$20)=0),0))`

- Then, press
**ENTER**.

**Formula Breakdown**

- Here, in the
**MATCH**function, we have set**1**as the**lookup_value**. - Then we compared the value using
**$B$4:$B$19=LARGE( $B$4:$B$19, E4)**. This will return an array of**TRUE**or**FALSE**. - Now, the
**COUNTIF**function with an expanding range of reference checks if a given item is already in the top list. It also returns an array of**TRUE**or**FALSE**. - After that, when we multiply these
**two**arrays another array of**1s**and**0s**is returned. - Here, the
**lookup_value****1**matches with this array and returns the relative position.

- Now, write the formula for the rest of the rows.

Which worked perfectly and provided what we wanted.

**Read More: How to Get Top 10 Values Based on Criteria in Excel**

### 2. Combination of SMALL and INDEX Functions to Find Top 10 List with Duplicates

As well as we can use the combination of the **SMALL** and **INDEX** functions as well. But before that, let’s set the rank dynamically. We have a placeholder Top for how many values we want to see.

Now, we will set such a way that the row number will fetch until it becomes higher than the number of values we want to see.

- Here, we have used the
**IF**function in the**F5**cell to do that.

`=IF(ROWS(E$4:$I4)>$G$17,"",ROWS(E$4:$I4))`

- Then, press
**ENTER**.

Next, find out the top** 10 **values.

- Similarly, write the following formula in the
**G5**cell. Actually, the formula is similar to the previous one. In addition, we have added an**IF**statement there to eradicate the error value for**empty**cells.

`=IF(F5="","",LARGE($C$5:$C$20,F5))`

- Then, press
**ENTER**.

Now our formula to find the items will be.

**INDEX(find_array,SMALL(IF(looup_array=lookup_value, ROW(find_array)-ROW(first_row)+1, COUNTIF(lookup_value_array,lookup_value)))**

Here, use the** IF **statement as previously to eradicate any error for the empty cells.

- So, write down the following formula in the
**H5**cell.

`=IF(F5="","",INDEX($B$5:$B$20,SMALL(IF($C$5:$C$20=G5,ROW($B$5:$B$20)-ROW($B$5)+1),COUNTIF($G$5:G5,G5))))`

- After that, hit
**ENTER**to execute the formula.

**Formula Breakdown**

- Here, using the
**SMALL**function, we find the**row_number**for the**INDEX**function. - Then, inside the
**SMALL**function, we have an**IF**function that checks whether the**lookup_value**is within the array or not. - Furthermore, the
**ROW**functions are used to generate the row numbers starting from**1**. - In addition, the
**COUNTIF**function is for checking whether the value has already been traversed or not. - Lastly, the combination of the result from these functions produces a
**row_number**that is needed to be fetched from the array.

Finally, we have found the **Site name** which topped the list.

- Similarly, write the formula or exercise
**Excel AutoFill**for the rest of the values.

### 3. Using AGGREGATE Function to Find Top 10 List with Duplicates in Excel

We can use the** AGGREGATE** function as well for our task. Furthermore, if you are using an Excel version higher than** 2007**, then you can use this function.

Basically, the **AGGREGATE **function returns an aggregate calculation like **AVERAGE**, **COUNT**, **MAX**, **SUM**, etc.

In addition, the syntax for the **AGGREGATE** function is as follows.

**AGGREGATE(function_number,behavior_option, range)**

**function_number:** This number specifies which calculation should be made.

**behavior_option:** Set this using number. This number denotes how the function will behave.

**range:** Range you want to aggregate.

Basically, there are a total of **19** operations, specified by the predefined **function_number** available in the **AGGREGATE** function. We are listing a few frequently used function numbers.

Function | Function_number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

LARGE | 14 |

SMALL | 15 |

Furthermore, there are **8 **possible values for **behavior_options**. They are.

Value | Behavior |
---|---|

0 | Ignore SUBTOTAL and AGGREGATE functions |

1 | Ignore hidden rows, SUBTOTAL and AGGREGATE functions |

2 | Ignore error values, SUBTOTAL and AGGREGATE functions |

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

4 | Ignore nothing |

5 | Ignore hidden rows |

6 | Ignore error values |

7 | Ignore hidden rows and error values |

Here, we need the **SMALL** functionality so that we will use **15** as **function_number**.

So, our formula will be.

**INDEX(find_array,AGGREGATE(15,6,(ROW(find_array)-ROW(first_row)+1)/(lookup_array=lookup_value), COUNTIF(lookup_value_array,lookup_value)))**

- Now, at first write the formula for
**Rank**and**Visit**column like**the previous method**. - Then, write the following formula in the
**H5**cell.

`=@IF(F5="","",INDEX($B$5:$B$20,AGGREGATE(15,6,(ROW($B$5:$B$20)-ROW($B$5)+1)/($C$5:$C$20=G5),COUNTIF($G$5:G5,G5))))`

- After that, to execute this formula you need to hit
**ENTER**only.

So, if you have understood the earlier formulas, then the only question that will be on your mind is why did we use** 6 **as **behavior_option**?

Can you figure out why?

Let’s help you a bit. Here, you may write the** ROW **function division portion in Excel. As a result, a bunch of division **errors** within the array. Which number can we use for ignoring errors?

Yes, to ignore the error, we have used **6 **as our **behavior_option** value.

- Lastly, write the formula for the rest of the rows and you will get the
**top 10**from the list with**duplicates.**

### 4. Use of FILTER & SORT Functions to Find Top 10 List with Duplicates

Here, you can apply a **combination** **of some functions** to find the top 10 from a list with duplicates in Excel. Here, we will use **FILTER**, **SEQUENCE**, **SORT**, **IF**, **COUNT**, and **LARGE** functions. In the case of the** FILTER** function, you will get this function only in the **Excel 365** version. The steps are given below.

**Steps:**

- Firstly, you have to make a table. So, select the data range.
- Secondly, from the
**Insert**tab >> choose the**Table**feature.

At this time, a dialog box named** Create Table** will appear.

- Now, make sure that you have selected the data range in the
**Where is the data for your table?**box.*Here, if you select the data range before then this box will auto-fill up.* - Then, check the
**My table has headers**option. - Finally, press
**OK**.

Finally, your table is ready.

- Now, select any cell of the table.
- Then, from the
**Table Design**tab >> go to the**Properties**option. - After that, write down a table name in the
**Table Name**box. Here, we have written**Internet**as our table name.

- Now, select a different cell where there should be enough space to keep your whole data.
- Then write down the following formula in that cell. Here, we’re going to use the
**F8**cell.

`=IF(COUNT((Internet[Visit/Month (Billion)]))>H5,SEQUENCE(H5),"Cross Limit")`

- Subsequently, press
**ENTER**to get the result.

**Formula Breakdown**

- Here, the
**COUNT**function will count the total cells of**Internet[Visit/Month (Billion)])**column**.** - Then, the
**IF**function will check whether the number of counted cells is greater than the**H5**cell value or not. - If the
**H5**cell value is less than the total counted cells then it will operate the**SEQUENCE**function otherwise it will return “**Cross Limit**“.

- Again, select a different cell where there should be enough space to keep your whole data.
- Then, write down the following formula in that cell. Here, we’re going to use the
**G8**cell.

`=SORT(FILTER(Internet,Internet[Visit/Month (Billion)]>=LARGE(Internet[Visit/Month (Billion)],H5)),2,-1)`

- After that, press
**ENTER**.

**Formula Breakdown**

- Here,
**the H5**cell will be the search box. - Firstly, the
**LARGE**function will return the**10th**large value from the column**Internet[Visit/Month (Billion)]**.**Output: 2.4.**

- Secondly, the
**FILTER**function will filter the data. Here, the array is a table named**Internat**. It will search for the values which are greater or equal to**2.4**. It will return all the details of**Visit**value like**Site Name**–**Visit**–**Category**.**Output:****{“Facebook”,25.5,”Social Networking site”;”Youtube”,34.6,”TV Movies and Streaming”;”Twitter”,6.6,”Social Networking site”;”Wikipedia”,6.1,”Dictionaries and Encyclopedias”;”Google”,92.5,”Search Engines”;”Instagram”,6.1,”Social Networking site”;”Yahoo”,3.8,”News and Media”;”Baidu”,5.6,”Search Engines”;”Netflix”,2.4,”TV Movies and Streaming”;”Amazon”,2.4,”Marketplace”}.**

- Thirdly,
**2**is the sort index and**-1**is the sort order for the**SORT**function. So, the**SORT**function will do sorting for**Visit/Month (Billion)**column in descending order.

As a result, you will get the **top 10** list.

Here, if you want to know the top **5** list with duplicates then just write **5** in the **H5** cell the result will be auto-modified.

**Read More: How to Find Top 5 Values and Names in Excel**

### 5. Inserting Pivot Table to Find Top 10 List with Duplicates

Apart from using the formula, we can use the **Pivot Table **to find the **top 10** values from a list.

- First of all, select the entire table and then explore the
**Insert**tab, you will find the**Tables**section. - After that, within the
**Tables**section, there will be an option called**Pivot Table,**click that. - Then, choose the
**From Table/Range**option.

Subsequently, a dialog box will open in front of you.

- At this time, check the range and select the place.
- Then, click
**OK**(you can simply hit**ENTER**as well).

As a result, a new sheet will open like the following image.

- Then, drag the
**Site Name**to the**Rows**.

Subsequently, you will see the **Row Labels**.

- Similarly, drag the
**Visit**column to the**Values**.

Usually, the **Pivot Table** sums up the numeric values. That’s not a contender for our concerns.

- At first, click the
**Filter**icon beside the**Row Labels**. - Then, inside the
**Value Filters**, you will find the**Top 10**option. Click that.

At this time, a dialog box will pop up.

- Now, click
**OK.**

Finally, you will find the **top 10** values from your list.

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You are welcome to download the workbook from the link below.

## Conclusion

That’s all for the session. Here, we have tried listing several ways to find the **top 10** from a list with duplicates in Excel. So, we hope you will find this helpful. Also, feel free to comment if anything seems difficult to understand. Furthermore, let us know any other methods which we might have missed here.

Hello,

all these functions not working with WPC Office, tried everything, i miss something? Therefore these functions

work only in Microsoft 365?

Thank you!

Dear Andrei,

These functions are available in WPS office.

Regards

ExcelDemy