Excel Fixtures and League Table Generator

17 Flares Filament.io 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.

Download the Excel fixtures and league table generator.

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

  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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  23. Hey, i change name teams, and then i press the button “Create fixtures” and nothing happens, what is the problem? Thanks.

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

  25. Hi,
    Great tool, thanks for sharing…

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

    Regards, Pheobe.

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

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

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

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

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

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

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

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

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

  35. 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 πŸ™‚

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

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

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

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

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

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

    • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  60. 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,

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

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

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

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

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

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

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

      • 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

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

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

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

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

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

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

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

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

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

  76. Hi the fixtures macro does not generate an equal number of home and away fixtures – is there an update on this?

    Many thanks
    John

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      • 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

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

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

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

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

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

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

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

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

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

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

    Kurt>

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *