The World Cup is starting next month and I have a free template for you to use whether you want to track the matches or make predictions with a group of friends. The entire schedule is downloaded within the file, and you can enter the the scores — both actuals and predictions.
How the template works
There are four tabs on the template:
Actuals: This sheet name needs to remain intact as when comparing your predictions, this is the source data that the prediction results will compare against.
Prediction.Blank: This is a blank sheet that is the same as the actuals that you can use for making predictions.
Prediction.Results: This is where you should post your final prediction results. Column A (name) is for the name of the person whose predictions they are. In columns B:D, you just need to copy those fields from the Predction.Blank page for any predictions you or someone else makes. It’s important the values are in the correct fields for everything to be recorded correctly. Also, make sure to paste them as values to ensure the formulas aren’t being copied over.
I’ve also added a section below the main prediction table that shows the teams you predicted to advance to each elimination stage:
This should also be copied over to the Prediction.Results tab. The stage should go in column B and the team will go in column C. I’ve left an example of how the Prediction.Results tab should be filled in within the template so that you can follow along. The table will automatically calculate and compare against the actuals.
Scoring.Rules: Here you can set up how points are allocated if the score, result, and total number of goals are correct. You can also specify if the teams need to be correct (applicable for knockout rounds) and how many points to assign if you’ve got the right team in the right elimination stage.
The basic idea is that you can make a copy of either the Actuals or Prediction.Blank sheet and give to someone to fill in. Once you get back their predictions, you can paste them into the Prediction.Results table. Then, once the tournament begins, you populate the Actuals. After that, it’s just a matter of updating the pivot table on the Prediction.Results page to see who has the most points.
Enter scores in the 0:0 format
One important item to note is that when entering scores, there should be a number followed by a colon, then by another number. There should be no spaces any no different characters, otherwise you will get an error and the value won’t be read properly.
In the case of shootouts and extra time, there is no extra field to enter these values in. For the purpose of minimizing the complexity of formulas, only one column is used for scores. The workaround for this is if a team wins in extra time or a penalty shootout, simply add 1 to their score so that instead of entering it as 1:1 (5:4) it would just be 2:1.
Tables will update automatically
The benefit of using the template is that the tables will automatically update based on your selections. This will help you in determining which teams play one another in the knockout rounds.
The template does factor in tiebreakers but in the event that a rare situation comes up where the tiebreaker doesn’t calculate correctly (e.g. it comes down to fair play points) then you have the option to override the values.
Next to the tables, there is another table for overrides:
In this example, I have overridden the results so that these will be the standings for Group B regardless of what the spreadsheet has calculated. You don’t need to list every team in a group and only need to list the teams that are tied. You also don’t need to specify the group letter.
Adjust the times for your time zone
Another feature that I’ve added here is to adjust the game times for your particular time zone. On the left-hand side you can specify how many hours you need to adjust for GMT. Remember that the value here will need to factor in for any daylight savings that may be in effect for your location (Qatar doesn’t adjust its time).
For example, in my region, my GMT adjustment is -7 but after factoring in for daylight savings in November, it becomes -8. So please remember to adjust for any daylight savings adjustment that might happen between now and the start of the tournament. For GMT -8, the local time would adjust by 11 hours (Qatar is +3), and so a match starting at 7pm local time would be playing at 8am in my time zone.
Highlight teams with a Watchlist
You can also highlight teams you want to track on the schedule by adding them to a Watchlist next to the schedule. For example, suppose I want to follow any games involving Canada and the USA:
Any values that are entered under the Watchlist will highlight corresponding matches on the schedule. For this to work, you need to enter the team names the same way that they are spelled out on the match schedule.
Download
This template is available free of charge, and you can download it here. It is locked to ensure that the formulas remain intact and nothing gets accidentally erased or overwritten.
If you like the Free World Cup 2022 Prediction Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
