Today I will be showing how you can filter out the top 10 values based on any criteria from any set of data in Microsoft Excel.

**Table of Contents**hide

**How to Find Top n Values in Excel**

First of all, let’s see how we can filter out any top n values in Excel. To achieve that, let’s be introduced with our data set first.

We have the students’ record of a school named Sunflower Kindergarten. We have the student names in column **B**, and their marks in Physics, Chemistry and Literature in columns **C, D** and **E **respectively.

Now, let’s try to sort out the top 10 marks in Physics (Column **C**).

**1. Using LARGE and ROW Functions**

We can extract out the top 10 marks in Physics by using a combination of **LARGE **and** ROW **functions of Excel.

The formula will be:

`=LARGE(C4:C20,ROW(A1:A10))`

See, we have got the top 10 marks in Physics.

**Note:** It is an **Array Formula**. So you have to press **Ctrl + Shift + Enter** to enter the formula and drag the **Fill Handle** to get all the values (Not necessary in **Office 365**)

Now, for better understanding, let’s break down the formula.

** ROW(A1:A10) **returns an array of values from 1 to 10, column-wise.

So, the formula becomes `=LARGE(C4:C20,{1,2,3,4,5,6,7,8,9,10})`

.

**Note: **I have used rows **A1** to **A10** to get rows from 1 to 10. You can use it according to your wish. Like **B1:B10**, **C1:C10,** etc. And if you require any other number of rows than 10, use that. For example, to get the top 5 values, you can use **A1:A5**, **B1:B5,** and so on.

The **LARGE **function takes a range of cells and a parameter** k **as the arguments. And returns the kth large value from that range of cells.

Here, instead of taking a single parameter **k**, it takes an array of values ** {1,2,3,4,5,6,7,8,9,10} **as

**k**.

So it returns the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, and 10th largest values respectively from the cells **C4** to **C20**.

These are the top 10 marks in Physics that we were looking for.

**2. Using LARGE and COLUMN Functions**

In the previous section, we filtered out the top n values from a column. Now we shall try to filter out any top n values from a row.

Let’s try to find out the top 2 marks obtained by the first student, James Austin.

Select a cell adjacent to him and enter this formula:

`=LARGE($C4:$E4,COLUMN(A1:B1))`

See, we have got the top two marks of the first student James Austin.

**Note: **You have to press **Ctrl + Shift + Enter** to enter this formula unless you use **Office 365**.

Now if you wish, you can drag the **Fill Handle** to get the top 2 marks of all the students.

Now, for sake of understanding, let’s break down the formula.

** COLUMN(A1:B1) **returns an array of 1 to 2,

**, in row-wise. (To know more, go to the previous section).**

`{1,2}`

The formula then becomes ** LARGE($C4:$E4,{1,2}). **It returns the 1st and 2nd largest values from the cells

**C4, D4,**and

**E4**.

This is what we want.

Notice one thing. We have used **mixed cell reference** for the lookup values in this formula, **$C4:$E4. **

This is because we want Excel to search for the largest values in between just **C4, D4** and **E4**. But if we use **C4:E4** in place of **$C4:$E4, **it will search for the large values from all the cells in between **C4** and **E4**, like **C4, C5, C6** and so on.

That is why we locked the columns.

We could have used **absolute cell reference**, **$C$4:$E$4. **But we did not use that because when we dragged the **Fill Handle** to get the top 2 marks of the other students, we wanted the row numbers in the formula to increase by one.

That’s why we just locked the columns, not the rows.

**3. Using SORT and FILTER Functions**

We can also use the **SORT** and the **FILTER** functions of Excel to extract out any top n values from a set of data.

But remember, these two functions are available in **Office 365 **only.

Select any cell and enter this formula:

`=FILTER(C4:C20,C4:C20>=LARGE(C4:C20,10))`

See, we have got the top 10 marks in Physics.

Let’s break down the formula now.

** LARGE(C4:C20,10) **returns the 10th largest mark in Physics, from cell

**C4**to

**C20**. In this case, it is 64.

** C4:C20>=LARGE(C4:C20,10) **returns an array of

**TRUE**or

**FALSE**for each cell in the range

**C4**to

**C20**.

**TRUE**if the cell content is greater than or equal to the 10th largest value,68. And

**FALSE**otherwise.

So the formula now becomes `=FILTER(C4:C20,{TRUE,TRUE,...,FALSE,...,TRUE})`

**FILTER** function returns the content of the cell from **C4** to **C20** when it faces a **TRUE**, and returns nothing if it faces a **FALSE**.

This is how we get the top 10 marks in Physics.

Now if you carefully notice, you will find that though we have got the top 10 marks, they are not arranged serially, from high to low.

But no worries, if you want, you can achieve this by using the **SORT** function of Excel.

Just wrap the formula inside a **SORT** function:

`=SORT(FILTER(C4:C20,C4:C20>=LARGE(C4:C20,10)),1,-1)`

See, we have arranged the top 10 marks in descending order.

Remember, the syntax of the **SORT **function is:

`=SORT (array, [sort_index], [sort_order], [by_col])`

We have used a **1** inside the **SORT** function because this is the column index to sort here.

And a **-1** because this is the value to be used to sort in descending order. To sort in the ascending order, use **1** in place of **-1**.

The **[by_col]** argument is optional here. It is set by default **FALSE**. This means we are not sorting by columns here, we are sorting by rows.

**4. Getting the Names of the Top 10 Values**

Now we have got several ways to filter out the top 10 marks in Physics.

But can you extract out the names of the students who got these top 10 marks in Physics?

