Due to popular demand, I have created an Excel fixture and league table generator.
Following on from my fixture list creator and league table generator files, I was asked to create a combined file to create a fixture list and also generate a league table from results entered.
Download the Excel fixtures and league table generator.
To use the file;
- Enter the teams competing and how many times they will play each other on the “Teams” sheet
- Enter the results from fixtures on the “fixtures” sheet
- View the league table on the “League table” sheet
Not able to run the macros as they are password protected
Sorry to hear that Steve. I just tested it and they work fine. Might be worth checking your macro security level.
I see you have a league generator I downloaded but it won’t work for my leagues they are billiards leagues with multiple teams at the same locations with multiple tables . Was wondering if you would have something that would work for me? Thank you for your time!
Hi Ken, it sounds like the league tables should be no problem. However the fixture generator will not consider the same location being used.
Hi,
How to edit that league table so, that a win gives you 2 pts, draw 1 pts and a loss 0 points?
There is a Data tab you can unhide (Right mouse click a sheet tab and select Unhide). The number of points are here to be changed.
where can i put results
i want to play teams onlu once in a league….want table not to show games like home and away
Hi Majid,
I have a course which shows you exactly how this is all done – Create automated sports league tables and tournaments in ExcelAlan
when i put scores in fixtures sheet it updates league table automatically but sometimes it duplicates team names and miss some teams…solution kindly
Unhide the Calculations sheet. In the first column named Rank Ties, double check it includes the full range of teams names. The formula below is what it should look like, ensure the emboldened part is the last row of the calculations table.
=IF(Teams!A2=””,””,RANK($B3,$B$3:$B$22,1)+COUNTIF($B$3:$B3,$B3)-1)
How can change the table to 11 teams? And the fixtures button doesn’t appear when I put the new teams
Hi ,
I downloaded your league table excel and ran it with a 10 team soccer league. Each team plays each other twice(once home,once away). The error that i’m facing is that home and away games are not equally divided. Some teams play 10 home games and 8 away games, while some play 12 home games and 6 away games.
how do i fix this error ?
I just tried this, unfortunately it doesnt do home and away. Had one team with 2 home games and 8 away games.
Everything else works though
Yes, the fixture list is not quite there. Sorry, with good fixture, everything else works perfect.
Hi same problem. Program runs well 9 teams. One team has 8 home games!. is there a simple solution that can be added so all teams play home and away weekly please. Andy
Hi
I created it all and got it working but now when I go in the file it comes up with an error loading it, it says the extension isn’t valid or the file is damaged, this has happened twice I have also another copy that also says the same, any thing I’m doing wrong?
I’m not sure what is happening here. Sounds like it got corrupted. The downloadable file is fine.
Hi there, this is not working for 2020/21 fixtures.
Hi Albert, this fixtures sheet is very old and unfortunately is not perfect.
I have downloaded your program which I had hoped to use it to run a Bowls League. I need to alter the points awarded for win loose & draw to 2, 0,1. But I get a request for the password. I am a complete novice with Excel and would welcome your help with this.
Kind regards
Paul
Hi Alan,
Great idea but it does require a password. Regards
Great spreadsheet, though some field are protected. It’d be great if you could re-upload or share the password 😀
Great job mate
The spreadsheet should be fully usable.
The program is just what I need, with the following exception: on loading file I can only see sheets Teams; Fixtures;League Table. How do unhide other sheets to enable me to change points for win and points for draw
From Excel 2007 you can right mouse click on a sheet tab, select Unhide, select the sheet you wish to unhide and click Ok.
In prior versions you would need to click the Format menu, select Sheet and then unhide.
It’s not for me, judging from other comments I’m not the only one having problems. I’m just needing a league table generator for 25/26 teams 🙂
Hi Cal,
I have tried editing it and re uploaded. You should be able to add teams and create a new fixture list and table without any problems.
It is a bit slow and I am hoping soon to have the time to improve it.
Watch this space.
Alan
HI, HOW DO I CHANGE THE NUMBER OF TIMES TEAMS PLAY EACH OTHER FROM 2 TO 4, TRIED TO DO IT BUT IT SAYS A USER HAS RESTRICTED THE NUMBER OF FIXTURES ALLOWED.
Yes it can only handle playing each other once or twice, it would need recoding. Unless you need something specific, I would recommend doing it for playing each other twice and then copy those fixtures to get 4 times.
Hi, is there a way to use this league table, just having the team names, 1 win column 1 draw column with a points total which puts the highest point earner in 1st position without the fixtures list etc?
Hey,
It’s working now! Thanks a lot Alan!
Cal
Nice!
But using 4 teams, where the teams play each other once, one game is left out in the first week.
IP
I also have this
Great Excel Fixtures and League Table Generator but how do you change the points system in the League Table for win, draw and loss?
Cheers
Would you be able to unlock the the Calculations Sheet so i could change the points structures for a league i am creating!?
Cheers
The workbook and sheets have been unlocked. A formulas and code are visible.
Sorry but do you have the file in excel of 2003 ? Would appreciate if you kindly have it Thanks
Trying to get this to work if i exampel take (Man U 4-2 Man C) man u is on the top with four point and Man c have 2 points.
Can you help or?
i speak some bad english but i will be glad if you could help me!
Hi
I’m looking for exactly this but using 3 times playing each other quite a few times. Just a few mates on FIFA basically. Was wondering how to alter this to make that possible.
Cheers
Great spreadsheet. I was looking for something like this and this is an ideal baseplate from which I can expand to what I need.
Many thanks for sharing it so openly
Thanks Phil, thats great!
The teams are not home and away in alternate weeks, can this be corrected??
Can you get this so it works on an iPhone? Thanks
This is just what I have been looking for but there are a few problems:
i – When you use 4 teams one of the fixtures is missing in week 1 and also week 4.
ii – When playing each other twice it just lists the same fixtures twice, i.e. Team A plays Team B at home, and in what should be the reverse fixture Team A plays Team B at home again. In a league of 18 teams I created, one team was scheduled to play away in every match.
iii – The points column does not calculate points, but instead lists the total of goals scored.
The solutions required or which I used:
i – This didn’t occur in my league of 18 teams. From what I can see, it only happens when 4 teams are used.
ii – All the even numbered fixtures require reversing. To overcome it I manually cut and paste all the fixtures to change the home and away teams.
iii – I just changed the points column to create a simple calculation of W*3 + D. In the case of position 1 this would be, =((D3+I3)*3)+(F3+K3). Then just copy/drag this calculation to each of the other positions.
I hope this helps and I look forward to an update.
Hi Alan. I want to thank you for this easy and perfect excel layout. This is the best design for a purpose like this. Thanks million times.
Now I want to share my point, I have been using Excel 2007 and my macro setting is “Enablle All macro” which means I do not have security issue and I am able to run the macros in the excel file but the time consumption is scary. If I enter a score for only 1 team into the “Fixtures” sheet, excel starts to process it approximately 25 seconds which is crazy because my PC is really good (Core Duo 2.8 GHz, 4 GB Ram). Is that normal? Thanks again.
Unfortunately it is normal. I need to create one using VBA instead of formulas. Just never get the time to sit down and work through it.
Hi, where do i enter game results and how do i generate the log based on game results including scores
There is a Fixtures sheet. You can enter the results in columns B and D of that sheet.
this is heaven! i’ve been giving myself a headache for few days now trying to figure out how to do this! thank you very much!
Could you send me the new updated database to my email
email:[email protected]
cannot seem to get any relationship between the fixture and league table info??…..This spreadsheet is the perfect answer to providing the results for a mini league tournament we are trying to organise next week if I can understand the issues with the link. Would really appreciate any assistance to overcome the problem
Cheers Julian
I have sent you an email to discuss further.
Excellent template Alan. Hopefully will make my life a lot easier as a cricket fixture organiser.
In the league I have created a win equals 10 points, a tie/abandonment of a game equals 5 points and a loss 0 points. Are you able to help change the template as I have a very basic knowledge of excel. Any help will be appreciated.
Many thanks,
Andy
Hi Andrew,
I have dropped you an email.
Fantastic!! But I have a major problem that I cant seem to get sorted? Every time I generate a fixture list, there is at least 1 team that plays away every single week. Is there a way of sorting that out at all? I have a league of 8 teams who need to play each other 2 times in the season. Secondly, we only get 2 points per win and not 3 🙁 Is that easily adjusted too??
Many Thanks
Carl
Hi Carl,
The points for a win can be adjusted by unhiding the Data sheet and changing the required cell.
The constant away team is a known problem that needs to be rectified.
This is exactly what I have been looking for!
Just wondered, if I wanted to add a league table of individual players wins that contribute to the whole team (its a pool league, each meeting results in 9 matches, 6 singles and 3 doubles, best of 9)
sounds confusing, and my excel skills arent great.
Would appreciate any help.
Regards
Carl
Hi Carl,
Sounds good. Should be ok to do. On first thoughts I think I would add a column for each individuals name next to the team name in the fixture list and then it can be added in the same way in the current league table.
You could also then have a league table for individuals too if you wanted. Similar to Formula 1 with drivers and manufacturers.
I should have league tables for different sports coming over the next few months. I’ll make sure to include pool.
That would be fantastic. Thanks again – please email me with any updates!
I have been using your spreadsheet (well some of it) for a while now, but can not for the life of me work out how to change the points for wins etc to what I require. In netball we are awarded the following: win=5, draw=3, loss=0 and loss, but scored half=1
Please, please help
unhide the sheet that’s called ‘data’ and change the points allocated there
Hi,
I am keen to know how to handle “loss=0 and loss, but scored half=1”.
thanks
I have emailed you Debs.
Hi
Thank you for providing this spreadsheet for public access, it’s invaluable to community sports teams!
Could you please also let me know how to add a point for the losing team scoring over half of the winning score?
Many thanks
Hi Steph, This will need to be added to the sheet with the points and also the criteria added to the calculations sheet. I have a course which shows how this is created so that people can create their own versions and modify mine – http://bit.ly/2Siivkm
I would be keen to know how to use this spreadsheet to add half score = 1point as well. I’ve managed to unhide and adjust the win/loss points as required for my league.
I know this is quite old so would really appricate help if you are still supporting this.
Hi, yes still supporting this. I have a course which shows how these are set up – http://bit.ly/2Siivkm
Thanks mate, great macro you have here, really nice tool!
Respect
Thanks Sabs. I appreciate your comments.
thanks a lot mate! loved the excel file. you’re great 🙂
Hi, great spread sheet!!!!
Is there any way I can adapt it so that each team (or individual in this case) gets the number of points scored in a match as their league points with NO win bonus rather than the default 3 points for a win.
In my league, players play 5 frames of pool and if the score is 4-1 that’s the points they receive in the league. Any help would be great, im useless at spreadsheets!! Thanks, Ryan
Hi Ryan,
Would the loser pick up the 1 point or get 0?
If they also receive what they scored we can just change the reference on the calculations sheet to look at the scores and not the points for win. If not we would need to look at creating a helper column for referencing.
Alan
Hi,
I’m running a mini f/ball tournament: 6 teams.
Straight one off matches (no home & away).
Pts: 2 for a win- 1 for a draw – 0 for a loss
How do I edit your excel fixture list & table doc?
Best regards, les
Hi Les,
Please edit the Data sheet to receive 2 points for a win instead of the default 3.
The number of teams will need to be edited to 6 and the formulas possibly edited to maintain the correct ranges.
Man I believe you have saved so many lives reading the comments above. I wonder if you can help me with one issue. On our game day we have two rinks with 6-7 games on each. So 12-14 games per week. But on the fixture sheet it generate only 4 games per week. Is there a way to adjust how many games will be generated per week? Thanks
Hi Ondrej,
I’m afraid that functionality is not possible in that fixture generator.
Alan
Great spread sheet and macro but could you please explain how the macro works
Nice one for this! Just what I needed for my 5’s soccer league…much appreciated . Chris
Looks great, however my league table isnt working propely and when i put in the scores ‘Norwich’ comes up twice on the league table and after a while the table comes up with a load of “N/A” on, any idea what to do?
I’m not sure why Robert. Without seeing the file its difficult to explain what happened.
Formulas have been included to prevent the duplication of team names in the league table and to stop the #N/A appearing.
The only thing I can imagine is that a name has been entered differently on the team sheet as to the fixture list. Maybe a space has been entered after Norwich. Therefore it looks the same but is picked up as a different team.
Do you have one which can work for a snooker league. The fixtures side is great just the league table to change if possible??
Hi Adam,
I don’t have a snooker one I’m afraid. If you need some created please follow the link below.
Create a sports league table generator in Excel
Alan
Thank you very much. This has helped a great deal.
Thanks so much for sharing! I’ve started using your worksheet, but am stuck. We have a youth league we are trying to organize where the number of games played depends on our season timeline (12 weeks long, one game per week…not an even number where each team can play every team twice).
Is there a way to add extra weeks to the Fixtures after doing the standard 6 teams playing each other twice? In other words is there a way to add extra weeks? Or could you allow us to set it up where every team plays 3 times, and then I could delete any unnecessary games?
Hi Matt,
No, the spreadsheet does not support this. One day I might look at improving it.
Until then you could generate some fixtures (say the home and away fixtures), and then copy some weeks to add to it. Therefore reversing your idea of generating more and deleting some.
Thanks! I’ll copy and paste then. Really appreciate you sharing this.
Great Job!
Im using excel everyday for hours but I didnt see something like this before. Really great Job, appreciated your hard work!
greetings from Turkey.
Great work Alan, thanks!
I’m actually looking for a cricket league spreadsheet that would calculate like this one, but with a Net Run Rate calculated. Any ideas?
Hi Nigel,
I can set this up for you. Please use the link below to go ahead.
http://www.fivesquids.co.uk/Other/28413/Create-a-Sports-League-Table-Generator-in-Excel
Hey, i change name teams, and then i press the button “Create fixtures” and nothing happens, what is the problem? Thanks.
Maybe macros are not enabled. Try closing and re-opening. When asked the security question click enable macros.
Hi , i have found your fixture generator and find it really easy to use.
my question is how can this be modified so i can run 3 groups /leagues all in one document.
example i have a 15 team tournament coming up so i will be using 3 groups of 5 all playing each other once and at the same time i need to collect the data in a table.
any help would be much appreciated, if you can contact me on [email protected]
thank you.
Hello. Loike Robert in 2013, my league table isnt working propely. When I put in a couple of scores to try the programme out, a team will come up twice or the whole table will come up with “N/A” on. Any idea what to do? – See more at: I believe I have followed all instructions.
Thanks.
http://www.computergaga.com/blog/excel-fixtures-and-league-table-generator/#comments
Hi,
Great tool, thanks for sharing…
Would it be possible to change the points allocated for a 3-0 victory?
Regards, Pheobe.
Hello Alan,
I have downloaded your Excel file and tested it with a league of 16 teams with each team playing two matched with every other team. However the distribution chart for home and away matched is uneven for every team.
Each team is suppose to play 1 match at home and 1 match away with every other team thus 15 home matched and 15 away. However the sheet has produced uneven combination of home and away matches. Can you fix this issue.
Regards
The combination should be fine. It does not always sort them correctly so may need some manual jigging. That is something I need to look at. However you should get the correct number of home and away matches.
Hi big guy thank you for everything you have work into this program. Can you help me cause work out fixtures for Basketball games. We don’t have draws and we have forfeits though. Could you assist with giving points allocation for wins, lose, forfeit. Also can you calculate to play 3 times against each other.
Thanks
Z
The fixture list can only do playing each other twice. I would copy the first lot of fixtures for the 3rd set.
Forfeits can be added to the spreadsheet. The Data tab contains the points awarded for wins and forfeits. Will need to tweak a formula or two on the calculations sheet to work with forfeits depending on how you record them.
Hi is this safe to down load, just not use to down loading things
Absolutely Graeme.
Hi, Trying to adapt this to use as a dart league.
Problem is points are awarded for legs won rather than games won and drawn.
Is this something that is possible to do?
Ideally I would Like Legs
Played Won Lost Drawn For Against Points(=legs for)
Thanks in advance
Tom
hi there just foud this and its exactly what i need but….
we have 3 divisions
div 1 has 10 teams say
div 2 has 9 teams
div 3 has 8 teams ..
but thats not all in division one there is a pub with 3 teams and another in the second playing of 2 tables so two teams have to be at home and two away ..is it posable to create an excel sheet for this?????????
Sorry Paul this is not something I have done. I am sure it would be possible to include this testing into it, but seems tough. May just need to be a manual change after for simplicity.
Hi Alan
Great spread sheet. I know how to change the points for a win and a draw but is there any way I can add in points for a loss?
Regards, Tony
Yes Tony. It will need to be added to the data sheet and then on the Calculations sheet. Then the points column on Calculations you will need to add to the existing formula to include the points for losses.
Hi Alan
Thanks for this. The spread sheet is password protected or locked so I can’t add or amend any of the details or formulae. Can you help me on this?
Thanks
Tony
The spreadsheet is fully usable. Some sheets are hidden and may need unhiding. There is no password.
Hi, excellent spread sheet you have created, I was wondering if by any chance you have created a similar spread sheet for a knockout cup that updates on its own when the scores are entered?
I am in the process of creating an online course demonstrating how to create sports league tables and knockout cup competitions of different formats. Keep an eye out as it is coming soon.
We are trying to develop and create a more readable and editable 6-week league fixture schedule. We have all of our fixture templates for our individual divisions (depending on the number of teams per division) and have online registration on our website. Would love to generate a excel spreadsheet to manage the fixture schedule to better help with conflicts with fields and times and generate a nice table display that would be searchable for the player
Looks a great little Generator, I’m in the middle of setting it up for my Darts League, one small glitch, I need the Fixtures to come out as Home and Away Basis alternately, but i’ve ended up with One Team with five away Games, and another with Five Home Games, is there away rounds this, thanks Julez
I am aware that the fixtures sometimes come out in the incorrect order, however there should be a unique set of fixtures. They just need some manual arrangement. I’m hoping to get around to fixing this at some point.
Have you managed to get a workaround yet to try and get more of a Home one week away the next scenario?
Thanks
Kev.
No 🙁
Hi!
How’re you? Firstly let me thank-you for producing this and letting us use it – I really like it.
However, I have a slight problem…when I wish to use 28 teams, the league table and fixtures do not seem to communicate properly. It often comes up with an error.
Can you help at all?
Thanks 🙂
Thanks for your comments Ian.
I’d like to help but I would need to see the spreadsheet and the errors you are getting.
While trying to change the numbers of teams, It discovered
“Run time error 13
type mismatch”
Hope to here from you. Thank you Alan.
Regards!
I,m using Excel 2007.
Thank you
IT IS WORKING FINE!!!
GOD BLESS YOU. AMEN.
Is there a way to incorporate more than 2 games per team? The number doesnt go beyond 2!!
This spreadsheet only handles teams playing each other twice a season.
Hi, I have used it to create a head to head league for my fantasy football league this season. I have 8 teams so we need to play each other quite a few times over the season (38 match days). So as above I copied and pasted extra fixtures to make it up to 38. Works perfectly. Thanks so much for this fantastic fixture generator.
Thanks Paul O. Appreciate your comments.
Hi
Is there a way whereby I can only see 1 league table without the home and away. I tried to change some of the calculations but not able to change that. I just need
P W D L F A PTS
On top of that I would like to ask whether is it possible to award extra points for penalty shoot out…maybe 2 points for a win
Absolutely. Would need to make changes to the fixtures sheet to accomodate the penalty shoot out result and the league tables.
I should have an online course coming out within the week to learn how to adapt these league systems to how you want.
i have used this spreadsheet and i like it i just have a couple of issues if you could help me with them 1 i selected 30 teams but the table only shows 29
and 2 the fixture generator button doesnt do anything please can you help address these issues
If the button does not work it sounds like the macros need to be eabled when the file is opened or from your Macro Security settings. As for the table, it may need expanding. Look for the bottom right corner of the table and try dragging it down one row. Check the formulas for previous row they may need copying down a row.
=IF($Calculations.$C31=””;””;VLOOKUP(SMALL($Calculations.$A$3:$A$31;A31);$Calculations.$A$3:$C$31;3;0)) this is the formula i am having problems with it should be for A31 on the teams page and the table page is should be B32 but it just loads the previous team again can you help?
This is a great spread sheet and is great to use to create the fixtures. I am a excel beginner and require your assistance to change the log generator to suit rugby results, like tries scored for, against, points difference bonus points etc. Like a proper rugby log. can you perhaps assist.
Hi Jessie,
The link below is for our online course in how to create sports league tables and tournaments. A rugby example inc bonus points for tries etc is included on the course.
Create sports league tables and tournaments in Excel
Alan
Hi, I am trying to do a world cup league where the 40 teams which competed in the last 2 world cups in Brazil and South Africa compete.
I would love to know how you can expand the spreadsheet to 40 teams as I can generate the fixtures for 40 teams but the table does not work past the 29th team.
Any help would be greatly appreciated.
To include the other teams tou will need to expand each table (league table, calculations table, maybe teams aswell) and if you are still having problem check the ranges of the formulas esp the first 2 columns of Calculations.
Any problems sign up for my course on creating these league tables and tourna ments in Excel.
Create sports league tables and tournaments in Excel
its not doing reverse fixtures after the half way point of the league, just a repeat of the 1st set
ive solved it myself. by unhiding the home and away tabs, i could see that they were both the same. After reversing the order on the away tab it is now showing the fixtures correctly. Totally guess the solution but it work by just looking at what you had done
its now not working and it keeps restting the figures as they were before
I’m not sure how it worked reset the figures. Tey are generated by a macro, not a formula. Unless the button that triggers the button is pressed again the figures on the home and away tabs should not change themselves.
Great spreadsheet and thanks for the effort.
I am trying to convert it for another sport and had a couple questions:
1. Teams can end up playing each other more than 2 times, what would be the best way to modify this?
2. Don’t need to show Home wins and Away wins just overall wins and losses (Played, Wins, Losses, Goals Against, Goals For, Points, %) . What would be the best way to modify this?
Thanks again for your effort.
Hi Rob,
For playing multiple times, I have not covered this in the macro. My intention was that once you have home and away versions created you can just copy them to get the multiples.
For the overall and not seperate home and away wins. It needs modifying on the Calculations sheet. The formulas will need changing to remove the criteria that specifies home wins and away wins.
If you are interested more on how to create these sports tables, check out my course using the link below.
Create sports league tables in Excel
Alan
Hi Alan, i want to create a league of 4 teams, which they play each other twice. when i generate the fixtures it gives me 10 matches in total which is wrong. should give me 12 matches. do you know what is wrong?
Hi Saroop,
I see that. I’m not sure whats wrong. It needs looking into.
it’s good…easy to use…tq
I am trying to modify the number of players to 19 , but it seems to throw everthing out – can you help?
Good Morning Alan,
I have tried to create a league table for this years Premier League teams, I copied and pasted this years teams over the original list and clicked create fixtures, I then copied and pasted the week 1 fixtures over the week 1 fixtures created and when I put the first result Bournmouth 0-1 Aston villa in the League table Bournbouth now appears twice 18th and 19th. West Brom have dissapeared, any thoughts as to why this is happening.
Regards Paul
It’s hard to say without seeing the file Paul. Does this still happen when all of the results of the week are entered?
Alan,
I decided to try again from scratch, I downloaded the file, I double checked that the macro setting was enabled and tried again, I copied this seasons teams over the previous list then created the fixtures, this time instead of copying and pasting the week one fixtures over the list created I looked for the individual matches, I looked for Bournmouth v Villa but it was not listed, I then went through the Bournmouth home fixtures and the list is wrong, I have copied the home fixtures below for you to see
Bournemouth v Swansea City
Bournemouth v Everton
Bournemouth v Manchester United
Bournemouth v Sunderland
Bournemouth v West Bromwich Albion
Bournemouth v Southampton
Bournemouth v Crystal Palace
Bournemouth v Chelsea
Bournemouth v Watford
Bournemouth v Swansea City
Bournemouth v Everton
Bournemouth v Manchester United
Bournemouth v Sunderland
Bournemouth v West Bromwich Albion
Bournemouth v Southampton
Bournemouth v Crystal Palace
Bournemouth v Chelsea
Bournemouth v Watford
Hi,
I have had the same problem as Paul, when I change the teams click the create fixtures button and put in the scores i look at the table and their are errors for most of the teams.
Please help
Thanks Jack
I can’t explain the errors without seeing them. It should work fine. It is all driven by the Calculations sheet. Check it out.
Alan,
Just as a test I used the teams in the download and noticed the fixtures were wrong, for example Arsenal were down to play 4 away games and all the rest were home games.
Regards Paul
Alan,
As a test I downloaded the spreadsheet again and without making any changes I input the score Swansea 1-0 Bolton and exactly the same as I described earlier happens, Bolton are shown as 18th and 19th in the table and Wigan (19th) is the team no longer shown, I then went to Wigan and input a result for their game with Man U as a 1-0 win for Man U and this time Wolves (20th) is the team no longer shown, does this help pinpoint where the problem lies
Hi,
Great spreadsheet.
I am trying to change the points for a netball league where you score points for loosing. I see a comment earlier about amending the Data Sheet which I have done but I am not sure how to implement that into the calculations sheet. Can you advise one line and I should be able to replicate it into the others, if a losing point was cell B4 in the datasheet
HI Tim,
Changes would need to be made only to the calculation of points in the Home and Away sections. Have a look at the formula in each where it currently multiplies the number of draws by point son the Data sheet and number of wins by points on the Data sheet.
Try and copy this for B4 on Data for your number of losses. For a better understanding I offer an online course on setting up sports league tables in Excel to explain things thoroughly. A link below will offer a 50% discount getting the course for just $19.
https://www.udemy.com/excel-league-tables-and-tournaments/?couponCode=Sports19
I hope you solve the issue.
Alan
Hi down loaded your demo sheet and inputted 2 weeks of scores but when I checked the league table sheet it shows Manchester utd in 17 and 18 place and West Bromwich Albion are missing I have double checked my inputs and they are all filled out correct so there seams to be a mistake some where within the program
Hi Alan
This spread sheet is a work of art but it fails after week 2 if I input scores for week 1 and 2 and then go to the league table it shows one team in there twice and another team has been removed so when you go to input data for week 3 you get #N/A in every cell on the league table. hope you can help
Alan,
I have to say this is excellent – hats off to you buddy!!!
I have 2 problems that I hope you can help me with
1) Is it possible to create a fixture list where the teams play 1 week at home (or away) and the following week away (or home)
2) Instead of week 1 fixtures can this be changed to actual dates?
Thanks again for this – you saved many hours of headache
Regards
Sanjay
Hi Sajay,
Thank you for your comments. Yes both are possible. The first point would be possible but difficult to ensure that every team played home and away alternate. Because the fixtures are completely random we may need to change the way that the calculation worked.
The second point is much easier. In the VBA code we would just change the string for “Week x”. We can enter a little loop to calculate the date. Either loop through a list of dates entered on a worksheet, or calculate them in the macro. So if you wanted weeks we could have a loop that added 7 days to the date each time it looped.
This is simply fantastic! Many thanks for sharing it! 🙂
Will try to figure out how it works.
Hi!
Great spreadsheet! tried to read all the comments but couldn’t find the answer I was seeking.
Any solution yet to the “constant away team”-problem?
The home and away league table won’t fill its function if the teams don’t play each other the same amount of times home and away:/
greetings from Sweden
Hi,
what can I do to be able to create fixture with 3 teams?
(Great sheet btw. I really appreciate the sharing)
Regards,
Arild
Hi!
Great Sheet,
But I only need 10 teams, and when changing to 10 teams I get the following message: Run Time Error 13, Type Mismatch…
And I do not know how to debug it…
Please help
Hard to say. The error indicates an incorrect dat type/format. Such as entering a number when text is required, or text where a number is needed etc. Cannot really say without code. Good luck hunting.
Hi there,
Great table, Is there anyway to change the cell which indicates number of times you play each other ? I would like that to be 3 times.
the fixture is great but when you put results on 20 team fixture the table does not come out. what do i do
Brilliant sheet, looking to use it for a pool league, only problem is some pubs have 2 teams, so both cannot be at home on the same dates anyway around this?
There is nothing built into the spreadsheet to accomodate this.
So how do I generate the fixtures?
Click the button. Make sure macros are enabled on opening the file.
Wait, this works ONLY on Excel (and no work on numbers, Excel Online)?
I do not use Numbers so could not comment. This is set up in Excel though, yes.
I want to use this for a darts singles league but need a formula for 2 points for a win 3-1
3 points for a win 4-0
1 point for a draw 2-2
all 4 legs to be played, can you help please
20 players play each other twice
Hi Andy,
This would be extra work to accommadate the two different points systems for a win. I will reply to you through your e-mail.
Alan
I am trying to use this, with 19 team. When i add some scores in one of the teams becomes duplicated in the league table. i have tried downloading again and reentering the team names but get the same result.
I have almost the same issue, I have a 19 team league and when I enter the scores on the last team (alpha order), either duplicates a random team or I get #NA on the Table. But this only happens when I put the scores on the last team. All other scores and teams reflect ok on the Table. Just the last team is displaying a duplicate or #NA
Thank you for the spreadsheet it is really helpful, just need to know if this can be fix.
Hard to explain why without seeing the possible issue. I don’t expect this behaviour. The problem can probably be identified by viewing the Calculations sheet.
^^^^ following on from the above comment. I get the same behaviour with the 20 pre-loaded football teams. What ever team is in the 1st fixture away league becomes duplicated in the league. any ideas?
Once all results are entered, no teams should be duplicated.
Hi, where do I enter the results in the fixtures page? No matter where I enter, the League Table does not seem to change.
Enter the results into the boxes provided. It will change automatically.
Check the Formulas tab and Calculations Options button to ensure that formulas are set to update automatically.
Hi, I added 8 teams but when I generate the fixture only 6 of them actually show on each week instead of 8.
So basicaly I have 3 games per week instead of 4. Why is this happening on my file? Any suggestions? Either way the file is really useful thanks for the upload!
Thanks Sebastian,
I am aware that in some instances the fixture list fails to generate them perfectly. However I’m not familiar with your case. Usually all the fixtures are there but sometimes some appear in different weeks and need some shifting.
Hi, it worked well for me, it was easy to use the problem i have is reputation of teams on home/away some of the teams appear only playing away how do i get to make them to appear like in week 1 they are home and in week 2 they are away,
Sometimes it does not correctly put the games home and away on alternate weeks. The fixtures however are all there and correct. They will need to be moved manually.
Hi Alan,
Just wanted to thank you. Great work!!
Hi Allan,
I am trying do use this table for a field hockey tournament. Its going to be for 2 days, with 2 pools, each pool has 4 team and they play each other 3 times. Can you share with me how I can make this table functional for the tournament.
There is alot to explain. If you have any specific queries I am happy to help.
You can also sign up for my online course explaining exactly how to set up this league tables by following the link below.
https://www.udemy.com/excel-league-tables-and-tournaments/?couponCode=SportsLeagueComputergaga
Hello Allan!
Thanks a lot for sharing. Unfortunately, as for me, it doesn’t work. The fixtures is ok but the league table isn’t. When I type the results two team names always duplicated and the table became incomprehensible due to the duplication. I tried to copy and paste all the results of a week but no avail. Could you give me any advice?
Sorry Eguzki, When all results are in this should not happen.
Hi!
just tried it with the 20 teams in the download. The same team appears as the away team every week for the 1st match each week through the whole season, never plays at home!
Solved previous report. Change VBA in Away fixture generation to…
‘Search across columns
For intCol = 2 To intnumteams
‘Go down each row looking for the gameweek
For introw = 2 To intnumteams
If Worksheets(“Away”).Cells(introw, intCol).Value = intgameweek – intgames / 2 Then
‘strFixture = Worksheets(“Away”).Cells(intCol, 1).Value _
‘& ” v ” & Worksheets(“Away”).Cells(1, introw).Value
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Worksheets(“Away”).Cells(intCol, 1).Value
ActiveCell.Offset(0, 2).Value = “v”
ActiveCell.Offset(0, 4).Value = Worksheets(“Away”).Cells(1, introw).Value
End If
Next introw
Next intCol
Hello Sir,
I think there’s a problem with the away game rule which is also leading to duplicates in the league table. For eg: Swansea 2-4 Man utd. Swansea is being duplicated in the league table. Please solve this.
Have a full round of fixtures been entered? I am not aware of an issue with the league table.
Hi Alan
Amazing tool but can’t seem to get it to work as per previous comment. I entered the teams for the 2016-17 season and created a fixture list. There is the problem where there are uneven numbers of home and away games. Secondly, when I entered a trial result from week 1 fixtures, it created a duplicate in the league table and I ended up with two Watfords. Would love to use this for the new season – any idea on how to make the fixture list accurate and how to fix the duplication?
Just downloaded this. Looks great! No problems so far once I got the macros sorted. This is just what i have been looking for, fingers crossed it stays good, thank you for sharing 🙂
Matt
No problem, thanks Matt.
I do have a slight problem. Worked great with the test leagues I ran up to 8 teams now im trying to run a full league of 20 teams. The fixtures create ok but when i enter results they do not display on the league infact after a second of displaying o’s it flashes to #NA’s across all the board. Any ideas or quick fixes? Many thanks
Perfect Job Alan.
Thank you v.m
Hello’s from Greece.
I have a question…how do i input my information to make it automatic? For example, if a team plays a game how do I input that? If i just type 1 in the cell then the formula gets erased and the other parts of the table with formula doesn’t show anything. Also, if a team wins a game what do I do to make the points section generate the points?
Just enter the results into the fixture list and the league table will look after itself automatically using the formulas you mention.
If you formulas do not auto calculate, check the formulas are set to automatic by clicking Formulas tab > Calculation Options.
Where in the fixture should I input the results? I don’t see a section for that
Columns B and D are there for the scores to be inputted.
english premier league 201 /2017 . any news on the new one
The new fixture list would need to be imported into the fixtures sheet from a source. Once in there the Calculation sheet is updated with the correct team names and it should all work the same.
What happens if two teams have not come to their game. Usually if one team doesnt pitch then the socore would be 2-0 soft points. But in this case its two teams
I don’t have an answer for this. What would you like to happen. No points for either team? You may need to enter it as a draw to record the result but enter a penalty point for each to ensure they got no points, or negative points.
I’m in charge of Mini Cricket in Kwa Zulu Natal cricket Union. I Have eight district to cover. Some Township have ten or six teams. Please help how to draft a simple fixture lis using excel. I did try other fixture generator, it’s ver clumsy though with dots. With excel will be more professional and clean layouts
I would recommend using a online fixture generator. There are lots around. Find one that suits your needs and that allows you to export the fixture list to Excel. You can then always modify the layout a little after.
Hi just downloaded the league sheet put 20 teams in but when I generate it’s only doing 18 and 18 on league table
Amazing tool but can’t seem to get it to work as per previous comment. I entered the teams for the 2016-17 season and created a fixture list. There is the problem where there are uneven numbers of home and away games. Secondly, when I entered a trial result from week 1 fixtures, it created a duplicate in the league table and I ended up with two Watfords. Would love to use this for the new season – any idea on how to make the fixture list accurate and how to fix the duplication?
Tried to make a 2016/17 League table for Division two.
However when I generated the fixture list there was an uneven distribution of home and away games.
One team ended up only playing 4 games away all season.
Can anybody help.
Hi Alan
Thanks for sharign this it is a great spreadsheet
wouldit be possible to have one spreadsheet with multiple leagues
or is my only option to have multiple spreadsheets?
You can have multiple leagues in one spreadsheet, no problem. The same technique can be used as much as you want.
Can u give me the idea to download this excel
There is a link on the blog post to download. If it is not working it may be blocked by your browser.
I want a league for 8 teams playing on the same night and venue 4 games each night Thankyou
Yes that is no problem to have Excel do this automatic league table
i want to run a type of heptathlon fixture list.
6 TEAMS
5 DIFFERENT EVENTS
Play each other twice per event
So this would mean each player plays 50 fixtures.
can you ut this into a tablr and fixture list for me.
tHE EVENTS ARE
PING PONG
AIR HOCKEY
FOOTBALL
POOL
DARTS
Sounds interesting, but I don’t set these up for people.
I have an online course that takes you through each step to build the league table yourself. Check out the Creating automated sports league tables in Excel course.
Hi Alan,
Great job with the spreadsheet
I’ve found that switching the even weeks home and away teams that this produces an even number of fixtures h & a for all teams and is done simply by checking if mod intgameweek 2 = 0 then populate away cell first, then populate home cell else behave as normal.
This is interesting. I do not have excel. I have Open Office. I have loaded your workbook, enable macros, and had a look at it.
At present it generates the league table, but refuses to generate the fixture list. I may have some follow up questions after you give me some thoughts on getting it to work in OO, please, if you would be kind enough to do that.
Those follow up questions are irrelevant if I can’t run it under OO
Hi the fixtures macro does not generate an equal number of home and away fixtures – is there an update on this?
Many thanks
John
Afraid no update, but the fixtures should be equal. They just sometimes get jumbled up and need manually moving.
Great work, especially on organizing placements when teams end on same points. Just tested with 4 team competition and it leaves out a match in the first week.
I see this was an issue mentioned before. Have you had a fix as yet?
can this fixtures manage 66 participant?
already try & league table only show 29 participants
can you guide me so this table & fixtures can afford 66 participants?
a thousand regards
djunarifta
Sorry but this generator does not support that many.
Hi, I came across this and thought it would be great for a school based fantasy league. I keyed in all the values as instructed and when I look at the league table there is #NA in all the spots and a value unavailable error. How can I fix this? Also how many teams can participate in the generator, help would be appreciated on this fantastic document. David
Firstly, thank you for your comments. The generator works but has its problems. There are others online that are a little better.
As for the league table. I’m sure that the errors disappear when results are entered into the fixture list. The IFERROR function could be used to hide them until the results are entered so it does not look so ugly.
I have an online class which teaches how to make these league tables here – http://skl.sh/2bvcJb5
Thank you, great program. What if you only wanted 8 weeks of games and you have 23 teams?
Hi GipsonA,
This spreadsheet is set up to work a specific way. I does not accommodate an 8 weeks and 23 team scenario without the code being adjusted.
The Home and Away sheets are used to build the fixtures list. The code uses this to create a fixture grid which the schedule is then built from.
Alan
Sorry forgot to ask are there instructions of how Home and Away works and what it’s used for?
Hi,
This is amazing! Is there any way to change how many times a team plays each other? I need it to go up to 10 if possible?
Not with this spreadsheet Chris. Just the two, it would need recoding. Unless you need something specific. would do it for playing each other twice and then copy it 5 times.
Hey, this is an excellent tool. I like to program but it takes alot of time to learn to build sophisticated codes like this. I am using this sheet for curling but I was wondering if there is a way to put into the code to limit games per week? I only have one hour once per week and four sheet of ice. Therefore I can only play four games every week. The number of weeks isn’t really important. Thanks
This code does not support such functionality on the fixture side I’m afraid. It is an interesting conundrum and it may be worth searching online for a fixture generator that fits your needs. The fixtures can always be moved to Excel to used in the league table generator.
Hi,just appreciate this great piece.just a concern though, have noted that when the fixture is generated most teams appear more times either on home/away side considering home team to be the first team and away team the second. is there a way to change that. Thanks
The fixture list should produce a random and working set of fixtures. It does mix up the weeks at times and teams appear twice in the same week. In the past I have just manually moved these.
Wasup,
So I entered the team names plus the number of times I wanted teams to face each other. When I clicked on create the fixture it did but the league table standings bit didn’t work out. All I see is #REF on every cell. what’s wrong??
Did it work when you started to enter results? Did you adapt anything on it?
Hard to fully answer without seeing it.
Alan,
just found this and worked all sorted in 30 seconds, thanks, no adverts, no US language which most online versions have and no sign up for endless adverts.
nice to see people doing something for nothing (like the sports volunteers who run the leagues)
Thanks again
Andy
Thanks Andy. Your welcome.
I am secretary of a bowling league, containing 6 teams. and i want to make a lane draw sheet so i know what team is playing who each week. i tried to create fixture but it kept telling me i was doing something wrong. as i am a novice with excel. i have no idea what im doing….
What message did it display Patrick?
You should just be able to enter the team names, select whether they play each other once or twice and click the button. Make sure that macros are enabled. You should be prompted for this with a long yellow bar at the top when the workbook is opened.
Hello,
First of all THANK YOU for such a great sheet, it has helped immensely with the league i run. If i may i would just like to ask if it would be possible to make any add-ons/adjustments? These are just some of the ideas:
1. Can the number of times teams play each other be increased to 4 as this helps with the smaller leagues
2. Can there be a section where a points deduction can be made which covers things such as forfeited games etc
3. Can we have a section where we can capture Yellow and Red card counts per player and team
4. Can we have a section where we can track goalscorers so that by the season end we can award the seasons top scorer?
5. When using the auto schedule option it seems to not have teams as Home and Away but rather one team can have all games at Home.
A lot of what i have mentioned above i actually adapted onto another sheet but it would be so much better if it was part of this sheet BUT the hugely important one for me is to increase how many times teams can play each other.
Your feedback is welcomed.
Thanks
Paolo
Thank you for the suggestions. Point 1 can be created by duplicating the fixtures created for teams playing each other twice. Point 2 I have done on one of my other league table posts and in my online course. I have not considered involving more analysis such as discipline or goal scorers but is an idea.
FIrst, thanks for your work in creating this spreadsheet for the rest of us.
I am the scheduler for our seniors snooker league. Currently we have 11 teams that play each other 2 times over the season. The spreadsheet works great for that. However, the 2 games that each team plays has a “home” game & an “away” game.
Can your spreadsheet be revised to accommodate this requirement ?
Hi Marke,
Thank you for the nice comments.
I believe the spreadsheet does accommodate for home and away. There is a list where you can choose how many times each team/player play each other. If it is set to 2 then you get home and away.
Alan
Thanks for the quick reply, Alan
When I make How many times do each team play each other = 2 and Number of teams = 11 I get each team playing 20 games (which is correct) but not equally at home & away… I get this… any suggestions ?
Team Home Away Total
Bonsor 4 16 20
Century House 0 20 20
Confederation 8 12 20
Dogwood-Blue 18 2 20
Dogwood-Red 12 8 20
Edmonds 14 6 20
Kennedy 2 18 20
Langley 16 4 20
Maple Ridge 20 0 20
Minoru 10 10 20
Pitt Meadows 6 14 20
Hi Marke,
I tested it out and yes it is not working the homes and away out correctly. Your quite right.
Sorry but I have no suggestions for it. It will need looking at.
There are some useful fixture generators you can find online which will be accurate. And some of them will export or enable you to simply copy and paste them into a spreadsheet like mine. Then the league tables will all work swimmingly.
Thanks, Alan… appreciate the quick reply.
Marke
HI ALAN
I HAVE BEEN TRYING TO USE THE ABOVE FOR A PREDICTION LEAGUE I HAVE AT WORK FOR THE PREMIER LEAGU E
WHERE THEY WOULD GO HEAD TO HEAD TO SEE WHO SOCRED MOST POINTS THAT WEEK AND THIS WOULD EQUATE TO A LEAGUE TABLE LIKE THE PREMIER LEAUGE
HOWEVER WHEN TRYING TO PRODUCE FIXTURES WITH 38 PEOPLE PLAYING ONCE THE MACRO FAILS?
ANY TIPS? OR IS THE TEMPLATE ONLY ENABLE FOR SO MANY ENTRIES?
There is not a limit on the number of entries, but there some issues with the fixture generator side. I have not known it completely fail. But sometimes the fixtures generated are a little jumbled. Right fixtures, bit messy normally.
Hi, thanks for a great spreadsheet. One piece of advice required. I’m using a league of 20 teams which are all shown in the league table. However when i ask to generate the fixtures there are only 9 (18 teams) produced.
Help.
Peter.
Hi Peter,
I don’t know much of what to suggest. Does cell E6 of the teams sheet recognise that there are 20 teams? If it does, I don’t know why it would not have worked. Sorry.
Alan
Hi Alan,
Love your formula, helped me so much. Your an honest hardworking guy, keep up the hard work PAL.
xxxxxxxxx
Tobs
Thank you very much Tobs
hey Alan, my name is jammy and i live in Sussex. my search team has been asked to lookup league tables and because of this great, actually incredible formula i have been promoted at my work. you’re a great man who deserves much praise,would love to be friends with a guy like you. Top man cheers for the formula
love jammy xxx
Your welcome Jammy. Not a problem. Thanks for your kind words.
Hi there
Thanks for this spreadsheet. I’m wondering if it is suitable to use on a Mac for a Summer Skittles League?
I’ve opened it and a message flashes up ‘This file contains Visual Basic macros… do not work in Office 2008 for Mac’ It then says I can either ‘Open and Remove Macros’ or ‘Open’ and keep the macros. To be honest I’m not sure what to do. Hope the query makes sense!
You know what it’s like, offer to help a mate out and I’ve ended up tying myself up in knots…
Thanks in advance.
Sorry, just occurred to me you’re probably not aware of the intricacies of Summer Skittles (neither was I a couple of days ago!!), so a bit of info might help you answer the question.
In our league there are 15 teams, each team plays the others once, there are 3 games a night each Monday to Thursday. In addition I need to factor in 8 evenings throughout these weeks when 14 of the 15 teams will be playing Cup matches. The scores for these will need to be kept separate from the league scores, so I’m presuming it’ll be best to use an additional fixtures and league table generator file? Many apologies if I’ve made that sound more complicated than it is.
Yes the cup and league fixtures should be kept separate. Probably separate sheets, but don’t have to be.
This fixtures generator won’t handle the 3 games a night scenario. But the fixtures could be manually split after generation.
I do have a course which explains how to create the league tables and cup tournaments. How the league rankings are automatically generated (not the fixtures though) – https://www.udemy.com/excel-league-tables-and-tournaments/?couponCode=SportsLeagueComputergaga
You can select open and keep the macros, although it sounds like they may not work on the Mac. You may need a windows platform. There are differences when it comes to macros with Windows and Macs/
Hi Alan
Thanks for both lots of info, really helpful. I thought that might be the case with the macros – those pesky old Windows/Mac incompatabilities!
Manually splitting the fixtures sounds possible, at least much of the work will already have been done.
Thanks again.
Hello I am trying to do a 24 teams championship, the schedule is fine but the league table is only full of N/A (after week3). What should I do?
Hi, I’m not sure what the issue may be. Can you email me the file.
Hi, I am sorry, I can’t find your email adress. Could you please tell me where it is. Really sorry about this!
Hi alan, I emailed you the file
Otherwise, The fixtures generation is quite impressive, congratulations !
Thanks De Handschutter. Much appreciated.
Hi Alan, I have created à 24 teams championship, the fixtures are fine but thé table is full of N/A. Could you help me please ? I work with Excel 2016
Hi
Really appreciate this and is a great tool but…
When I run it based on default 2 matches and 20 teams.
Week1 to 19 fixtures are repeated from Week 20 to 38 …Bizarre? I would expect Week 20 to 38 to be inverse of Week 1 to 19. If that could be fixed – then it is a Perfect piece of work! 🙂
Fixed by added this code to end of CreateFixtureGrid in the VBA Code
Sheets(“Away”).Select
Range(“A2:A24”).Select
Selection.Copy
Range(“B1”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets(“Home”).Select
Range(“B1”).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“Away”).Select
Range(“A2”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range(“A1”).Select
There probably is an easier way by tweaking it in your existing code, but the above works 🙂
Thanks again for this excellent excel spreadsheet, it is a work of art.
Thanks Paul, I’m glad you like it.
Paul,
This solution doesnt work, implementing it the result has (from memory) Team 1 playing Team 1 in round 4.
The correct solution is much simpler, in ‘Sub AwayGames’ just assign the home value to the away cell during population. this gives the exact same fixture list but home are now away and away now home.
Ill post the solution in an up to date post for current readers.
Cheers
Steve
Hi Alan,
I need to create a 9 team league with conditions about when fixtures take place.
For example Team X must be free in round 7, Team Y must play Team Z in round 12 etc…
Can your generator do this?
Thanks
Afraid not Paul. Bit too much for my generator.
Hi,
I’m currently working on a project which requires a fixture generation functionality. Would it be okay if I tweaked the code and used it in my project?
Sure
Hi Alan,
Can I download this onto a tablet or is it only for computers?
It can be used on any device as long as you have Excel and macros enabled.
Hi Alan!
Is it possible for the generator to split the results into more columns? Im looking at a beach volleyball tournament and they play each match by the best of 3 sets? so im wondering if there’s a way to input the scores of 3 sets into each game result.
Hi Shirmin,
Its possible, but you may as well just insert the other 4 columns yourself into the fixtures once generated.
You would then need to update the calculations for the league table though. Feel free to experiment with the file to see if you can do it. I do have on online course showing you exactly how I create these automated league tables. You can sign up here – https://www.udemy.com/excel-league-tables-and-tournaments/?couponCode=SportsLeagueComputergaga
This course does not cover the fixtures creation, just the automatic league tables from result input. It can be adapted to any sport or competition.
Kind regards
Alan
Hi mate,
Have you resolved the problem of having fixyures generated equally home and away? I just downloaded the spreadsheet and when I input teams, one play away all the time and others play home on numerous occassions week after week.
I don’t have a clue about macros etc but I can copy and paste if that helps!!
No sorry the fixtures side is not 100% perfect and can need manual adjustment.
Hi imm draing upp a league table for 22 player but is only allowing upto 20 is there anyway i can update to 22 players league table and fixtures
Sure, on the Teams sheet enter the player names, adjust to 22 and then create.
Hi just downloaded you table generator but when I try and create fixtures it says Macro could be corrupted on file is there any way around it to generate fixtures
I’m not sure why it would say this. Maybe try downloading it again. Macro works fine my end.
can you extend this to 5 division? please
That could be done. It would just be the same 5 more times. There would be no automatic functionality for promotion/relegation.
hi
i have tried to run the above for my prediction league at work, but when entering a result the league table and all its cells show as N/A, value not available error?
PLease help
Have you tried a few different results incase there is an issue with one team/player, or one fixture?
It should be working fine, but I couldn’t fix it without looking into it further. Typically this is caused by a typo in the fixture list though, causing the league table to not find that result.
Alan,
just used this again to organise a county league structure for work, saves me hours.
(i would pay a nonimal amount each time, especially if you had multiple formats, several pools, different formats etc)
thanks again
Hello,
Thanks for having this editable. I have two questions:
1. I created a new list of teams. I entered the first weeks scores. My league table has two teams listed twice. Any thoughts?
2. Is there a way to speed up the calculations? Mine takes about 6-10 seconds every time I add a score.
Thanks.
K>
Hi Kurt,
1. Unhide the Calculations sheet. Check the first columns formula. It will look like below. Check the bold bit is the last row of the table.
=IF(Teams!A21=””,””,RANK($B22,$B$3:$B$22,1)+COUNTIF($B$3:$B22,$B22)-1)
2. Yes it lags. It could be sped up with a bit more thought. I was planning to look at it but has not been done yet.
Thanks! i had the same issue .. it wasn’t reading a value of 0.5 (half points for assists), but any value like 1.5 or 3.5 was ok.
error was on the last row. so changed it to $B$22 and it worked. Table displays the proper values. Will test it further.
Glad to hear it working Deepak.
Hey Alan,
turns out the workaround displayed some of the teams in duplicates. So had to apply that change to the whole column , and that sorted it out.
Very happy into gameweek 2 of the season and so far so good. Thanks again for an awesome template.
You’re very welcome. Thanks Deepak.
Thank you! That was it. Thanks again for sharing this. I am learning many new tricks by seeing what you have put together.
Kurt>
How can I change this to add in 2 divisions? Also how can I also limit the # of weeks to 10? Currently the macro is running 42.
Alan,
Love the spreadsheet, I found that the second half (away games) of the fixtures was an exact duplicate of the first half (home games) the solution to this seems to be to swap the data being populated in the second half of the fixtures from the home cell to the away cell.
in Sub AwayGames simply switch the values being written to the cells –
swap line – ActiveCell.Value = Worksheets(“Away”).Cells(introw, 1).Value
For – ActiveCell.Value = Worksheets(“Away”).Cells(1, intCol).Value
And
swap line – ActiveCell.Offset(0, 4).Value = Worksheets(“Away”).Cells(1, intCol).Value
for – ActiveCell.Offset(0, 4).Value = Worksheets(“Away”).Cells(introw, 1).Value
This results in the same fixtures as for hame games but with home team switched to away.
Cheers
Steve
I changed the team names but the fixtures are not updating with the latest names! Any solution?
You will need to click the button to re-generate the fixtures. Plus ensure that macros are enabled.
Alan,
Everytime I finish putting in a rounds fixtures. #N/A shows up on the league table for everything and doesn’t change. The #N/A just stays there. Please can you help me.
When you say putting in a rounds fixtures, you mean the results yeah?
It should be working fine then. Hard to know the probably without seeing, but I would check the Calculations sheet (its hidden, right mouse click a sheet tab and Unhide) and it should have something like below in column A.
=IF(Teams!A2=””,””,RANK($B3,$B$3:$B$22,1)+COUNTIF($B$3:$B3,$B3)-1)
The bold bit should be the last row of your teams. 20 teams puts in in row 22.
Really helped would love it if you could create a excel sheet that can run two different pools fixtures on the same fixture list giving the user the choice how many matchdays the user wants and how many matches per matchday, really complex but would be amazing!
Where can I find templates for cricket points table?
Hi, I have a course which shows you how to create a Cricket League Table.
Good evening.
Please could you let me know why the league table would have #N/A in every column? How would I amend this?
Thank you
Dan
It should work find when results are entered. If not I would check the Calculations sheet, and maybe the ranges of the formulas in column A and B could be incorrect. Hard to say.
Hi Alan
On the calculations sheet the N/A is appearing in the ‘Rank ties’ column which I guess could be making every cell in the league table say N/A. What would I need to do to correct this?
I am a teacher and planning to use this in lessons to add competition to lessons so pupils can progress further.
Thank you
Dan
Hi Dan,
I would check the ranges used in the 2 rank column on Calculations (columns A and B). Just double check the ranges in the formula go to the last row with a pupil/team in.
Alan
Hi Alan
I have looked at that and the formula seems to be correct. I know this is a lot to ask but would you mind if I e-mailed you the spreadsheet or a screen shot for further advice? Thank you for all your help so far. Dan
You can email it to me Dan
Great, thank you so much Alan. What is your e-mail address so I can send it to you? It is very much appreciated, thank you. Dan
Hi, I have just downloaded your spreadsheet to use for our rugby teams. Is there any way I can adjust the points awarded for a win and also add in other points options e.g. bonus point for scoring 4 tries, bonus point for losing by less than 7 etc.
Hi Jane,
Sure. You can use the Data sheet (it may be hidden) to adjust the points awarded for a win.
As for the bonus point options, these can also be included but you would need to write some formulas. On the Calculations sheet you could create a column to calculate any bonus points earned. You can copy the formulas I use in the other columns and adjust them to your requirements.
I have an online course showing how these league tables are set up from the ground up, and they do include a Rugby example. You can enroll here – Create sports league tables in Excel.
Hello,
This is a phenomenal spreadsheet. Can it be adapted to include more than 20 teams? I want to include 36 teams…
Any help much appreciated
Thank you. The spreadsheet can handle 36 teams. Just enter them on the Teams sheet and it will pick them up.
You will however then need to update the Calculations and League Table sheets to include the additional rows, the teams made up. Just means copying the formulas and checking the range of the formulas in columns A and B.
Hi Man, great Sheet!
The main problem for me is that the Home/Away alternative system is completely Random.. have you fix this problem?
Best,
Marco
That would require major updates to the code Marco. Sorry it was set up this way to mimic football fixtures when teams regular play 2 or 3 times at home or away consecutively depending how the fixtures fall.
Hi,
Good work, it’s very useful,
I just have one problem, for “How many times do each team play each other” we can only choose between “1” or “2”, I added by myself “3” and “4”, but it doesn’t work, do you have a solution for that?
Thanks!
Jordi
Thank you Jordi.
Yes this program can only handle playing each other once or twice. I normally just copy the fixtures to create three or four times scenarios. I know its not completely random that way, but it works for me.
thanks good spreadsheet , my question is which column on the fixtures that i’m suppose to load the weekly results for the log standing to be automatically generated
Thanks you. Columns B and D are for the results.
I have 72 teams on board and each team shall play 4 league rounds with each other. how do i put them in a pool. please help. Thank you
This pool does not accept that number of teams or playing each other 4 times. There are some great online fixture generators and some of them will export them into an Excel file so then you can use the automatic league table.
Hi,
I am trying to add over 100 teams.
Once I generate them, many teams are missing. It also shows fixtures up to 99 weeks. How do I fix this issue?
Cheers,
Alex
Hi Alan Great program. Is there any way of rectifying a glitch I have whereby the fixtures do not generate hoem and away fixtures for each team in equal number. IE some fixtures are at home twice instead of one home and one away?
Best wishes
John Wilson
Yes unfortunately the program is not perfect and does need some modification. The home and away will be in equal numbers, but not necessarily order equally. It does need some tweaking.
I have some problem in league table when input up to 45 team. How to sloved this problem?
why when I input the score occurs doubling the team name in league table?
I think there is an error on the spreadsheet. If you unhide the Calculations sheet and in the formula on the first column edit the formula to row 22 instead of 20.
This excel program is very cool.
I am looking at just doing a singles dart tournament with maybe 20 players max.
Each player only plays the other player twice.
So i would only need to keep track of wins and loses not goals for and against. 1 Point would be awarded for a win.
I am not exactly sure how to modify the spread sheet to exclude the unnecessary columns.
Thank you Terry. I’m glad you like it.
I have a online course showing how this automatic league tables are created.
Is there a way to remove Home and Away?
You can set the fixtures so that the teams only play each other once.
have entered the results but when i go to table it shows #N/A
what could be the problem
Have you changed the number of teams? the most common problem relates to the Calculations sheet and the first column. Check the formula references all the rows of the table.
Hi, I would would like to use this fantastic worksheet to monitor a golf league but in testing I seem to have a problem. If I change the number of teams to say 4 and run the macro then 3 weeks fixtures can be seen on the Fixture sheet but 2 of the teams scheduled to play only 2 matches instead of the expected 3.
How will i adjust match fixtures if three weeks of match fixtures had been played already?
Hi Jeffrey, this fixtures sheet is very old and unfortunately is no perfect. I don’t have any plans to work on it at the moment.
How will i adjust match fixtures for 20 teams if three weeks of match fixtures had been played already?
Nice piece of work !
However, I wanted to use this for a local pool league, and found that the Home and Away fixtures are not even alternate, or even “balanced”.
I put in 9 teams called “Team A” thru to “Team I”, and ran the fixture generator. It all looked good until I did some rudimentary “COUNTIF”s, and discovered that Team C never play “Away”, and Team I never plays “At Home”.
This not going to be any use as it is, and I will try to modify it so that they alternate between Home and Away on successive fixture days. At the same time I will look at how to accommodate some venues having 2 teams, while one is “Away”, the other being “At Home”.
Obviously the macro will have to inspect the first part of the team names to see if they are the same, and not generate both at home or away on the same date. Could be tricky ….
Hi Dave,
You are right the fixture generator is not perfect. I created it many years ago (I think 8 years) and it goes close. I’ve used it many times and just tweaked the few issues.
However, I tend to use the many online fixture generators now and just copy the fixtures into my Excel spreadsheet that then auto updates the league tables. The league table side of things is perfect.
Alan
I want to use something like this to create a game schedule for a bocce league here are my parameters:
– 4 courts
– 10 teams – 2 teams each court so only 8 teams can play at a time
– teams play 2 games each week
– 3 time slots each week (9am, 10am, 11am)
– 28 weeks in season totaling 56 games
How can I modify this to meet those?
Hi Debbie,
This is a very specific question which goes beyond the spreadsheet provided. It would involve a lot of time to adapt and then explain.
There are many fixture generators available online. Some free and some cost. You may find one able to fulfil your needs.
I wish you the best.
Alan
Alan,
Thanks for sharing. What are the ranking rules used in this sheet, and in what order are they implemented?
You’re welcome, Dar. The ranking order is points, goal difference and then goals scored.
IS THERE A AWAY YOU CAN HELP US USE THE DATA TO CREATE SOCCER PREDICTION MODILE. SUCH A WAY THAT WHEN YOU CLICK OR SELECT A DROPDOWN LIST OF COUNTRYS, YOU IT WILL ACTIVATE THE NEXT TWO DROPDOWN LIST HOME AND AWAY AND THEN YOU GET YOUR PREDICTION.
This sounds like a great idea, but it is not something I have created or will be able to get involved with right now.
Hi i have used the fixture generator for a league of 32 teams but the league table is only showing 29 teams is there something i can do to get all 32 teams to show on the league table Thanks
Hi Darren,
This issue is probably related to the Calculations sheet (might be hidden). The table on that sheet needs to be expanded for all 32 teams, and then the league table also expanded for all 32 teams.
I wanted to include 3 teams in each game. How can i edit that.
I’m not providing hands-on help to make large edits to this spreadsheet. That would take an adjustment in the code to generate the fixtures.
It would be better to find an online fixtures generator and use those results with my Excel league tables.
Good morning
Please add 1 for a bonus point.
I want to use the spreadsheet for rugby union.
you get 1 extra point if you have 4 tries or your score is within 7 points of the opponent
Hi Roy, I have a rugby union example that I build in my course on how to create these league table generators. I do not have one on this blog though.
Hi,
I have tried numerous times to create a fixture list and the league table, and I am not winning. Can you please assist. The teams are 12 and they will play only once. The name of the teams are as follows;
1. Bhubezi FC
2. City Pillars FC
3. Djemba Djemba FC
4. Eastern Rangers FC
5. Junior Pirates FC
6. Labamba FC
7. Liverpool FC
8. Movers FC
9. Real Rangers FC
10. Wondrous FC
12. Xivavula FC
I don’t create the spreadsheets for users, but I do have a course explaining how to create the league tables – http://bit.ly/2Siivkm It does not show how to create the fixture lists. There are some fixture generators online that can help with this.
Hi, Is there anyway to save the league table and add new fixtures once all fixtures are played?
And is there a way that allows everyone to play each other 3 times every game week instead of once or twice?
Thanks
Hi Alex,
To save the league table. You can save and copy the file for a new season.
There is no option in this fixture list to play each other three times a week. However, the league table will work fine if you can get those fixtures in there.
Thanks for this. Works well and was simple to edit to suit my specific requirements. Just needed a little browse through the past comments to resolve the occasional “feature”. Curiosity is all it takes.
Thanks, John.
Hi is there a free spreadsheet program that will run this with the macro please, I cannot afford Excel. Thank you
I don’t Alan. I am an Excel user for the most part. Especially at that level.
When you use 4 teams for the League, there is a missing fixture in week 1 meaning one team has one less fixture than they should
I tried to change teams but fixture still have the original english team names.
Read through all comments and can see some of the same issues i’m having. First off, great sheet, perfect for needs. My issue, as many above was initially the duplicated teams in the league causing the league to crash……fixed this when i noticed the calculations table formulas in column A didnt include all teams in column B.
Issue i’ve located but cant fix (as im not skilled with Macros), is the fixtures including one team to play every game “Away” and one team, every game at “Home”. Naturally i can manually resolve this but i can see the issue in the “Home” & “Away” tabs. Both column A and row 1 are missing a single team, these are the teams. So the fixture generator is not recognising 1 team as having any home games and 1 team having any away games. Is there any way to edit the macro to pick up all teams as have both home and away?….Thanks in advance. Appreciate this sheet is so old now you may not even pick this up.
Hi Martin, good work on fixing the league table issue.
Yes, the fixtures have a few minor issues which years ago I solved manually or found a web fixture creator. My league tables in Excel work fine, but the fixtures I haven’t looked at in years.
Have just discovered the above spreadsheet which would seem to fit the bill for running a Bowls league. However we play the same team perhaps 3 or 4 times per season with up to 10 teams. Can I make modifications to cope with this please?
Hi Ian,
The fixture list is not set up for 3 or 4 and is not easily modified. In fact, it has some minor issues handling 2.
Hi I have put in 41 teams but only 29 show up on the league table. When I put in a result for a team that is not in the list of 29, it creates an error. If the result is involving teams of 29 then it is fine. How can I get it to show all the teams on the league table that are listed on the first sheet?
This post is very old and I cannot remember the specifics, but there should be a ‘calculations’ sheet which along with the ‘league tables’ sheet would need to be adjusted. The formulas are probably all set for 29 maximum.
It require me to input password once I try to record new Marco or customize it, somebody know what the password is?
Hi,
This is a wonderful tool but i am using it for an EFL Championship and when i put in the result of swansea it says errors but any other else in the top 20 it is fine.
Pls help
Hi William, thank you for your comment on my work. I would need to see the specifics of the problem to give an accurate solution. Often the issue is on the calculations sheet and is the size of the table and the formulas that reference it.
Hi there
Fantastic sheet. Can this be duplicated for use with Google Sheets so I can arrange shared access???
Yes, but the fixtures would be different as that is code generated. The formulas for the league table side is 100% possible to duplicate.
Thanks for sharing with us the useful content
You’re welcome.
This is a great blog post! I have been using Excel for years and I find it really helpful to be able to generate fixtures and league tables.
Thank you very much!
How can I get this to work on Excel 2003
thx