Euro 2016 is here and I have a Euro 2016 planner to share. The spreadsheet has a separate sheet for the group stage and the knockout stage. Enter the results into the boxes on the sheets and everything is calculated automatically.
The group league tables are ranked as the results are entered. As soon as a groups fixtures have been completed, the knockout schedule is created. The quarters, semi-final and final fixtures are then calculated as these results are entered.
The spreadsheet is unlocked so you are free to check out the formulas and how it all works. Download the Euro 2016 planner spreadsheet.
What Excel Skills Can I Learn From the Planner
There are many formulas and techniques used to create this Euro 2016 planner. They include;
The SUMPRODUCT function – This has been used heavily to perform the calculations at the group stages. I thought it was the best function to handle the calculation of a team’s games played, number of wins, goals scored etc. Learn more about the SUMPRODUCT function.
Ranking and then calculating unique ranking – The rankings for the group stages are done on the Calculations sheet (you will need to unhide it). On here I needed to calculate a teams ranking based on points, then goal difference and then goals scored. If a ranking was still tied, a unique ranking was formulated.
Nested IF’s – These have been used in the knockout stages to organise the fixtures as the results are entered.
The VLOOKUP function – It was guaranteed to be used somewhere. It has been used on the League Tables sheet (another hidden sheet) to formulate the group tables from the workings on the Calculations sheet. It is also used to help calculate the third place teams permutations (explained later in this post). Learn more about VLOOKUP.
The Excel Camera tool – A brilliant little tool. Used to create a dynamic picture of the group tables from the League Tables sheet onto the Group Stages sheet.
These skills and techniques are all shown in detail in my online course for creating your own sports league tables and tournaments in Excel. Join hundreds of others in learning these skills. I will be on hand to help with any queries.
Last 16 Permutations for Third Placed Teams
The UEFA European Championships 2016 has a new format for some of the fixtures in the last 16 (the first knockout round). The top 2 teams in each of the 6 groups are automatically through. They are then followed by the 4 best third place finishers.
A ranking is given to each of the teams who finish third in their group. The four best teams’ progress. Who they play in the next round is determined by the table below.
For example, the table above shows that if the 4 best third place teams are from groups A, B, C and D, then the winner of group A would play the third best team in group C. But if the 4 best third place teams are from groups A, B, D and E, then the winner of group A would play the third best team in group D.
This made things a little complicated to calculate which team progresses and who they will play in the last 16. On the League Tables sheet you can see a league table for the third place teams. The four best groups are calculated and the table below is formulated.
On the Knockout Stages sheet a VLOOKUP function is then used to return the correct team from this table.
Thanks for sharing this Euro 2016 planner template! I’m really excited for the tournament and this template will help me keep track of all the matches and scores. Can’t wait to use it!
This Euro 2016 Planner Excel Template is fantastic! It’s so easy to use and has everything I need to keep track of the matches and my predictions. Thanks for sharing!