FIFA Worldcup 2018 Excel Tracker – FREE Download

Power Query , Templates - 25 comments

FIFA world cup 2018 is around the corner. I love soccer, I love Excel, Let’s marry them. Here is an awesome, free FIFA world cup Excel Tracker to help you follow this year’s games in Russia.

Click here to download the FIFA world cup 2018 tracker.

What you can do with this FIFA world cup Tracker Excel?

You can use this tracker to,

  • View schedules in your local time for group and knockout stages
  • View summary and detailed points table
  • Refresh liveย points table. When you refresh, the tracker show updated points based on latest results (You need Excel 2016, Office 365 or older versions of Excel with Power Query)
  • View knockout stage matches as a bracket
  • See timeline of the matches

How to use the world cup 2018 tracker?

Start page – set up the tracker

When you open the workbook, use “Home” page to set up your local timezone and favorite teams.

FIFA 2018 world cup tracker - home page

Group Stage – Schedule & summary points table

The group stage view shows all teams. It highlights your team(s) in different color to see when their games will be on.

2018 soccer world cup Excel - group schedule

On this page, you can also see group-wise points table. This is a live table. So as the matches happen, just press CTRL+ALT+F5 to update this table. The data comes from Wikipedia FIFA 2018 page thru Power Query. So you need Excel 2016 / Excel 365ย  or an older version of Excel with Power Query to get the refresh work.

group level points table - football world cup 2018

Bracket View – Knockout Schedule

The knockout stage shows last 16, quarter-final, semi final and final matches in bracket view. This page is not dynamic. So as group stage finishes, go ahead and list the team names in the gray cells.ย 

bracket view - knockout stage - Russia world cup 2018

Timeline View

This shows all matches in a Gantt chart like view. From this you can track all your favorite team matches. It will also highlight current date so you know which matches are coming up next.

soccer world cup 2018 Russia - timeline of the matches

Download FREE FIFA world cup tracker

Click here to download the FIFA world cup 2018 tracker.

Errors or Problems with this template?

  • Error: Points table looks funny, incorrect
  • What to do:
    • If you know Power Query:
      Go to Points Table. From Query ribbon, edit query.
      Backtrack the query to see at what step things have gone south.
      Try fixing it.
      Usually, this is because the wikipedia points page has been changed by someone
    • If you don’t know or care for Power Query
      Try downloading the latest version.
      As the games progress, I will test this and upload new version whenever I see serious issues.
  • Error: Can’t see times or values in cells, All I see is #####
  • What to do:
    • This happens when the column is too narrow for the content
      Turn on Headings from View ribbon
      Adjust column width
  • Error: Can’t refresh the points table
  • What to do:
    • You need Power Query for Excel & internet connection to refresh.
      If you said no for either, try fixing the problem or manually enter points in the table.
    • Sometimes if the wikipedia page is in the middle of editing process, you might get funny results. Wait a few minutes (until the match is over) and refresh.
  • Error: Something else
  • What to do:
    • Try downloading the latest version.
      As the games progress, I will test this and upload new version whenever I see serious issues.

Which teams are you rooting for?

As usual, I am cheering for Germany and Brazil. I will also watch out how Denmark & Sweden (my former homes) are going to play.

What about you?

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or may be join my online video class to master Excel.

