Create an Excel Football League Table
By almurray
I love undertaking little projects in Excel, especially ones that interest me. A couple of weeks ago I set about creating an Excel Football League Table, and here it is.
The league table calculates a team’s position in the league as the results are entered. The table consists of 5 teams. Expect some related hubs over the coming weeks as the league table grows.
The Excel workbook is made up of the following sheets:
Fixtures – This sheet contains the fixtures for the season. The results are entered onto this sheet after each game and the league table and fixtures sheet for each team update themselves.
Team Sheets – These sheets contain the fixtures for the teams in the league. The results are entered automatically from the Fixtures sheet.
Data – This sheet contains the number of points earned for a win and for a draw. Change the data here to configure how the table is calculated.
Calculations – This sheet performs the calculations from each team sheet and ranks the teams for the league table.
League Table – The league table updates from the Calculations sheet.
Download the Excel Football League Table.xlsx to go through as you read this hub.
Calculating the League Data
All the working out happens on the Calculations sheet. First of all I needed to find out how many wins, draws, losses each team had so that their points total could be calculated. The following Excel functions can be found in the worksheet.
COUNT – used to count the number of games each team has played.
COUNTIFS – used to count the number of wins, draw and losses for each team at home and away. The COUNTIFS function is new to Excel 2007. In previous versions the SUMPRODUCT function should be used.
SUMIF –used to add up the number of goals scored and number of goals conceded by each team.
 | Amazon Price: $14.99 |
 | Amazon Price: $14.99 List Price: $19.99 |
 | Amazon Price: $14.84 List Price: $29.99 |
League Position on Points
The points are then calculated by multiplying the number of wins and draws by the data on the Data sheet.
The next step was to give each team a position based on their points total. This was done using the RANK function. The RANK function returns the rank of a number within a set of numbers, therefore returning a teams league position dependent upon their points total.
However, the RANK function returns the same rank for teams with the same number of points. So I needed to include the team’s goal difference.
League Position after Goal Difference
To include the goal difference of each team I decided to use the SUMPRODUCT function. This is an incredibly powerful and useful function that can be used to test multiple conditions and return a result on how many records met the required conditions.
In the league table spreadsheet the SUMPRODUCT function ran a test on what teams had the same points total, and of those that did, how many teams had a greater goal difference.
This effectively provided a goal difference ranking for teams that had the same points total.
League Position after Goals Scored
A third condition ranking was added to handle the situation where teams also had the same goal difference.
Once again the SUMPRODUCT function was used. This tested to see which teams had the same points total, and the same goal difference, and if so found out how many teams had scored a greater number of goals.
This provided me with a goals scored ranking.
 | Amazon Price: Too low to display List Price: $59.99 |
 | Amazon Price: Too low to display List Price: $59.99 |
![FIFA Soccer 12 [Download]](http://ecx.images-amazon.com/images/I/61Y-iIqqSbL._SL75_.jpg) | Amazon Price: $19.99 |
 | Amazon Price: $26.99 List Price: $49.99 |
The Final League Table
With the three rankings set up, it was time to calculate the teams overall league table position. This was performed by simply adding the rankings together:
=Points Ranking + Goal Difference Ranking + Goal Scored Ranking
On the League Table sheet the following functions were then added to calculate each team’s position throughout the season based on the data in the Calculations sheet.
VLOOKUP – used to look up and return the required data from the Calculations sheet such as team name, matches played, wins etc.
MIN – used to find the team in first place. It looks for the minimum number in the Rank column
SMALL – used to return the appropriate team and league data in the right order from teams 2-5 dependent upon their ranking
Hard to understand this but will surely pull through. Thanks for the education.
Thanks Big Bright.
Any problems, give me a shout.
I will be grateful if u can send me an Excel template on the League table...want follow the correct functions and formulas to create the league table.
My e-mail: bigbright@bigfoot.com
Looking 4ward 2 that.
Thanks in advance.
Hi
I downloaded your league tables and have edited it for my swimming league and everything works correctly when you put in all the results on the Fixtures sheet.
My question is, how do I make it work from the start of the season when there are no scores in the Fixtures page - ie if I leave them blank or put "0" in the cells?
At the moment with zeroes or blanks it shows that all the results to be draws with one point each being awarded.
Is there a way for the program to recognise the blanks and zeroes and update the table when results are added as the season progresses?
Regards
Lev
Email: levbaddley@hotmail.com
Hi Lev,
I am sending you an email with an updated file that handles the blank cells.
Will upload to here when I get a chance too.
thanks i used to wright all my fixtures and tables which takes 4 ever but thanks to u its changed
i WOULD LIKE TO SEE AN EXCEL VERSION ON HOW TO DO FIXTURES
I wonder if you can help. I drunkenly suggested that I draw up an automated Excel league table for a Monday night Darts League. I was hoping to utilise your spreadsheet but if anything what I need doesn't need to be as complex as we play 13 games, and if you lose 8-5 you score 5 points, no win bonus etc to worry about. I've drafted up what I need in excel but don't have the skills to get the league to update. can you help? if so my e-mail is daniel_mortimer@hotmail.com
I am not sure if the file is not working correctly because I'm using an older version of Excel, but the Fixtures info is not populating on the Team pages automatically.
I am attempting to use the sheet to track standings for a league of 8 teams, playing each other once (7 games total). I am not sure how to update the Fixtures sheet to work, and how to add the other 3 team sheets and make those work.
Anything you can suggest is appreciated!! THANKS!!
email: heidichan26@hotmail.com
Dan and Heidi,
I have sent you both an emailto explain further.
Thanks for commenting.
@ Almurray, please send me the same e-mail you sent to heidi..
e-mail: saborangolano@gmail.com
Hi there Almurray
firstly great spreadsheet have been looking for something like this for a while, like Dan and Heidi above I cannot get the fixtures to pre-populate the individual team tabs and the calculations, am I missing something - using excel 2007.
my email is Garybarkerg@aol.com
Thanks in advance and keep up the good work, also the fixture generator is great just adapting it at moment to put dates in...
Thanks
@McLau i have sent you an email
I have sent you an email Gary
Hi almurray,
Please cna you send the same email you sent Gary and @McLau - have real trouble. Don't know the first thing about any of this and would love some help! laura@prezent.me.uk
Thanks!
Hi Laura,
I have sent you an email. Hope it helps.
Hey dude!
Very Nice!
Im doing like an online football 1v1 league and your league table would be perfect could you send me it ?
dean.c.chalmers@gmail.com
Thanks in advance :)
Dean
Nice spreadsheet but like others have been unable to get the data on the fixtures sheet to populate the team tabs. Any help appreciated.
Thanks...Mick
m.burnham@virgin.net
Is the data worksheet missing, I also have nothing populated in the teams worksheets. GeorgeJack1973@gmail.com
Can you make a multi league system with promotion/relegation at the end of the season? This will move teams automatically between leagues. TY
@George The Data worksheet is hidden. Right click a sheet tab and select Unhide to make it visible
@Andy This can be done with more work. id love to create this sort of situation in the future.
Please download the spreadsheet again. The team tabs will now update when results are entered onto the fixtures sheet.
Shaun 22 months ago
I never understood the goal difference =S