Very easy. Just wrap any of the above formulas within an **INDEX-MATCH** or **XLOOKUP** function.

First of all, let’s use **INDEX-MATCH** along with the **LARGE** and **ROW** functions.

The formula will be:

`=INDEX(`

`B4:B20`

`,MATCH(LARGE(`

`C4:C20`

`,ROW(`

`A1:A10`

`)),`

`C4:C20`

`,0),1)`

See, we have got the top 10 students in Physics.

**Note**: It is an **Array Formula**. Do not forget to press **Ctrl + Shift + Enter** unless you are in **Office 365**.

Now let’s break down the formula.

`MATCH(LARGE(`

`C4:C20`

`,ROW(`

`A1:A10`

`)),`

`C4:C20`

** ,0) **returns the row numbers of the students with the top 10 marks between cell

**C4**and

**C20.**

Now the formula becomes `=INDEX(`

`B4:B20`

`,{3,1,12,11,7,2,17,16,6,4},1)`

.

It returns the cell contents of the cells from the table **B4 to B20**, with row equals to each of the arrays and column equals 1.

These are the required names of the top 10 students in Physics.

If you want, you can accomplish this using the **XLOOKUP** function, like this:

`=XLOOKUP(LARGE(C4:C20,ROW(A1:A10)),C4:C20,B4:B20)`

(Do not forget to press **Ctrl + Shift + Enter**)

**Top 10 Values in Excel Based on Criteria**

Now we have learned how to filter out any top n values from a set of data.

This time we shall try to figure out the top 10 values from a set of data based on some criteria.

**1. Based on Single Criterion**

Let’s go for a single criterion first.

Can you figure out the top 10 marks in Physics where the marks in Chemistry are also greater than 70?

Pretty easy. Use this formula:

`=LARGE(IF(D4:D20>60,C4:C20,""),ROW(A1:A10))`

See, we have got the top 10 marks in Physics with marks in Chemistry also greater than 60.

[Do not forget to press**Ctrl + Shift + Enter**.]

Now let’s break down the formula.

** ROW(A1:A10) **returns an array of values from 1 to 10 column-wise,

**.**

`{1,2,3,4,5,6,7,8,9,10}`

(Details in the earlier sections)

** D4:D20>60 **returns an array of

**TRUE**or

**FALSE,**

**TRUE**if the mark in Chemistry is greater than 60,

**FALSE**otherwise.

**IF(D4:D20>60,C4:C20,””) **now becomes **IF({FALSE,TRUE,TRUE,…,TRUE},{C4,C5,…,C20},””)**

It returns the adjacent cell from column **C** (marks in Physics) for a **TRUE**, and returns nothing for a **FALSE**.

The complete formula now becomes `LARGE({75,98,..,72},{1,2,3,...,10})`

It returns the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th and 10th largest values from the array `{75,98,..,72}`

.

These are what we want.

If you have **Office 365** access, you can achieve the same goal using the **FILTER** function in place of **IF**.

`=LARGE(FILTER(`

`C4:C20`

`,`

`D4:D20`

`>60),ROW(`

`A1:A10`

`))`

And to get the names of the top 10 students in Physics, who also got more than 60 in Chemistry, wrap any of the two formulas inside an **XLOOKUP** or **INDEX-MATCH**, like this:

`=XLOOKUP(LARGE(FILTER(C4:C20,D4:D20>60),ROW(A1:A10)),C4:C20,B4:B20)`

And if you wish, you can use the **INDEX-MATCH** formula too.

`=INDEX(B4:B20,MATCH(LARGE(FILTER(C4:C20,D4:D20>60),ROW(A1:A10)),C4:C20,B4:B20,C4:C20,0),1)`

Whatever you use, do not forget to press **Ctrl + Shift + Enter**.

**2. Based on Multiple Criteria**

Now comes multiple criteria.

Let’s try to find out the top 10 marks in Physics where also marks in Chemistry are greater than 60 and marks in Literature are greater than 50.

Don’t worry. Just multiply the two criteria with keeping “–” in front in place of the single criteria within the IF function of the previous section:

`=LARGE(IF((--D4:D20>60)*(--E4:E20>50),C4:C20,""),ROW(A1:A10))`

Here ** (--D4:D20>60) **returns a

**1**if the mark in Chemistry is greater than 60, otherwise

**0**.

Similarly, ** (--E4:E20>50) **returns a

**1**if the mark in Literature is greater than 50, otherwise

**0**.

So, ** (--D4:D20>60)*(--E4:E20>50) **returns a

**if both the conditions are fulfilled, otherwise it returns a**

`1`

**.**

`0`

The rest is the same as the previous section.

**Note:** You can use more than two criteria in this way.

**Top 10 Values with Duplicates**

Let’s accomplish our last task today.

If you have a close look at the marks in Literature, you will see some marks have duplicates.

That is, 88 appears twice, 71 appears thrice, and so on.

If we use any of the formulas discussed above to filter out the top 10 values, the duplicate values will appear multiple times according to their numbers of appearances.

But we want each value to appear only once, despite having duplicates.

How to achieve that?

There are ways.

You can use the formula mentioned below to filter out the top 10 marks in Literature including the duplicate values only once.

`=LARGE(UNIQUE(E4:E20),ROW(A1:A10))`

See, we have got the top 10 marks with the duplicates only once.

Here **E4:E20 **is the range of data where I am searching for. You use your one.

**Conclusion**

Using these methods, we can filter out the top 10 values based on any criteria from any set of data in Excel. Do you know any other method? Let us know in the comment section.