This post may contain affiliate links. Please read my disclosure for more info. Power Query Course

Top 10 list in Excel using formulas

Create top 10 using formulas

A top 10 list is a common form for displaying information, especially on dashboards and summarized reports.  It is easy to create the list when working with sorted data, just link to the top 10 items in the list… easy!  It’s also relatively easy when using Auto Filter, Tables and Pivot Tables, it is a default filter setting within these Excel features.

However, when creating a top 10 list using formulas on a non-sorted dataset things becomes a little bit trickier.  In this post, I will show you exactly how to do it.  Through these methods, you’re not restricted to a top 10,  you can create a top 5, top 8 or any number you choose.

One of the most common problems is dealing with duplicate values, but don’t worry, we will be covering that too.  Hopefully, along the way you’ll get to learn a little more about how Excel works.

Using the LARGE function

It is easy to obtain the Top 10 values, it is getting the Names/Labels which relate to those 10 which is not straightforward.

Look at the example below.  There are 20 customers, with their Sales Values.

Top 10 with Formulas LARGE Function

If we wanted to create a Top 10 of these customers, without sorting the customer list, we could use the LARGE function.  Cell F2 contains this formula:

=LARGE($B$2:$B$21,D2)

LARGE only has two arguments:

=LARGE(Range,k)
  • Range – The range of data to be analyzed.
  • k – The nth item to be found.

In our example the k value is linked to D2, then copied down, so that it will find the Top 10 values (look at the screenshot above).

Finding the Labels for the Top 10

We have the values, so now we can use INDEX/MATCH to find the names of the customers.  Cell E2 in our example would contain the following formula.

=INDEX($A$2:$A$21,MATCH(F2,$B$2:$B$21,0))

Copy this Formula down to Cells E3-E11.  Our worksheet would display as follows:

Top 10 with Formulas Duplicate Names

There is one really big problem – our Top 10 values are not unique.  There are 3 values of 80.  As INDEX/MATCH only returns the first value it finds the name of Customer #1 is returned 3 times.  Yet, Customer #5 and Customer #20 do not even feature on the list.  This is clearly an error.  So, how do we get around this little problem?

Finding the Labels when the Top 10 values are not unique

To solve this issue, we will first round the values, then use the ROW function to create a unique extension that will ensure the values are always unique.  Keep reading it will all become clear.

Firstly, we need to decide on an acceptable level of accuracy.  For example, if our numbers are accurate to 2 decimal places we should round the LARGE function to that level.

{=LARGE(ROUND($B$2:$B$21,2),D2)}

This is now an Array formula, do not type the { }, but press Ctrl+Shift+Enter to enter the formula, excel will enter the { } automatically.

Now that we have rounded data we can add a small number to the end of our values to make them unique.  We use the ROW() function for this.  The Formula in Cell F2 will be changed to:

{=LARGE((ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),D2)}

This formula is also an Array formula, so remember to enter the Formula with Ctrl+Shift+Enter.

By adding the Row value divided by 10000 we are creating unique numbers.  Customer #7 has a value of 120, but once we’ve added the ROW value it is now 120.0008.  Because we rounded the value before adding the ROW number each number is guaranteed to be unique.

Note, if you rounded the value to 4 decimal places the ROW function will need to be divided by a larger number.  The aim is for the ROW number to add additional decimal places rather than change the value of the rounded number.

Copy the formula in Cell F2 down into F3-F11.

We now apply the same ROUND and ROW functions to the INDEX/MATCH function in Cell E2.

{=INDEX($A$2:$A$21,MATCH(F2,(ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),0))}

Again, this is an Array formula – enter with Ctrl+Shift+Enter.  Copy this formula down into Cells E3-E11.

Our Top 10 list looks like this:

Top 10 with Formulas No Duplicate Names

Now we can just reduce the number of decimal places displayed to show the correct level of accuracy.

Correcting the numbering

There is just one final stage (which is optional).  Our Top 10 includes positions from 1 to 10, but we know that positions 7 to 9 all have the same value, so surely, they should all be given a 7.  That’s how it works in the Olympics, right?  If two competitors have the same result they share the position.

Let’s change our LARGE formula again.  We do not need to use the values in D2-D11 in our formula, we can use the ROW function to create the same value.  Cell F2 will now become:

{=LARGE((ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),ROW(D2)-ROW($D$2)+1)}

The red highlight section will create the number count from 1-10 when the formula is copied down to Cells F3-F11.

Now, we must correct the position numbers.  Cell D2 will always be equal to 1, so we can hardcode a value of 1 in that cell.  In Cell D3 we can enter the following formula and copy it down to Cell D11.

=IF(ROUND(F3,2)=ROUND(F2,2),D2,COUNT($D$2:D2)+1)

This formula identifies any rounded values which are the same, and gives them the same position in the Top 10.

The final result now looks like this (notice how the values of 80 all have unique customer names and are all 7th in the Top 10 list.

Top 10 with Formulas Final

Will this definitely calculate the correct result?

The only issue with this method is the lost accuracy from rounding the values.  For example, where there are two similar values as shown below

B2 = 60.05634
B3 = 60.05621

These values, if rounded to 2 decimal places, would both round to 60.06.  By applying the ROW function to get unique values, the values would change to:

B2 = 60.062
B3 = 60.063

As a result, these values would switch order in the Top 10.  B3 would be above B2, even though B2 is a larger value.  This is why the rounding accuracy is important.  Provided you round the values to the right level it will give you the correct result.

What about a Bottom 10? (or Bottom 11? or Bottom 8?)

Good question, I’m glad you asked.  In the example above just use the SMALL function rather than the LARGE function.  Everything else will be the same.

14 thoughts on “Top 10 list in Excel using formulas

  1. Autofilter lets you to choose top or bottom and also change the number. So you can choose top 11, top 8, or whatever you like. Just click on the filter and choose “Number Filters”. Click Top 10… and you can change the criteria.

    • Excel Off The Grid says:

      Hi Jason – ah yes, you’re right.

      Thank you correcting me, I appreciate it. I will update the post, so that’s (hopefully) not giving any incorrect information.

      Note to self . . . always check the facts.

  2. Aya says:

    Hello,
    I have an excel worksheet with many columns.. and I have a ranking score in column (H).. I need exact formula to retrieve column (b) for the first five records with ranking score between (20 and 25) with duplicate values exist..

    Thank you for your help

    • Excel Off The Grid says:

      Hi Aya,

      Work through the examples in this post and you should be able to achieve that.

      Pay special attention to the section titled “Finding the Labels when the Top 10 values are not unique”, as this is where you’ll find the information to make your own formula.

  3. J Cridford says:

    Hi, I have a raw data sheet and a front ‘summary’ sheet showing sales by account, with sales totals ‘ranked’ by number in order from smallest to largest. I want to write a formula into the summary sheet that will pull in the top 10 products for a specific account when their account number is typed in to cell A3. What formula would I need for this?

    • Excel Off The Grid says:

      Hi,

      It’s not necessary to rank the data. The same result can be achieved by adding some true/false logic into the formula:

      {=LARGE((Range_Of_Account_Numbers=Account_Number)*(ROUND($B$2:$B$21,2)+ROW($B$2:$B$21)/10000),D2)}

      Notice the added “(Range_Of_Account_Number=Account_Number)*” towards the start of the formula.

      Retrieving the description is the same, as it includes the row number within the formula.

      • Jonathan Cridford says:

        Hi Mark, thanks. I just wanted to ask which columns your formula would refer to for B2:B21 and D2?
        The column headings I have are:
        A: Customer Number
        B: Customer Name
        C: Product Code
        D: Product Description
        E: Quantity Ordered

        • Excel Off The Grid says:

          Hi Jonathan,

          I have added your columns into the formula below

          {=LARGE((Customer_Number=SpecificCustomerToFind)*(ROUND(Quantity_Ordered,2)+ROW(Quantity_Ordered)/10000),nth_position)}

          nth_position = 1 for largest, 2 for 2nd largest, 3 for 3rd largest etc.

          Once you’ve got a list of the values from the formula above use the following formula to get the product code.

          {=INDEX(Product_Code,MATCH(Result_From_Formula_Above,(ROUND(Quantity_Ordered,2)+ROW(Quantity_Ordered)/10000),0))}

          Both of these are array formulas. Don’t type the curly braces, but press Ctrl + Shift + Enter to complete the formula. Excel will add the curly braces by itself.

          If you’ve not used array formulas before check out this post:
          https://exceloffthegrid.com/understanding-basic-array-formulas/

  4. akira says:

    Thanks for great tutorial. But can I ask why two sales columns doesn’t work?

    Imagine in your example, customer name is still in A column, but his sales value are not only in B column, but also in C column.

    If i use =LARGE($B$2:$C$21,D2), everything works and TOP10 list is created as expected.
    But when i try match it with labels in this way:
    =INDEX($A$2:$A$21,MATCH(F2,$B$2:$C$21,0))
    it no longer works and it producing inaccessible value.
    Why?

    • Excel Off The Grid says:

      Hi Akira,

      That’s a good question. The error is caused by the MATCH function, as it only works with a single column.

      When working with multiple columns, we just have to find another way of calculating the position of the label.

      Based on the scenario you provided the following should work:
      {=INDEX($A$2:$A$21,(LARGE(ROUND($B$2:$C$21,2)+ROW($A$2:$A$21)/10000,$D2)-LARGE(ROUND($B$2:$C$21,2),$D2))*10000-ROW($A$1))}

      This is a CSE array formula, so don’t include the { or } as Excel will include these itself, but do press Ctrl + Shift + Enter.

      Alternatively, the following would achieve the same without Ctrl + Shift + Enter:

      =INDEX($A$2:$A$21,(AGGREGATE(14,4,ROUND($B$2:$C$21,2)+ROW($A$2:$A$21)/10000,$D2)-AGGREGATE(14,4,ROUND($B$2:$C$21,2),$D2))*10000-ROW($A$1))

      There may be easier solutions, but these methods use the same methodology as the other sections of this post.

  5. Ronald Jerger says:

    I have a unique application in which I have multiple (26) columns spaced apart with a numeric value, I am trying to evaluate only the top 12 of these (some will probably have a value of zero), and add these up to the value of these 12 numbers, the formula LARGE appears to not work when the values are not in the same column… HELP

    • Excel Off The Grid says:

      Hi Ronald,

      I’m struggling to picture the data layout in my mind. I’ll send you an e-mail, hopefully you can share the file with me.

Leave a Reply

Your email address will not be published. Required fields are marked *