Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to calculate Top 10 with formulas in Excel

Top 10 with formulas featured image

Top 10 lists are a common form for displaying information, especially on dashboards and summarized reports. It is easy to create a top 10 in Excel when working with sorted data; just cell link to the top 10 items in the list… easy! It’s also relatively simple when using AutoFilter, Tables, and PivotTables, as it is a default filter setting within these features. However, creating a top 10 with formulas on a non-sorted dataset, things become a little bit tricky.

But, formulas are the most flexible method for reporting in Excel. Therefore, in this post, I will show you exactly how to calculate a top 10 list. Through these methods, you’re not restricted to a top 10; you can create a top 5, top 8, or any number you choose.

Since the introduction of dynamic array calculations in Excel, we have a new and easier method for creating the top 10 list. Therefore we will start by looking at dynamic arrays. However, don’t worry if you have Excel 2019 or prior, we will cover a solution for that too.

Download the example file: Click the link below to download the example file used for this post:

Note: If you don’t have a Dynamic Array-enabled version of Excel, the tabs containing those examples will display errors. 

Watch the video:

Note: The blog post has been updated to include the latest dynamic array formulas; these are not included in the video.

Watch the video on YouTube

Dynamic array functions

If you have a dynamic array enabled version of Excel (Excel 365 and Excel 2021), it’s excellent news, as you don’t need to rely on complicated formulas. Instead, dynamic arrays make it easy.

INDEX / SORT / SEQUENCE function combination (Excel 2021 & Excel 365)

Start by looking at the Top 10 – DA tab in the example file.

In our example file, there are 26 customers, with their corresponding locations and values.

Dynamic Arrays using SORT

In cell F4 the formula is:

=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})

And that’s it! It’s a simple solution. No need for complex formulas, no need to press Ctrl+Shift+Enter, and no need to copy formulas down.

Let’s dig into this a bit deeper to understand how it works.

SORT

SORT has four arguments:

=SORT(array, [sort_index], [sort_order], [by_col])
  • array: The range of cells, or array of values to be sorted.
  • [sort_index]: The nth column or row to apply the sort to. For example, to sort by the 3rd column, the sort index would be 3.
  • [sort_order]: 1 = sort in ascending order; -1 = sort in descending order (if excluded, the argument defaults to 1).
  • [by_col]: TRUE = sort by columns; FALSE = sort by rows (if excluded, the argument defaults to FALSE).

In our formula, we used the following SORT.

SORT(A2:C27,3,-1)

This sorts cells A2 to C27 on the 3rd column in descending order.

Find out more about the SORT function in this article: SORT Function in Excel.

SEQUENCE

The SEQUENCE function has four arguments:

=SEQUENCE(rows, [columns], [start], [step])
  • rows: The number of rows to return
  • [columns]: The number of columns to return. If excluded, it returns a single column.
  • [start]: The first number in the sequence. If omitted, it starts at 1.
  • [step]: The amount to increment each subsequent value. If excluded, each increment is 1.

In our example, the SEQUENCE function creates a list from 1 to 10. We only need the first argument; we can use the default options for the remaining arguments.

SEQUENCE(10)

This formula evaluates to an array {1;2;3;4;5;6;7;8;9;10}

Find out more about the SEQUENCE function in this article: SEQUENCE function in Excel.

INDEX

Let’s use both SORT and SEQUENCE into a traditional INDEX function:

=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})

The formula calculates the first 10 results from the SORT and returns columns 1 and 3.

That was so easy… right.

CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE function combination (Excel 365 only)

If you have Excel 365, we have access to even more dynamic array formulas. Two of these are CHOOSECOLS and CHOOSEROWS, which we can use instead of the INDEX function.

Top 10 calculation with CHOOSECOLS and CHOOSEROWS

The formula in cell K4 is:

=CHOOSEROWS(CHOOSECOLS(SORT(A2:C27,3,-1),1,3),SEQUENCE(10))

The formula may be longer than the INDEX alternative but it is more flexible. Let’s take a look at the CHOOSEROWS and CHOOSECOLS functions.

