# Excel Fixtures and League Table Generator

17 Flares 17 Flares

Due to popular demand I have created an Excel fixtures 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.

To use the file;

1. Enter the teams competing and how many times they will play each other on the “Teams” sheet
2. Enter the results from fixtures on the “fixtures” sheet
3. View the league table on the “League table” sheet

## 288 thoughts on “Excel Fixtures and League Table Generator”

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

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

• i want to play teams onlu once in a league….want table not to show games like home and away

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

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

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

3. 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?

4. Nice!
But using 4 teams, where the teams play each other once, one game is left out in the first week.

IP

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

6. Sorry but do you have the file in excel of 2003 ? Would appreciate if you kindly have it Thanks

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

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

9. 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!

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

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

12. 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!

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

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

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

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

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

• I have emailed you Debs.

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

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

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

I charge Β£5 to set fixture lists up for people tailored on their requirements. Let me know if I can be of help using the link below.

Alan

21. Nice one for this! Just what I needed for my 5’s soccer league…much appreciated . Chris

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

23. Do you have one which can work for a snooker league. The fixtures side is great just the league table to change if possible??

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

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

26. 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?

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

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

29. Hi,
Great tool, thanks for sharing…

Would it be possible to change the points allocated for a 3-0 victory?

Regards, Pheobe.

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

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

• Absolutely Graeme.

32. 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)

Tom

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

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

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

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

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

38. 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 π

39. 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 π

I’d like to help but I would need to see the spreadsheet and the errors you are getting.

40. 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!

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

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

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

44. 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?

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

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

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

48. 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?

• 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

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

50. I am trying to modify the number of players to 19 , but it seems to throw everthing out – can you help?

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

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

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

54. Hi,
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

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

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

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

58. This is simply fantastic! Many thanks for sharing it! π
Will try to figure out how it works.

59. Hi!
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

60. Hi,

what can I do to be able to create fixture with 3 teams?

(Great sheet btw. I really appreciate the sharing)

Regards,
Arild

61. 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…

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

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

63. the fixture is great but when you put results on 20 team fixture the table does not come out. what do i do

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

• Click the button. Make sure macros are enabled on opening the file.

• I do not use Numbers so could not comment. This is set up in Excel though, yes.

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

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

67. ^^^^ 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.

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

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

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

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

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

73. 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!

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

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

76. 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?

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

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

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

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

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

81. Hi just downloaded the league sheet put 20 teams in but when I generate it’s only doing 18 and 18 on league table

82. 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?

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

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

• There is a link on the blog post to download. If it is not working it may be blocked by your browser.

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

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

88. Hi Alan,

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.

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

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

91. 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?

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

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

• 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

94. Sorry forgot to ask are there instructions of how Home and Away works and what it’s used for?

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

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

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

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

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

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

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

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.

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

• 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

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

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

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

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

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

107. 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…

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

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

109. Otherwise, The fixtures generation is quite impressive, congratulations !

• Thanks De Handschutter. Much appreciated.

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

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

112. 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…

Thanks

• Afraid not Paul. Bit too much for my generator.

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

• It can be used on any device as long as you have Excel and macros enabled.

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

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

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

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

• That could be done. It would just be the same 5 more times. There would be no automatic functionality for promotion/relegation.

118. 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?

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

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

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

121. Thank you! That was it. Thanks again for sharing this. I am learning many new tricks by seeing what you have put together.

Kurt>

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

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

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

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