ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

Create an Excel Football League Table

Updated on November 25, 2011

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.

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.

Ranking teams on goal difference
Ranking teams on goal difference

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.

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

Excel football league table
Excel football league table

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Sean 2 years ago

      Hi I'm try

    • almurray profile image
      Author

      Alan Murray 3 years ago from Ipswich, United Kingdom

      @Gary Banks The spreadsheet can be expanded to cover as many teams as necessary. Use the formulas used for the smaller table and adapt to your needs.

    • profile image

      Gary Banks 3 years ago

      Hi

      like the look of the table but would want to know how I can expand it and use it for a full league table for say 24 teams to use to track as part of my football statistics

    • profile image

      Kent 3 years ago

      Hi Almurray

      Great spreadsheet:)

      The Fixtures info is not populating on the Team pages automatically.

      Do you have a newer Version, I am using Excel 2010?

      Thanks.

      email: kentallen@online.no

    • profile image

      Jerome 3 years ago

      Hi Al

      The team lists, still isn't populating from the fixture list

      Could you email the newer version

      Thanks jmorley58@hotmail.com

    • profile image

      Hussein Fakharany 3 years ago

      I am asking for a FIFA world ranking spreadsheet, and thank you for this one, it was very helpful

    • profile image

      CHRISO 4 years ago

      I have set up a FIFA league for 4 of us .. We play each other each week once and then keep tabs on how each of us are doing.We are looking to do this until we get bored. Is there a spreadsheet that can just generate 4 to play each other unlimited amounts of time. Generates a fixture list and keeps track of the results in a league table. The one I downloaded only can play each twice.. Thanks.

      Email me

      chrisa2011@hotmail.com

    • profile image

      DB 4 years ago

      This is fantastic. Good work mate, saved me a lot of time!!

      Any chance I could get the blank version? I want to edit it to suit a darts league my friends and I run.

      Cheers again bud.

      Email: dnb1987@live.co.uk

    • profile image

      Sandeep Chakroborty 4 years ago

      Hi, could you please mail me as well - on violinnme@gmail.com.

      Also if you have any other detailed document on further details, working, etc

      Thanks in advance!

    • profile image

      salmansalha 4 years ago

      HI I tried it and all is fine but how can I change the names on the league table ?

      PLease reply to salmansalha@hotmail.com

    • profile image

      Phil 5 years ago

      Al - could you send me the spreadsheet you sent to Dan please that enables bonus points scoring to be used.

      Cheers,

      Phil

    • profile image

      Nigel James 5 years ago

      Can you send me the Excel sheet for this please? Email - nigee690@hotmail.com

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      Hi Andy,

      I have sent you an email to have a look at your file.

    • profile image

      Andy Eyres 5 years ago

      Hi Al,

      Have modified your spreadsheet to give me 15 indoor bowls teams and it all works a treat except for the games which have not been played yet i.e no result, but it's inserting zeroes for these and producing, as a result, draws for all these games so distorting the League table. Am using Excel 2010. Can you help please ?

      andeyres@aol.com

    • profile image

      Chris Baldwin 5 years ago

      Hi Al,

      I am Editing the sheet, changing the Teams and adding more teams to the league etc. . but i getting the n/a message and when i update the scores etc. . the table wont update according to the scores input.

      Sheet is exactly what i need, just need to get my head around it.

      Any help would be appreciated

      Thanks

      chrisbaldwin27@hotmail.com

    • profile image

      Matt 5 years ago

      Hi almurray this spreadsheet looks awesome btw, I have joined a 6-a-side league and need a table which i can edit myself but they have a different points scoring eg 2 points for a win and 1 point for a draw. How can i change that on the spreadsheet you have provided? Or have you got another blank copy which would be easier to edit? Reagrds Matt

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      I have emailed you Abdulkadir

    • profile image

      Abdulkadir 5 years ago

      Please Almurray I tried to do things as it appears in your sample but all i see is (#N/A) please help me

      And for those who commented on this please if you can help me like you got helped by Almurray, my email is dmaxdas@hotmail.com

    • profile image

      smegfish 5 years ago

      Ive got a similar spreadsheet that i use for fifa, the idea is that 2-4 players each have 3 teams (good bad n ugly) and these play off against each other, obviously your own teams cant play each other but other than that it sorts out fixtures and rankings for both the players and individual teams with win percentages n stuff....the last thing I'm working on is calculating each players best possible finish given the current points and remaining fixtures..I got it working for player 2 in the 3 player version but it was a ball ache and not really scaleable but i think it makes an interesting problem to solve hence why Im posting

      if anyone wants the somewhat messy spreadsheet email me (smegfish@hotmail.com)

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      Diane,

      Can you contact me with how I could help.

      admin@computergaga.com

    • profile image

      Jake 5 years ago

      This is a great tool you've created. So far to add teams I've had to add a team sheet, as well as edit the fixture and league table. Is there a quicker way to go about this? Also how would it handle blanks or possibly zeroes?

      Thanks

      carjac04@gmail.com

    • profile image

      amos jr. 5 years ago

      Its nice!!!

    • profile image

      Diane 5 years ago

      Hi Al - I am trying to set up a school boy football league for 10 teams here in Tanzania - I do not know how to work out the fixture list please can you help - many thanks in advance - Diane

    • profile image

      chutneye 5 years ago

      wow al that is amazing! can u send me a 1 wiv 10 teams so that i can save it! wish i knew how to do that man :)

    • profile image

      chutneye 5 years ago

      Al is it easy to add extra teams so say 10 in a league? if u cud send a sheet or an e-mail that wud b much appreciated! address is chutneyrodriguez@gmail.com! Thanks

    • profile image

      Sharansr 5 years ago

      Hi,

      Could you send me the updated excel sheet that can handle blanks or 0's before a season starts?

      Thanks

      sharansr@yahoo.com

    • profile image

      liam 5 years ago

      this is who is going to be in the leage

      liverpool

      wovls

      fullham

      westbrom

      blackpool

      westham

      bolton

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      Please download the spreadsheet again. The team tabs will now update when results are entered onto the fixtures sheet.

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      @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.

    • profile image

      Andy 5 years ago

      Can you make a multi league system with promotion/relegation at the end of the season? This will move teams automatically between leagues. TY

    • profile image

      George 5 years ago

      Is the data worksheet missing, I also have nothing populated in the teams worksheets. GeorgeJack1973@gmail.com

    • profile image

      MickB 5 years ago

      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

    • profile image

      Deano 5 years ago

      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

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      Hi Laura,

      I have sent you an email. Hope it helps.

    • profile image

      Laura 5 years ago

      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!

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      I have sent you an email Gary

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      @McLau i have sent you an email

    • profile image

      Gary Barker 5 years ago

      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

    • profile image

      McLau 5 years ago

      @ Almurray, please send me the same e-mail you sent to heidi..

      e-mail: saborangolano@gmail.com

    • almurray profile image
      Author

      Alan Murray 5 years ago from Ipswich, United Kingdom

      Dan and Heidi,

      I have sent you both an emailto explain further.

      Thanks for commenting.

    • profile image

      Heidi 5 years ago

      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

    • profile image

      Dan Mortimer 5 years ago

      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

    • profile image

      GONA GOVENDER 5 years ago

      i WOULD LIKE TO SEE AN EXCEL VERSION ON HOW TO DO FIXTURES

    • profile image

      serk 5 years ago

      thanks i used to wright all my fixtures and tables which takes 4 ever but thanks to u its changed

    • almurray profile image
      Author

      Alan Murray 6 years ago from Ipswich, United Kingdom

      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.

    • profile image

      Lev1948 6 years ago

      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

    • profile image

      Big Bright 7 years ago

      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.

    • almurray profile image
      Author

      Alan Murray 7 years ago from Ipswich, United Kingdom

      Thanks Big Bright.

      Any problems, give me a shout.

    • profile image

      Big Bright 7 years ago

      Hard to understand this but will surely pull through. Thanks for the education.

    • profile image

      Shaun 7 years ago

      I never understood the goal difference =S