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

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

## 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

## Popular

3

3

• ### How to create and configure a bubble chart template in Excel 2007 and Excel 2010

1

0 of 8192 characters used

• Sean

3 years ago

Hi I'm try

• AUTHOR

Alan Murray

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

• Gary Banks

4 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

• Kent

4 years ago

Hi Almurray

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

• Jerome

4 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

• Hussein Fakharany

4 years ago

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

• CHRISO

5 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

• DB

5 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

• Sandeep Chakroborty

5 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

• salmansalha

5 years ago

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

• 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

• Nigel James

6 years ago

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

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

Hi Andy,

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

• Andy Eyres

6 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

• Chris Baldwin

6 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

• Matt

6 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

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

6 years ago

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

• smegfish

6 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)

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

Diane,

Can you contact me with how I could help.

• Jake

6 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

• amos jr.

6 years ago

Its nice!!!

• Diane

6 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

• chutneye

6 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 :)

• chutneye

6 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

• Sharansr

6 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

• liam

6 years ago

this is who is going to be in the leage

liverpool

wovls

fullham

westbrom

blackpool

westham

bolton

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

• AUTHOR

Alan Murray

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

• Andy

6 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

• George

6 years ago

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

• MickB

6 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

• Deano

6 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

Dean

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

Hi Laura,

I have sent you an email. Hope it helps.

• Laura

6 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!

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

I have sent you an email Gary

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

@McLau i have sent you an email

• Gary Barker

6 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

• McLau

6 years ago

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

e-mail: saborangolano@gmail.com

• AUTHOR

Alan Murray

6 years ago from Ipswich, United Kingdom

Dan and Heidi,

I have sent you both an emailto explain further.

Thanks for commenting.

• Heidi

6 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

• Dan Mortimer

6 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

• GONA GOVENDER

6 years ago

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

• serk

6 years ago

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

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

• 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

• Big Bright

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

• AUTHOR

Alan Murray

8 years ago from Ipswich, United Kingdom

Thanks Big Bright.

Any problems, give me a shout.

• Big Bright

8 years ago

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

• Shaun

8 years ago

I never understood the goal difference =S

working