{"id":4670,"date":"2023-02-21T16:17:41","date_gmt":"2023-02-21T16:17:41","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=4670"},"modified":"2024-01-17T22:27:37","modified_gmt":"2024-01-17T22:27:37","slug":"how-to-find-top-10-values-in-excel-with-formulas","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-find-top-10-values-in-excel-with-formulas\/","title":{"rendered":"How to Find Top 10 Values in Excel with Formulas?"},"content":{"rendered":"<p>Finding the top 10 values in Excel is a popular way to present data, particularly on dashboards and compiled reports. A top list is used to rate businesses or people according to their values. In this tutorial, we will learn about some formulas for finding the top 10 values in an Excel worksheet.<\/p>\n<p><strong>This Content Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#how-to-find-top-10-values-in-excel-using-dynamic-array-formulas?\"><strong>How to Find Top 10 Values in Excel using Dynamic array Formulas?<\/strong><\/a>\n<ul>\n<li><strong>By combining the INDEX, SORT, and SEQUENCE functions (Excel 2021 &amp; Excel 365)<\/strong><\/li>\n<li><strong>By Combining the CHOOSECOLS, CHOOSEROWS, SORT, and SEQUENCE functions (Excel 365 only)<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-find-top-10-values-in-excel-using-dynamic-arrays-with-criteria?\"><strong>How to Find Top 10 Values in Excel using Dynamic Arrays with Criteria?<\/strong><\/a>\n<ul>\n<li><strong>Using FILTER Function with INDEX, SORT and SEQUENCE Function<\/strong><\/li>\n<li><strong>Using FILTER Function with CHOOSEROWS, CHOOSECOLS<\/strong><\/li>\n<li><strong>Calculating Top Values Based on Multiple Criteria<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-find-the-top-10-values-in-excel-using-traditional-functions?\"><strong>How to Find the Top 10 Values in Excel using Traditional Functions?<\/strong><\/a>\n<ul>\n<li><strong>Using the LARGE Function<\/strong><\/li>\n<li><strong>Identify the Labels When the Top 10 Values are Not Unique<\/strong><\/li>\n<li><strong>Top 10 Values using Single Criteria<\/strong><\/li>\n<li><strong>Top 10 Values using Multiple Criteria<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-find-the-bottom-10-values-in-excel-using-formulas?\"><strong>How to Find the Bottom 10 Values in Excel using Formulas?<\/strong><\/a>\n<ul>\n<li><strong>Bottom 10 Values using Dynamic Array Formulas<\/strong><\/li>\n<li><strong>Bottom 10 Values using Traditional Formulas<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2><strong id=\"how-to-find-top-10-values-in-excel-using-dynamic-array-formulas?\">1. How to Find Top 10 Values in Excel using Dynamic array Formulas?<\/strong><\/h2>\n<p>Suppose we have a table like this which contain the data of students, their scores, and result. But the data here are not sorted and we want to find the top 10 scores and their scorer among these students.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter size-full wp-image-4671\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-1.png\" alt=\"Find top 10 values in excel with formulas\" width=\"522\" height=\"374\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-1.png 522w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-1-300x215.png 300w\" sizes=\"(max-width: 522px) 100vw, 522px\" \/><\/p>\n<p><strong>1.1 By combining the INDEX, SORT, and SEQUENCE functions (Excel 2021 &amp; Excel 365)<\/strong><\/p>\n<p><strong>Step 1: <\/strong>Select cell G2 and insert the following formula in this cell.<\/p>\n<p><strong>=INDEX(SORT(A2:C21,2,-1),SEQUENCE(10),{1,2})<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-4672\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-2.png\" alt=\"Find top 10 values in excel with formulas\" width=\"615\" height=\"383\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-2.png 615w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-2-300x187.png 300w\" sizes=\"(max-width: 615px) 100vw, 615px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press <strong>ENTER<\/strong> key. When you press the ENTER key the formula will return you the top 10 scores and their scorer data. There is no need to auto-fill or insert the formula in multiple cells, you just have to insert it in one cell, and done, you got your result.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-4673\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-3.png\" alt=\"Find top 10 values in excel with formulas\" width=\"587\" height=\"438\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-3.png 587w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-3-300x224.png 300w\" sizes=\"(max-width: 587px) 100vw, 587px\" \/><\/p>\n<p><strong>Formula Breakdown:<\/strong><\/p>\n<p><strong>SEQUENCE(10)- <\/strong>The number of rows, columns or data to return. The <strong>SEQUENCE<\/strong> function in our example generates a list of numbers from 1 to 10.<\/p>\n<p><strong>SORT(A2:C21,2,-1)- <\/strong>The <strong>SORT<\/strong> function in our example looks through the range or criteria A2:C21 to sort. 2 is the sort index here which means the function will sort the data based on column B\/column 2. Use 1 to sort in ascending order and -1 for descending order.<\/p>\n<p><strong>INDEX(SORT(A2:C21,2,-1),SEQUENCE(10),{1,2})- <\/strong>Uses <strong>SORT <\/strong>and <strong>SEQUENCE<\/strong> functions and (1,2) returns the data of column 1 and 2.<\/p>\n<p><strong>1.2 By Combining the CHOOSECOLS, CHOOSEROWS, SORT, and SEQUENCE functions (Excel 365 only)<\/strong><\/p>\n<p><strong>Step 1: <\/strong>Insert the following formula into cell G2 after choosing this cell.<\/p>\n<p><strong>=CHOOSEROWS(CHOOSECOLS(SORT(A2:C21,2,-1),1,2),SEQUENCE(10))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4674\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-4.png\" alt=\"Find top 10 values in excel with formulas\" width=\"639\" height=\"342\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-4.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-4-300x161.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press ENTER key.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4675\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-5.png\" alt=\"Find top 10 values in excel with formulas\" width=\"641\" height=\"467\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-5.png 641w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-5-300x219.png 300w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/p>\n<p><strong>Formula Breakdown:<\/strong><\/p>\n<p><strong>CHOOSECOLS(SORT(A2:C21,2,-1),1,2)- <\/strong>Column 1 and 2 are returned after the values from cells A2:C21 are sorted by column 2 in decreasing order.<\/p>\n<p><strong>CHOOSEROWS(CHOOSECOLS(SORT(A2:C21,2,-1),1,2),SEQUENCE(10))- <\/strong>This only returns the top 10 rows of the previous CHOOSECOLS result.<\/p>\n<p><strong id=\"how-to-find-top-10-values-in-excel-using-dynamic-arrays-with-criteria?\" style=\"font-size: 1.7em;\">2. How to Find Top 10 Values in Excel using Dynamic Arrays with Criteria?<\/strong><\/p>\n<p><strong>2.1 Using FILTER Function with INDEX, SORT, and SEQUENCE Function<\/strong><\/p>\n<p>Now we have a situation where the students are from different classes and we want to get the top 10 values only for class eight students.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4676\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-6.png\" alt=\"Find top 10 values in excel with formulas\" width=\"638\" height=\"342\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-6.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-6-300x161.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Insert the below given formula inside H2.<\/p>\n<p><strong>=INDEX(SORT(FILTER(A2:D21,B2:B21=K2),3,-1),SEQUENCE(10),{1,3})<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4677\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-7.png\" alt=\"Find top 10 values in excel with formulas\" width=\"641\" height=\"317\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-7.png 641w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-7-300x148.png 300w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Hit the ENTER key and this formula will return you the top 10 values based on a criteria.\u00a0 you choose. The <strong>FILTER <\/strong>function here returns cells A2 to D21, but only where the corresponding values from B2 to B21 equal the selected class in cell K2.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4678\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-8.png\" alt=\"Find top 10 values in excel with formulas\" width=\"639\" height=\"359\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-8.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-8-300x169.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>2.2 Using FILTER Function with CHOOSEROWS, CHOOSECOLS<\/strong><\/p>\n<p><strong>Step 1: <\/strong>Place the following formula into H2 and hit ENTER key. This works in Excel 365 only.<\/p>\n<p><strong>=CHOOSEROWS(CHOOSECOLS(SORT(FILTER(A2:D21,B2:B21=K2),3,-1),1,3), SEQUENCE(10))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4679\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-9.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"362\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-9.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-9-300x170.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>2.3 Calculating Top Values Based on Multiple Criteria<\/strong><\/p>\n<p>Here we have two conditions, the students have to be from class eight and their score must be more than 60. Now we will see how we can look for the top 5 scorers from this list of students who scored more than 60 and are in class eight.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4680\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-10.png\" alt=\"Find top 10 values in excel with formulas\" width=\"639\" height=\"321\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-10.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-10-300x151.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Input the given formula in H1 and press ENTER.<\/p>\n<p><strong>=INDEX(SORT(FILTER(A2:D21,(B2:B21=K2)*(C2:C21&gt;L2)),3,-1), SEQUENCE(5),{1,3})<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4681\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-11.png\" alt=\"Find top 10 values in excel with formulas\" width=\"639\" height=\"198\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-11.png 639w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-11-300x93.png 300w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/p>\n<p>The <strong>FILTER<\/strong> function is essential when working with multiple criteria. While using <strong>FILTER<\/strong> function on multiple criteria, the plus sign (+) generates OR logic, whereas the asterisk (*) creates AND logic.<\/p>\n<p><strong id=\"how-to-find-the-top-10-values-in-excel-using-traditional-functions?\" style=\"font-size: 1.7em;\">3.How to Find Top 10 Values in Excel using Traditional Functions?<\/strong><\/p>\n<p><strong>3.1 Using the LARGE Function<\/strong><\/p>\n<p><strong>Step 1: <\/strong>Select cell H2 and insert the below given formula in this cell. Press ENTER.<\/p>\n<p><strong>=LARGE($B$2:$B$21,ROW(G2)-ROW(G$1))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4682\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-12.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"260\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-12.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-12-300x122.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Drag the fill handle downwards to apply the formula in the other cells of this column. This will return the top 10 values\/scores from this data table.<\/p>\n<p>The <strong>LARGE<\/strong> function [=LARGE(range,k)] has two arguments, range and k. When we used the LARGE function in cell H2, it looked through the range <strong>$B$2:$B$21<\/strong> to sort the values. <strong>ROW($G$2)-ROW($G$1) <\/strong>here is the \u201ck\u201d( the <strong>N<sup>th<\/sup><\/strong> item to be found ). So, the first row of data calculates as 1, the second row calculates as 2, and so on.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4683\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-13.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"260\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-13.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-13-300x122.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Now we will use the <strong>INDEX<\/strong> and <strong>MATCH<\/strong> functions to find the labels or the scorers for the top 10. Insert this formula in G2 and drag it downwards.<\/p>\n<p><strong>=INDEX($A$2:$A$21,MATCH(H2,$B$2:$B$21,0))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4684\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-14.png\" alt=\"Find top 10 values in excel with formulas\" width=\"643\" height=\"251\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-14.png 643w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-14-300x117.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/p>\n<p><strong>3.2 Identify the Labels When the Top 10 Values are Not Unique<\/strong><\/p>\n<p>Suppose we got a situation where the top 10 values\/scores are not unique and three of the students scored the same score 88. In this sort of situation, finding the labels is tough because a basic <strong>INDEX\/MATCH<\/strong> function only returns the first value so it finds the name\u00a0<strong>Mathews<\/strong>\u00a0thrice. Though <strong>John<\/strong> and <strong>Hawland<\/strong> also scored 88 but their names aren\u2019t in the list. We will have to use an advanced array formula to solve this issue.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4685\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-15.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"250\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-15.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-15-300x117.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Insert the formula in G2 after selecting it and hit ENTER key. Drag the cell down from G2 to G11.<\/p>\n<p><strong>=INDEX($A$2:$A$21,SMALL(IF($B$2:$B$21=H2,ROW($B$2:$B$21)-ROW($B$1)), COUNTIF($H$2:H2,H2)))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4686\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-16.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"424\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-16.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-16-300x199.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Formula Breakdown:<\/strong><\/p>\n<p><strong>SMALL- <\/strong>The <strong>SMALL <\/strong>function looks for the N<sup>th<\/sup> smallest value inside the criteria. It works quite similar to <strong>LARGE<\/strong> function, the only difference being it looks for the smallest values.<\/p>\n<p><strong>IF-<\/strong> IF($B$2:$B$21=H2,ROW($B$2:$B$21)-ROW($B$1))<\/p>\n<p>If B2 = H2 then return the count of rows between B2 and B1.<\/p>\n<p>This formula is an array; therefore, it automatically moves on to the next row and does another calculation.<\/p>\n<p>If B3 = H2 then return the count of rows between B3 and B1.<\/p>\n<p>This process goes on.<\/p>\n<p>The IF function\u2019s calculation for the formula in G2 matches the 10<sup>th<\/sup> item\/student name from the list. So, the only value that is True is 10<sup>th<\/sup> item.<\/p>\n<p><strong>COUNTIF<\/strong>&#8211; COUNTIF identifies the number of times the value has already occurred in the top 10. Only 1 item in the top 10 for the first row has a score of 94, thus COUNTIF will calculate as 1.<\/p>\n<p>Since all other results are FALSE, the <strong>SMALL<\/strong> function will only discover the first smallest number which is 10.<\/p>\n<p>The <strong>INDEX<\/strong> function is then used to find the 10<sup>th<\/sup> value in the source table. Finally, the result for this calculation is found in cell A11, which is Abegil in this instance. The we just drag G2 downwards to apply the formula in the other cells to get the labels for you top 10 values.<\/p>\n<p><strong>3.3 Top 10 Values Based on Single Criteria<\/strong><\/p>\n<p><strong>Step 1: <\/strong>Now we will find the top 10 values\/scores for class eight only using the traditional function. Insert the formula in H2 and hit Enter key.<\/p>\n<p><strong>=LARGE(IF($B$2:$B$21=$J$2,$C$2:$C$21),ROW(G2)-ROW(G$1))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4687\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-17.png\" alt=\"Find top 10 values in excel with formulas\" width=\"641\" height=\"212\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-17.png 641w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-17-300x99.png 300w\" sizes=\"(max-width: 641px) 100vw, 641px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Drag the fill handle downwards and the formula will return the top 10 scores by class eight students.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4688\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-18.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"215\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-18.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-18-300x101.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Use any of the methods shown before to find the labels for these top 10 values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4689\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-19.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"213\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-19.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-19-300x100.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>3.4 Top 10 Values Based on Multiple Criteria<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Select cell H2 and insert the following formula inside the cell and hit Enter key.<\/p>\n<p><strong>=LARGE(IF(($B$2:$B$21=$J$2)*($C$2:$C$21&gt;$K$2),$C$2:$C$21), ROW(G2)-ROW(FG$1))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4690\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-20.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"209\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-20.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-20-300x98.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Drag the fill handle how to input the formula in the other cells also. This formula will return the top 10 scores that are more than 40 and scored by the students of class eight.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4691\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-21.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"201\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-21.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-21-300x94.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Now select cell G2 and incorporate the following formula in it. Drag the cells downwards and you will get the labels for these 10 values.<\/p>\n<p><strong>=INDEX($A$2:$A$21,SMALL( IF(($C$2:$C$21=H2)*(($B$2:$B$21=$J$2)*($C$2:$C$21&gt;$K$2)), ROW($C$2:$C$21)-ROW($C$1)), COUNTIF(H2:$H$2,H2)))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4692\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-22.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"197\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-22.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-22-300x92.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><strong id=\"how-to-find-the-bottom-10-values-in-excel-using-formulas?\" style=\"font-size: 1.7em;\"><\/strong><\/p>\n<p><strong id=\"how-to-find-the-bottom-10-values-in-excel-using-formulas?\" style=\"font-size: 1.7em;\">4.How to Find Bottom 10 Values in Excel using Formulas?<\/strong><\/p>\n<p><strong>4.1 Bottom 10 Values using Dynamic Array Formulas<\/strong><\/p>\n<p><strong>Step 1: <\/strong>In this tutorial, we saw some dynamic array formulas and used them to get the top 10 values from a data table. Now to find the bottom 10 values, all we will have to do is make a small change inside the <strong>SORT<\/strong> function and use \u201c1\u201d instead of \u201c-1\u201d. This will sort the list in ascending order and return us the bottom 10 values as you can see in the picture below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4693\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-23.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"286\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-23.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-23-300x134.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>4.2 Bottom 10 Values using Traditional Formulas<\/strong><\/p>\n<p><strong>Step 1: <\/strong>To find the bottom 10 values using a traditional formula, simply use <strong>SMALL<\/strong> function in place of or <strong>LARGE<\/strong> function.<\/p>\n<p><strong>=SMALL($B$2:$B$21,ROW(G2)-ROW(G$1))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4694\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-24.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"252\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-24.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-24-300x118.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Then find labels for you bottom 10 values using any of the methods shown before.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4695\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-25.png\" alt=\"Find top 10 values in excel with formulas\" width=\"640\" height=\"250\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-25.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Find-top-10-values-in-excel-with-formulas-25-300x117.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p>For ready-to-use Dashboard Templates:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Finding the top 10 values in Excel is a popular way to present data, particularly on dashboards and compiled reports. A top list is used to rate businesses or people according to their values. In this tutorial, we will learn about some formulas for finding the top 10 values in an Excel worksheet. This Content &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-find-top-10-values-in-excel-with-formulas\/\"> <span class=\"screen-reader-text\">How to Find Top 10 Values in Excel with Formulas?<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[535],"tags":[537,536],"class_list":["post-4670","post","type-post","status-publish","format-standard","hentry","category-top-10-values-in-excel","tag-top-10-values-in-excel","tag-top-10-values-in-excel-with-formulas"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=4670"}],"version-history":[{"count":7,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4670\/revisions"}],"predecessor-version":[{"id":8119,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4670\/revisions\/8119"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=4670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=4670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=4670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}