Create an Excel Football League Table

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

More by this Author

  • Create an Interactive Excel Chart using Option Buttons
    6

    Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...

  • Simulate a Cup Draw using Excel
    0

    Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...

  • Brock Lesnar Nutrition and Workouts
    2

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...


51 comments

Shaun 6 years ago

I never understood the goal difference =S


Big Bright 6 years ago

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


almurray profile image

almurray 6 years ago from Ipswich, United Kingdom Author

Thanks Big Bright.

Any problems, give me a shout.


Big Bright 6 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.


Lev1948 5 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


almurray profile image

almurray 5 years ago from Ipswich, United Kingdom Author

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.


serk 4 years ago

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


GONA GOVENDER 4 years ago

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


Dan Mortimer 4 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


Heidi 4 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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

Dan and Heidi,

I have sent you both an emailto explain further.

Thanks for commenting.


McLau 4 years ago

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

e-mail: saborangolano@gmail.com


Gary Barker 4 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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

@McLau i have sent you an email


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

I have sent you an email Gary


Laura 4 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

almurray 4 years ago from Ipswich, United Kingdom Author

Hi Laura,

I have sent you an email. Hope it helps.


Deano 4 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


MickB 4 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


George 4 years ago

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


Andy 4 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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

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


liam 4 years ago

this is who is going to be in the leage

liverpool

wovls

fullham

westbrom

blackpool

westham

bolton


Sharansr 4 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


chutneye 4 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


chutneye 4 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 :)


Diane 4 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


amos jr. 4 years ago

Its nice!!!


Jake 4 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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

Diane,

Can you contact me with how I could help.

admin@computergaga.com


smegfish 4 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)


Abdulkadir 4 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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

I have emailed you Abdulkadir


Matt 4 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


Chris Baldwin 4 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


Andy Eyres 4 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


almurray profile image

almurray 4 years ago from Ipswich, United Kingdom Author

Hi Andy,

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


Nigel James 4 years ago

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


Phil 4 years ago

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

Cheers,

Phil


Phil 4 years ago

ps phil_turbitt@hotmail.com


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


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!


DB 3 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


CHRISO 3 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


Hussein Fakharany 2 years ago

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


Jerome 2 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


Kent 2 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


Gary Banks 2 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


almurray profile image

almurray 2 years ago from Ipswich, United Kingdom Author

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


Sean 14 months ago

Hi I'm try

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working