This guide will explain how you can rank teams in a sports championship with a league table in Excel.
A sports league involves a group of sports teams or athletes that compete against each other in a particular sport. In case of possible ties in total scores within teams, other factors can help determine the championship winner.
The teams’ ranking in a league is typically based on three factors: total points, goal differences, and goal scoring.
The goal difference is calculated by subtracting the number of goals or points conceded from the number of all goals or points scored in the league. It was mainly introduced to league competitions as a tiebreaker when sports teams finish on equal points.
In case teams are also tied in goal differences, we use the number of goals scored as another tiebreaker. Between the two tied teams, the team with the most goals throughout the competition will gain victory.
For example, let’s say two soccer teams both won 10 games in the championship. To determine the winner, a league table can compute their goal difference rank. While the goal difference factor has a smaller weight in the computation, it should be enough to break a tie.
Now that we know when to use a league table, let’s explore a sample spreadsheet that ranks teams using an existing league table.
A Real Example of Ranking a League Table in Excel
Let’s take a look at a real example of a spreadsheet that ranks teams based on data given in a league table.
The league table below has data for six different teams. Each team played 15 games with each other. For each team, a game can end in either a win, loss, or draw.
Column I computes the total score of each team. Both Team E and Team A scored 33 points in the championship, giving us an initial tie. Additional factors like goal difference and goals scored can help break that tie. After taking into account additional factors, Team A is ranked first place.
To get the PTS value in Column I, we just need to use the following formula:
=C2 * 3 + D2
The goal difference or GD is computed by getting the difference between the Goals For (GF) and Goals Against (GA) values. A positive GD implies that a team scored more points than they gave away.
The Rank PTS value in column J comes from the following formula:
=COUNTIF($I$2:$I$7,">"&I2) + 1
We can derive the Rank GD value in column K using the following formula:
Dividing the ranking of each team’s GD by 100 gives this factor a smaller weight in the overall ranking later on. Similarly, we divide the Rank GF by 1000 to give it an even smaller weight in the ranking.
The Total Rank values in column M are simply the sum of columns J, K, and L. The final rank of each team in the league is based on the results in column M. The ranking is in ascending order, with a rank of 1 being the victor.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to create your own ranked league table in Excel, let’s start writing it ourselves!
How to Rank a League Table in Excel
This section will guide you through each step needed to create your own ranked league table in Excel. You’ll learn how we can use the
COUNTIF function to rank multiple teams based on several criteria.
Follow these steps to start your own league table in Excel:
- First, compute the number of points each team receives for wins and draws. In this example, each win is worth three points, and each draw is worth one point.
- We can use the
COUNTIFformula to quickly rank each team in terms of the number of points they have accumulated.
- In the case of teams receiving the same number of points, we can use the goal difference to break the tie.
- To further break any ties, we can use the Goals For (GF) metric.
- We compute the total rank by summing up the rank values for points, goal differences, and goal scoring. This total rank will be the basis of the final ranking. In this example, we used the formula =J2+K2+L2 to get the total rank value
- We can use the
COUNTIFformula again to find the ranking of each team based on the total rank value in column M. In this example, we find out that Team A is ranked first place, scoring slightly lower than rival Team E.
That’s all you have to remember to start using your own league table to rank teams in Excel. This step-by-step guide shows how you can quickly set up a league template that you can use for any league competition for any sport.
Ranking league tables is just one use case that takes advantage of several Excel formulas to compute specific values. With so many other Excel functions out there, you can surely find one that suits your needs.
Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.