# Creating Top 10 Lists and League Tables in Excel 2007

## Introduction

Welcome to my latest hub on Excel 2007. Today, I am going to look at the **MIN** and **MAX** functions alongside the **SMALL** and **LARGE** functions and how they can be used on your data to provide you with the largest and smallest numbers in your data. Using these functions alongside **INDEX** and **MATCH **allows you can create top 10 lists and league tables.

In addition, I will look at the **INDEX** and **MATCH** functions and how they can extract a label to go along with the data gathered using **MIN, MAX, SMALL** and **LARGE** to create a dynamic table with the label (in my case Hub name) and value (in this instance, total number of hits).

**MIN**and**MAX**provide you with the smallest and the biggest number in your data range.**SMALL**and**LARGE**perform a similar function; however, they allow an argument which can for example allow you to determine the 3 largest numbers in your data, or the second smallest.

## Working with the MIN and MAX functions

I have a number of hubs I have created over time and would like to know the minimum and maximum values of my Hub Score, my Hub Views over different periods of time etc. I can easily use **MIN** and **MAX** to display these.

**MIN** and **MAX** are quite straightforward to use, simply start as with all formulas or functions with an = followed by MAX and the range of cells you want to check.

=MAX(<Range>)

**MIN** works in exactly the same way

=MIN(<Range>)

By creating a formula for each column for both the **MIN** and **MAX** function, I end up with the following.

## Working with the SMALL and LARGE functions

**SMALL** and **LARGE** are very useful if you want to create a league table, or to find a specific value, for example the fourth smallest value in your data range.

For my example above, I want to know the 10 highest values for total overall hits for my published hubs. To do this I use the **LARGE** function in ten formulas, starting with 1, 2, 3 and ending with 10 as below.

=LARGE(<RANGE>,1)

And ending with

=LARGE(<RANGE>,10)

This gives me a list as shown below

Similarly, working with **SMALL**, could give me a list of the ten hubs with the lowest totals. If I wanted to know the third smallest value for total hub views of all my hubs, I would use a formula as below, with the range being the column containing the total views.

=SMALL(<RANGE>,3)

## Limitations of MIN, MAX, SMALL and LARGE functions

If you look at my league table of the ten hubs with the highest number of overall hits above once more, you will notice the limitations that these functions have. While they are fantastic at returning the smallest or largest values in a range, or telling you the third smallest value in a range, that is all they tell you.

For my league table it would be very helpful to know which hubs they were as well as their totals.

In part two of this hub we are going to explore a way to get Excel 2007 to provide us with the names of my top performing hubs automatically to go along with their total hits. To do this we are going to use three functions, one of which we have already used **INDEX, MATCH** and**LARGE**

## Using the INDEX and MATCH functions alongside LARGE

To create a league table that not only contains the total number of hits for my ten most popular hubs but also the names of those hubs, we have to use two new functions alongside** LARGE**. These are the** INDEX** and **MATCH** functions.

What we are doing in essence is

- Firstly we ask Excel to find the largest value in the column for total hits using
**LARGE** - We then ask Excel to use
**MATCH**to find the position of that cell in the same column **INDEX**then looks for the cell that is adjacent to that cell in the column that contains the hub names

Voila, it will construct our league table

OK now onto the formula itself shown in the figure below.

OK, so again starting with **LARGE**, it simply looks for the number one largest value in the column that contains the total hub views exactly as we did in our earlier example.

For **MATCH**, the syntax is as follows:

=MATCH(lookup_value,lookup_array,match_type)

- The lookup_value was provided by the result given to us by
**LARGE** - The lookup_array is once again the column that contains the total hub views and
- The match type is 0 which tells
**MATCH**to look for the number that is exactly equal to the result given to us by**LARGE**

For **INDEX**, the syntax is as follows:

=INDEX(**array**,row_num,column_num)

- The array is the column containing the hub names
- The row_num and column_num is provided by
**MATCH**

So, our two formulas together,

=INDEX('hubs(1)'!A3:A47,MATCH(LARGE(('hubs(1)'!F3:F47),1),'hubs(1)'!F3:F47)) and =LARGE('hubs(1)'!F3:F47,1) provide us with the following table

This is exactly what we wanted to achieve which is brilliant. To create each row, we just increased the value in the **LARGE** part of the formula from 1 to 2 and so on

=INDEX('hubs(1)'!A3:A47,MATCH(LARGE(('hubs(1)'!F3:F47),2),'hubs(1)'!F3:F47)) and =LARGE('hubs(1)'!F3:F47,2)

## Alternatives to INDEX, MATCH and LARGE

Before investigating the possibility of using **INDEX, MATCH** and **LARGE** in combination, I investigated the possibility of using **VLOOKUP. **

**VLOOKUP** is an immensely powerful function, however, it is unable to search a column to the left of the column you are interested in, which is the case in this example. Rather than move the data around each time I extract it into this spreadsheet, I discarded **VLOOKUP** in this instance.

I have another hub which looks into **VLOOKUP** in greater detail. I used it to auto-fill prices for various music media and also to auto-fill a customerâ€™s address based on their customer number. My hub for **VLOOKUP** can be found here.

A pivot table would have worked perfectly in this instance, but not with the **MIN** and **MAX, LARGE** and **SMALL** functions I had already started to use. In fact, I already have such a table but I wanted to follow through with these functions.

Pivot tables are also very powerful, allowing you to create a table from your data and then change it at will. If you decide that you want your pivot table to provide you the hubs with the lowest Hub Score and then change your mind to the hubs with the top ten hits for the last seven days, this can be done easily and quickly. Whatâ€™s more, pivot tables are dynamic, when the data changes they can be instantly refreshed to reflect that.

I also have a hub that goes into detail on creating, using and configuring pivot tables which can be found here.

## Conclusion

I hope that you have enjoyed this hub on the **MIN, MAX, LARGE, SMALL, INDEX** and **MATCH** functions. We explored **MIN, MAX, LARGE** and **SMALL** in detail before exploring how **INDEX** and **MATCH** could make those initial functions even more useful.

We started with the first four functions returning a number (for example the largest number of total hits) and using **INDEX** and **MATCH** we were able to return the actual name of that hub to create a dynamic table league table.

As is often the case in Excel 2007 and why it is such an interesting application to learn and also why it is so powerful is that you can take some seemingly un-related functions and build something using them that is both powerful and very useful. Many thanks for reading and if you have any comments, please feel free to leave them below.

## More by this Author

- 2
Shapes in Excel 2007 and Excel 2010 can be renamed and reordered using the Selection Pane. To save time, shapes can also be grouped so that they can be formatted, manipulated or moved together.

- 2
The OFFSET and COUNTA functions allow you to create dynamic ranges of data. Formulas created to SUM this data (for example) will automatically update if data is added or removed from within the range.

- 9
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.

## Comments 2 comments

As a keen Excel user, I think this is a really good article. I like the way that you separated out the various functions, providing good examples with each. The use of screenshots was also very useful in summarising the key steps people need to take. A great tutorial for some often overlooked functions, great work.