CHOOSEROWS and CHOOSECOLS

The CHOOSEROWS and CHOOSECOLS return only the specified rows or columns from an array. They have similar syntax and only have 3 arguments:

=CHOOSEROWS(array, row_num1, [row_num2],…)
=CHOOSECOLS(array, col_num1, [col_num2],…)
  • array: The array containing the rows or columns to be returned
  • row_num1 / col_num1: The first row/column to be returned
  • [row_num2] / [col_num2] : Additional row / column numbers to be returned

In our formula, we used the following:

CHOOSECOLS(SORT(A2:C27,3,-1),1,3)

This takes the sorted values from cells A2:C27 (sorted by column 3 in descending order) and only returns columns 1 and 3.

Next, we wrap the result inside a CHOOSEROWS function.

=CHOOSEROWS(CHOOSECOLS(SORT(A2:C27,3,-1),1,3),SEQUENCE(10))

This takes the previous result from CHOOSECOLS and returns only the first 10 rows.

Bottom 10

To get the bottom 10, we only need to change the 3rd argument of the SORT function from -1 to 1. Look at cells F18 and K18 in the example file to see this in action.

Dynamic arrays with criteria

Even if we have selection criteria to apply, it is still straightforward with dynamic arrays. Cell J4 contains the name of the city; London or Birmingham. We want the top 10 to only include customers in that city.

Look at the Top 10 – DA with criteria tab in the example file.

Dynamic arrays with criteria

The formula in cell F4 is:

=INDEX(SORT(FILTER(A2:C27,B2:B27=J4),3,-1),SEQUENCE(10),{1,3})

The only difference to the previous example is that we are using the FILTER function to include the matching items, before it is fed into the SORT function.

FILTER

FILTER has three arguments:

=FILTER(array, include, [if_empty])
  • array: The range of cells, or array of values to filter.
  • include: An array of TRUE/FALSE results, where the TRUE values are retained in the result.
  • [if_empty]: The value to display if no rows are returned.

In our example, the FILTER function is:

FILTER(A2:C27,B2:B27=J4)

It returns cells A2 to C27, but only where the corresponding values from B2 to B27 equal the selected city in cell J4.

Find out more about the FILTER function in this article: FILTER function in Excel.

Bottom 10 with Criteria

Look at the example file (cells E17:H27) to see how a bottom 10 with criteria is calculated.

CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE combination with criteria

In the example file, you will also find the CHOOSECOLS / CHOOSEROWS / SORT / SEQUENCE combination in cell M18.

=CHOOSEROWS(CHOOSECOLS(SORT(FILTER(A2:C27,B2:B27=J4),3,1),1,3),
SEQUENCE(10))

This combination of functions detailed above can be used with Excel 365 only.

Working with multiple criteria

The key to working with multiple criteria is the FILTER function.

Look at the Top 10 – DA with multi tab in the example file.

An additional criterion has been added to only include items with a value equal to the city OR less than 100.

Multi Criteria with Top 10

The formula in cell F4 is:

=INDEX(SORT(FILTER(A2:C27,(B2:B27=J4)+(C2:C27<J6)),3,-1),
SEQUENCE(10),{1,3})

The filter criteria are highlighted above. We can use this approach to add as many filter criteria as we like.

With the FILTER function, the plus symbol ( + ) creates OR logic; to create AND logic, we use the asterisk ( * ). This is shown in the example below.

=INDEX(SORT(FILTER(A2:C27,(B2:B27=J4)*(C2:C27<J6)),3,-1),
SEQUENCE(10),{1,3})

Traditional functions

OK, now let’s move on to look at traditional functions (i.e., non-dynamic array). Using these, obtaining the top 10 values is not a bit issue. But the challenge is getting the names/labels that relate to those ten values.

Start by looking at the Top 10 – non-DA tab of the example file.

Using the LARGE function

In our example file, there are 26 customers, with their values.

Top 10 source scenario

We use the LARGE function to create a top 10 of these customers (without sorting the list). Cell G4 contains this formula:

=LARGE($C$2:$C$27,ROW(F4)-ROW(F$3))

LARGE 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 data range to analyze is cells C2 to C27.

The k value is calculated as the row number minus the row number of the header row. This always calculates the relative row position in a range of cells. So, for example, the first row of data calculates as 1, the second row calculates as 2, and so on.

ROW(F4)-ROW(F$3)

The formula in cell G4 is copied down to display the top 10 values.

Finding the labels for the top 10

We have the values, so now we just need to calculate the customer name for that value.

We can’t use VLOOKUP as the customer name is to the left of the lookup value. So instead, we will use the INDEX/MATCH formula combination.

Note: Since XLOOKUP was released after dynamic arrays, I have excluded this as an option.

Cell F4, in our example, contains the following formula.

=INDEX($A$2:$A$27,MATCH(G4,$C$2:$C$27,0))

If this formula were copied down into cells F5:F13. Our worksheet would display as follows:

Top 10 Duplicate Values

Can you see the problem? Our top 10 values are not unique; there are 3 values of 80 (see the screenshot above). A basic INDEX/MATCH calculation only returns the first value; therefore, it finds the name Alfa 3 times. Yet, Echo and Tango, who also have 80, do not feature on the list. This is clearly an error, so how do we get around this?

Finding the labels when the top 10 values are not unique

To solve the issue of finding labels with non-unique values, we turn to an advanced array formula. The formula in cell F4 should be:

{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1)),
COUNTIF($G$4:G4,G4)))}

Woah!!! That’s a big and complicated formula!

Note: This is a special type of formula known as an array formula. Don’t put the curly brackets ( { } ) at the start or the end when typing the formula into the formula bar; when you press Ctrl+Shift+Enter, Excel adds the curly brackets by itself. Pressing Ctrl+Shift+Enter lets Excel know that it is an array formula.

REMEMBER! – If you go back into an array formula to edit it, you need to press Ctrl + Shift + Enter to re-enter the formula.

This formula behaves like INDEX/MATCH, but returns the 1st, 2nd, 3rd, 4th… or nth value. Let’s dig a bit deeper to understand how it works.

Section 1 – IF function

In the middle of the formula, we find an IF function.

IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1))

In English, this says:

If C2 = G4 then return the count of rows between C2 and C1.

As this is an array formula, it automatically goes onto the next row and calculates again

If C3 = G4 then return the count of rows between C3 and C1.

And it keeps on going.

If C4 = G4 then return the count of rows between C4 and C1.

This includes all the cells from C2 to C27.

For the formula in F4, the IF function calculates as:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE}

The 7th item in the list matches cell G4; therefore, the only value which is not FALSE is 7.

Section 2 – SMALL and COUNTIF functions

If we feed the result above into the SMALL function, it would be as follows:

SMALL({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE},COUNTIF($G$4:G4,G4))

SMALL finds the nth smallest value. It works in a similar way to LARGE, but for the smallest value.

SMALL only has two arguments:

=SMALL(range,k)
  • range: The range of data to be analyzed.
  • k: The nth item to be found.

In this context, COUNTIF calculates how many instances of the value have already appeared in the top 10.

For the first row, there is only 1 item in the top 10 with a score of 120, so COUNTIF will calculate as 1.

The SMALL will find the first smallest value, which is 7, as all the other results are FALSE.

Finally, it wraps that in the INDEX function to find the 7th value in the source table.

=INDEX($A$2:$A$27,7)

This calculates to cell A8, which in our example is Golf.

All the workings above were to show how the formula works. Now we can copy the complete formula down into cells F5:F13.

Testing on duplicate values

Let’s test out our duplicate values logic with cell F11. There are 3 values in the top 10 which are all 80; they are found in G10, G11, and G12. The label in F11 should be Echo, as it is the 2nd value of 80.

The formula in F11 is:

{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G11,ROW($C$2:$C$27)-ROW($C$1)),
COUNTIF($G$4:G11,G11)))}

The IF portion of the function calculates as:

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE}

Rows 1, 5, and 20 in the source table all match the value of 80.

