This article will show you how to rank things and create a league table in Excel
- The example we look at relates to ranking students on the basis of their grades
- The formulae we cover are =Rank(), =Offset(), =Match(), in addition to simple arithmetic formulae
Download the ‘Ranking things and Creating League Tables in Excel’ exercise file by clicking here.
Ranking things and creating league tables in Excel
This topic covers something that we are asked to do on a regular basis: rank things in Excel (usually sports teams or students) according to grades or points accrued. The formulae required are =rank(), =match() and =offset(). The technique takes time to master, but will save you time, energy, and will enable you to create effective data summaries.
What is wrong with simply ‘sorting’ data?
Of course, it is possible to use Excel’s ‘sort’ function to perform this kind of task; the drawback, however, is that multiple mouse clicks and scrolls are required. This is onerous and, with the required knowledge, there is a better way. How can we create a table that works without any effort on the part of the user – one that dynamically updates? Let’s take a look.
Technique 1: Implement a Basic =rank() formula
The first step is to implement a simple =rank() formula. This will look at a student grade and tell us where this grade lies in a list of grades. In other words, it will ‘rank’ the student in relation to other students.
Excel will ask us for three elements to complete the =rank() formula: the number that we want to rank, the range of numbers that we want to rank the number in, and whether we want to rank in descending or ascending order.
The final formula will appear as in the graphic on the right. Remember to use the F4 key to fix the references for the second component of the formula, the range of values. When you are happy with the formula, you can click the square in the bottom-right corner of the cell, hold and drag down to copy and paste the formula down the column.
Our example refers to 10 students, but you could easily implement this formula for 100 or 1000. Cool! Now, can we use the ranking information to create a league table?
Technique 2: Use =match() and =offset() formulae to create league table
When we combine formulae together, we begin to unleash Excel’s awesome computational power. An excellent example is combining =match() and =offset() with =rank() to create a league table.
The =match() formula tells is where a value appears in a list of values. Remember the =rank() formula that we have already implemented? By pointing our =match() formula to the column that contains the =rank() formula we can understand how far down the column each ranking (1,2,3) appears.
The =offset() formula looks at an anchor cell and moves away, or ‘offsets’ from that cell a certain number of cells. It then displays the value of the cell it arrives at. For each ranking (1,2,3), we can use offset to display the student at that rank by referring to the match formula we have already created.
It takes some attention to detail to build these formulae effectively. We have to make sure that the correct cell references are ‘fixed’ using the F4 key, for example. However, working at this level of precision is a skill that comes with practice, and will not come without practice. It is cool when it works, and – be warned – it can be addictive!
Technique 3: Deal with Equal Values
Let’s recap. We have created the =rank() formula to tell us where a student’s grade lies in relation to the other grades. Using this information, we have used =match() and =offset() to retrieve student information in order of rank. We have created a dynamic league table. Neat!
In order to make the table robust, we have to deal with equal values. What do we mean by ‘equal values’? In the league table, try making a student grade equal to the grade of another student. An error is created because our model cannot yet handle equal values.
To remedy this, we can create an increment to add to the grade for each student. Our increment gives the highest value to student 1 and the lowest to student 10. If you are working with sports teams, for example, you could allocate the highest increment to the team with the best goal difference. Where points are equal, the team with the highest increment would appear higher.
We make this work by including a new ‘total’ column to add the increment to the student grade. We then point our rank() formula to the ‘total’ column rather than the original grade column.
Technique 4: Completing the League Table
We are now in a position to create our final league table that can deal with equal values.
We have created an =rank() formula that refers to the ‘total’ column which is the sum of the grade and the increment. As in technique 2, we can implement =match() and =offset() formulae to retrieve student data and create the league table.
Let’s test our league table. Try making one or more of the student grades equal – what happens? The league table should cope with equal values and rank according to the increment. Note that the table dynamically updates – this is the main advantage over using Excel’s sort function. Excessive mouse clicks and, in particular, scrolling increase cognitive load and make user experience unpleasant; using these formulae we have made Excel do the work for us.
We would also want to see the student grade next to the student name in the final table. As an extension, see if you can implement a vlookup formula to retrieve and display each student’s grade.
Summary: What we have learned and Why it is useful
Being able to rank things and create this kind of league table is a useful skill that will enable you to create helpful data analysis. The exercise is an excellent example of how combining formulae (=rank(), =match(), =offset()) can create powerful analytical tools. We can make Excel do the work!
But why is this kind of Excel technique so important to learn?
We believe that progress in Excel is not ‘linear’ – x hours study does not result in x units benefit. Rather, there is a group of techniques that – if you master – can dramatically accelerate your Excel learning, unlocking exponential benefits as you improve. =rank() is one of them, and the others are covered on this website.
Mastering these will allow you to approach data-intensive tasks with confidence.
This is the fundamental idea behind the #ExcelRevolution – that focused learning on certain Excel techniques can transform your analytical ability. This has knock-on effects on a company- or organisation-wide level, and even on the economy as a whole.