Thank you and see you around.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “FIFA Worldcup 2018 Excel Tracker – FREE Download”

  1. Andy F says:

    Good work as always - I liked the way you did the "menu" on the left hand side (although the buttons aren't lined up between tabs if I'm being ultra picky)

    Have you previously written about the method of extracting the Wikipedia page into Power Query? It's not something I recall seeing before.

    ps other geeky observsations:

    - the bracket columns are too narrow for the date & match number - and will need to be wider still when the team names get populated
    - match 51 should be Moscow (Luzhniki) for consistency
    - it's not possible to be 23 hours ahead of GMT - the International Dateline gets in the way! I think the maximum is 14. There are also a couple of countries who work to a quarter hour to make it really complicated!
    - There's a typo in the how-to - "compated" instead of compared

    • Chandoo says:

      Thanks for the lovely feedback. I have fixed almost all of them.

      1) button alignment: this is tricky as row heights can change between sheets.
      2) Column width is fixed now so bracket view looks better
      3) Updated the stadium name
      4) Did not bother with the 23 hours ahead thingie. This is more of a novelty feature ๐Ÿ˜›
      5) Fixed the type
      6) Fixed an issue with live score table. This should work as long as the points table is maintained in wikipedia page - https://en.wikipedia.org/wiki/2018_FIFA_World_Cup

      7) I have not discussed the technique of reading all tables on webpage to one big table. Watch out for a blog post on this soon.

      • Andy F says:

        Button alignment is one for the ultra-OCD sufferers ๐Ÿ˜‰ There are ways, but only for those with too much time on their hands.

        • Chandoo says:

          Aah, Excel. The perfect tool for people like us. Everything (cells) is in same shape and size by default and aligned perfectly. ๐Ÿ˜€

          • Etienne says:

            Is there actually a way to copy row heights (in the same way you copy column widths?)

            By the way Chandoo, great post. I'm forwarding to my department. I actually use another query from the same page to automatically fill in the team names for the knock-out stages (I made one for round of 16 which I then duplicated and edited for quarter-finals etc.) This is incredible, I was always wondering how to do these type of queries from the web, and now I know ๐Ÿ™‚

  2. Jake says:

    Hello!

    This is quite amazing and incredibly cool to use ๐Ÿ™‚

    Testing the constraints of this sheets a few errors popped I noticed:
    - Vlookup Group E-H refers to column J instead of E (eg. Brazil gets the same points as Russia because the formula looks up Russia twice)
    - Power query only has 29 lines, the overview of has 32 but the 3 countries from group A are lost as the overview is refreshed - causing N/A in the group stage colums

    • Chandoo says:

      @Jake.. thank you. I am sorry for the errors. I could not test the live points table until the games began. I see my folly now. I have fixed both issues and uploaded a new file. As the points table relies on a wikipedia page, if someone decides to change the layout or rename a column it can seriously harm this template. I took some precautions in the Power Query layer to adjust column names dynamically etc, but it is not foolproof.

      Try downloading the newer version and let me know if you see something funny.

      • Jake says:

        No worries!
        Was able to fix the vlookup myself but the power query had me bit stumped ๐Ÿ™‚ And wanted to give you a heads-up to everyone can enjoy it!

        Thanks for the awesome sheet!

  3. Darren G says:

    Hi,
    Thanks for sharing this world cup tracker. Certainly makes it more interesting when the data is current. As a newbie, it also helps to have a couple of mistakes to find whether unintentional or not.

    Thanks again

  4. Christian says:

    Hi,

    Your v-lookups in the "Group Stage" tab for groups E, F, G, and H (all the ones under column O) are pointing to the wrong country. They all point to column J, so whatever happens to the countries in column J will also be reflected for the countries in the groups in column O for that same row.
    Just thought I'd call that out. Thanks for the great work on this!

    • Chandoo says:

      @Christian... Thanks for trying this and letting me know about lookups. I have fixed the issue now. Please download latest version for that and few more fixes.

  5. Sheeloo says:

    Refresh All did not work correctly. Team names vanished though points were updated.

    • Chandoo says:

      @Sheeloo... Can you please try with latest version (download again using above links). I tested up to latest Iran's stunning win over Morocco and it works.

  6. Gsm says:

    Dear Chandoo

    Thanks a lot for this worksheet.

    However, while refreshing the data, I am getting error message as "Initialisation of Data Source failed".

    • Chandoo says:

      May I know what version of Excel you are using? Do you have internet connectivity? If you are familiar with Power Query, try tracing the steps in the query editor. And oh, first start with the latest version of file (link above).

  7. Andy F says:

    @Etienne - yes. Copy row, paste formats will do it, although obviously that will bring the formats of every cell in the row as well as the height.

  8. Rob Tsintas says:

    Latest version seems to be working well.

    One request: the Groups & Points tables on the Group Stage sheet have the team names pre-entered. This means they don't get sorted according to the results.
    On my copy, I've changed them to a lookup, so they appear in the same order as the points table. It would be good if you can do the same if/when you release a new update!

    Here's what I did. It's not the most elegant, but it works, and I didn't have much time to spend on it!

    Using helper values of 1,2,3,4 in columns I and N for each group, the formula for the first team name in group A (cell J4) is:

    =INDEX(points[Team],MATCH(OFFSET(J4,-(I4),0),points[Group],0)+(I4-1))

    This can be copied & pasted to the other team name cells.

    Cheers!

    • Chandoo says:

      Good suggestion. I have made changes to the points table to remove lookups and just show teams in the order they appear in the detailed table. This way, You will see top two teams on first two rows. We could highlight them as well (figured this would make it look like a bowl of M&Ms, so didn't bother) or highlight *YOUR* team.

  9. Paige says:

    I consider my Excel skills as above average but far from guru and I love how your little projects like this get me to look at data in a new way. I would like to expand on the data in the points table through the use of some calculations but I am a little challenged by the data coming across as text. The Pts column is easy to deal with, but I'm having problems with the GD. The negative goal differential looks like it may be noted with an en dash instead of a minus sign, but if I search for an en dash in the data Excel doesn't find any. I would like to include conversion to a minus sign in my little macro so I can get everything to numbers but so far I am not having any luck. Any thoughts? Thanks for your help.

    • Chandoo says:

      Thanks for such kind words ๐Ÿ™‚

      I suggest adding an extra step in Power Query to convert points, GD & other columns to numbers. You can replace em dash in PQ. I did not do it as this will add another layer of dependency and should the wikipedia page change, one more reason for the query to fail.

  10. Petros says:

    As always, an awesome spreadsheet from Chandoo. I love the Power Query score update without macros. The country watch-out is a unique feature as well!

    For those who like a predictor template with flag lookup and a ribbon UI, here is our spreadsheet:

    https://www.spreadsheet1.com/fifa-world-cup-2018-russia-free-prediction-templates-for-excel.html

  11. Pranav says:

    Great template!

    I came across another one with image vlookups for country flags

    https://eexcel.co.uk/downloads (World_Cup_2018_Sweepstake.xlsx)

  12. Sean says:

    This is a great Template.
    I am running Excel 2010 with the PowerQuery add-in running.
    The scores will not update, so I followed the error and the second operation (Fitlered rows) says that the table is empty.
    After a few minutes on Wikipedia, I realise that my PowerQuery skills are not good enough to work out what the issue is.
    Any suggestions?
    I would like to fix it myself is possible.
    Thanks,
    Sean.

  13. Juan Pablo Diez says:

    Where can I see the results for a specific match?

    Thanks!

Leave a Reply


« »