Therefore, the COUNTIF calculates to 2, which makes sense, as it is the 2nd matching value we are looking for.

SMALL({1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE},2)

As the second smallest number is 5, the INDEX function returns the 5th value in the source list, which is Echo. Therefore, it has calculated the correct value.

Bottom 10 values

If we want the bottom 10 values, the only change is that the formula to get the values uses SMALL, rather than LARGE.

Look at the example file. The formula in cell G18 is:

=SMALL($C$2:$C$27,ROW(F18)-ROW($F$17))

The only difference between cell G18 and cell G4 is the use of the SMALL function.

The formula in cell F18 is the same as we saw above before (but pointing at different cells).

=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G18,ROW($C$2:$C$27)-ROW($C$1)),
COUNTIF($G$18:G18,G18)))

Adding criteria

Now it’s time to look at adding selection criteria. In our example file, look at the Top 10 – non-DA with criteria tab.

As we saw before, cell J4 contains the name of the city; London or Birmingham. We want to return the top 10 for the selected city only.

Top 10 with criteria

Cell G4 has the following function:

{=LARGE(IF($B$2:$B$27=$J$4,$C$2:$C$27),ROW(F4)-ROW(F$3))}

This is another array formula. Remember, don’t enter the curly brackets, but press Ctrl+Shift+Enter again.

This uses the same logic as we’ve already seen. The IF function checks cells B2:B27; if the value matches cell J4, the value in C2:C27 is returned.

When the city is London, the IF calculate as follows:

{80;FALSE;FALSE;FALSE;80;100;120;FALSE;95;FALSE;FALSE;55;FALSE;FALSE;
-35;30;FALSE;FALSE;FALSE;80;FALSE;-20;75;-15;FALSE;FALSE}

By using the LARGE function, only the London values are returned into the top 10. All the non-London values calculate as FALSE.

We make a similar adjustment to the formula which calculates the customer name (the added section is in bold)

={INDEX($A$2:$A$27,SMALL(
IF(($C$2:$C$27=G4)*($B$2:$B$27=$J$4),ROW($C$2:$C$27)-ROW($C$1)),
COUNTIF(G4:$G$4,G4)))}

This is an array formula. Don’t enter the curly brackets. Do press Ctrl+Shift+Enter again.

Working with multiple criteria

Look at the Top 10 – non-DA with multi tab in the example file.

An additional criterion has been added to only include items with a value equal to the city OR less than 100.

Traditional Formulas - Multiple criteria

The key to this is the arguments contained inside the IF function.

The formula in cell G4 is:

{=LARGE(IF(($B$2:$B$27=$J$4)+($C$2:$C$27<$J$6),$C$2:$C$27),
ROW(F4)-ROW(F$3))}

The formula in cell F4 is:

{=INDEX($A$2:$A$27,SMALL(
IF(($C$2:$C$27=G4)*(($B$2:$B$27=$J$4)+($C$2:$C$27<$J$6)),
ROW($C$2:$C$27)-ROW($C$1)),
COUNTIF(G4:$G$4,G4)))}

These are array formulas, so remember to press Ctrl+Shift+Enter.

The filter criteria are highlighted above. We can use this approach to add as many additional filter criteria as we like.

With Excel, the plus symbol ( + ) creates OR logic, and an asterisk ( * ) creates AND logic. This is shown in the formulas below:

{=LARGE(IF(($B$2:$B$27=$J$4)*($C$2:$C$27<$J$6),$C$2:$C$27),
ROW(F4)-ROW(F$3))}
{=INDEX($A$2:$A$27,SMALL(
IF(($C$2:$C$27=G4)*(($B$2:$B$27=$J$4)*($C$2:$C$27<$J$6)),
ROW($C$2:$C$27)-ROW($C$1)),
COUNTIF(G4:$G$4,G4)))}

Wow! That was getting complicated.

Conclusion

Once you know the techniques and functions, calculating a top 10 using formulas in Excel isn’t too bad.

This post demonstrates how good dynamic arrays are; we no longer need to rely on complex array formulas.

Other resources you might find useful:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published.