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.

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 n
^{th}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:

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:

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.

## 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.

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.

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.

You’re welcome! I know Excel has so many hidden features it seems like there’s no end to learn something new in it.

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

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.

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?

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.

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

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/

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?

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.

That’s a great answer. Thank you very much.

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

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.

Dear Excel off the Grid,

Thank you for this guide! This has helped me a lot!

I’d like to ask your assistance when implementing this guide with dates. If we use this example, I have a column B that indicates customer contract expiry dates. I would want to create TOP 10 list in a way that it would show the CLOSEST date compared to TODAY. Therefore using LARGE or SMALL function doesn’t work in a way.

I know it’s possible (like everything in excel) to do this, but I’ve tried now to implement this with many way, with no result.

Let’s see if I can get some help here 🙂

Best regards,

Miikka

Hi Miikka,

I’ve got a possible solution for you. It’s not particularly tidy, but it should work.

Let’s assume there are dates in Cells A2-A100, and Customer names Cells B2-B100.

In Cell C2 type this formula and copy it down to C100.

=A2-TODAY()

This will calculate how many days old the customer inquiry is.

Then in D2 type this formula and copy down to D100.

=RANK(C2,$C$2:$C$100,0)+COUNTIF($C$2:C2,C2)-1

Use an INDEX/MATCH formula to get the date and customer name based on the values in Cells D2-D100.

how should I edit the formula if the same customer has bought more than once?

thank you for sharing such valuable information

What is the result you’re looking for? Do you want the duplicate customer to be (a) ignored (b) added to other sales by the same customer (c) included in the Top 10 as a separate item.

A and B… In a sense that each of that customer’s separate purchases are summed up first

You’ll need to find a middle step which summarizes the data into a single table table first.

There are a few options to achieve this:

1. Pivot Table

2. Power Query

3. Dynamic Array Formulas – https://exceloffthegrid.com/dynamic-arrays-in-excel/

4. Array Formula – https://exceloffthegrid.com/list-unique-or-duplicate-values-with-excel-formulas/

How would you get the index match to work if you have two “Customers” with exactly the same numerical value even with rounding. is there a way to force it to label both values instead of labelling both with the same name.

Hi Hannah,

Read the section of the post entitled “Finding the Labels when the Top 10 values are not unique”. This shows how to do it.

Hi All,

If i have a list that contains 71 different accounts in one column (accounts can be repeated), but i want to filter by top 20 values against that account number within a list, how do i do this please?

Please read the conversation thread above from Keabetswe for the answer.