This blog post is brought to you by Diego Oppenheimer a Program Manager on the Excel team.
As we approached the 2010 World Cup in South Africa, and continuing on a theme from a couple of weeks ago, I set off to create an Excel template that was as automated as possible; with not a single line of VBA code but with conditional formatting, formulas, and the camera tool feature; I wanted to create the ultimate couch companion for the hours I have planned to spend in front of the TV waking up at 4:30 AM PST to watch 32 nations battle it out for ultimate glory.
After some quick research I managed to pull in all the data I needed from FIFA’s official website. I have laid it out nicely right here (World Cup 2010 Template.zip), which will be our starting point for today’s post. My goal is to walk you through the process of creating the template so that you end up with something like this (image file).
In case you don’t want to create the template yourself you can download the finished template here.
The Group Stage worksheet
The Group Stage worksheet will allow me to keep track of the first 48 games. It will also serve as the base for my calculations regarding who qualifies to the knockout phase.
In this section I will be working on the sheet called Group Stage
Step 1: Create tables
To make life easier when referencing columns, I’ll make tables out of the block of data that I got from FIFA.com.
1. Select cells B6:J54
2. On the Insert tab, click Table, and then click OK (or just press Ctrl + T).
3. On the Design tab, in the Properties group, type GroupStage in the Table Name box.
Step 2: Create conditional formatting rules
A few things I want to do now:
· Use highlighting to draw attention to empty cells.
· Differentiate between winner and loser
· Highlight the games that my favorite team is playing.
For each goal, you’ll create conditional formatting rules, so let’s go to the Conditional Formatting Rules Manager that can be found on the Home Tab under Styles > Conditional Formatting > Manage Rules.
1- Conditional Formatting rules manager
Rule 1- Display an orange background for empty cells: For this particular rule, we want to select New Rule… and use the “Format only cells that contain” rule type selecting “Blanks” under Format only cells with. We can now select the type of formatting we want to apply. Let’s apply this to all result cells (in my case H11:I58).
Your rule should look something like this in the manager.
Go ahead and test it out by typing a value into one of the orange cells., As soon as you enter any character, the orange formatting disappears.
Rule 2 – Differentiate between winner and loser: When scanning a long list of 48 games, we want to quickly be able to identify a winner from a loser. Our brains understand colors quicker than numbers (or at least that is what I am told) so this seemed like a good idea at a time. Once again, I want to create a new rule in the Conditional Formatting manager. This time, however, I want to use a formula to determine how to color the result. For this example I have chosen green for the winner and grey for the loser. There are four conditions we need to account for:
· Team A beats Team B therefore Team A’s result is green.
· Team A beats Team B therefore Team B’s result is grey.
· Team B beats Team A therefore Team B’s result is green.
· Team B beats Team A therefore Team A’s score is grey.
Under rule type select Use a formula to determine which cells to format and make the formula =H7>I7. Then, select H7:H58 as where you want this rule applied to.
Note: Make sure that the cells you are referring to are not absolute references (no $ in front of the column or row) since you want H7 > I7 to evaluate for the first row, H8 > I8 for the second row, and so on.
Your new rule should look something like this:
Following the same method we are going to create rules for all of the remaining cases. When you are finish, the Conditional Formatting Rules Manager should look like this:
Note: If you have added rules but you don’t seem to find them make sure that the drop down at the top of this dialog is selected to show formatting rules for the “This Worksheet”.
Go ahead and play around with the scores to make sure our rules are working right. We haven’t really defined any formatting for ties but feel free to go ahead and use your new knowledge to get creative in this area.
Rule 3: Highlight the games that my favorite team is playing: I wanted a way to easily identify the games that my team was playing in the first Group Stage so I am going to create a rule that is going to read both team columns row by row and if it finds a match to my favorite team highlight the entire row.
Ok, so let’s start building the rule. We always want to compare to the Favorite Team cell (in my bare bone sheet this is I7 so when I do reference it I am going to make sure it’s an absolute reference in the form of $I$7.
For this we are going to need to create 2 rules, one that will read through the Team A column and another that will read through the Team B column.
Once again we want to use a Formula rule , selecting the first team cell locking the column reference ($G7) and make it equal to our favorite team cell ($I$7). We then want to apply this rule to all the columns excluding the score columns. The second rule is essentially the same thing but we want to change the comparison cell to be ($J7 = $I$7).
Your new rules should look something like this:
The next step is to create a couple of helper columns in our table that will allows us to calculate , wins, loses and points in our group standings sheet. In the first column to the right of our table create a new column with the header Winner, followed by a column labeled Loser and two more columns labeled Draw 1 and Draw 2 respectively. The idea behind this is that we will record in the columns every instance of a team winning, losing or drawing then using COUNTIF formulas we will tally up the results in our Group Standings sheet.
For the Winner column we want to compare the results Score A to Score B, if they are equal we should leave this blank, if Score A > Score B then we want to record the name of Team A in the Winner column, if not then we want record the name of Team B.
The formula would look something like this:
=IF([@ScoreA]=[@ScoreB], “”, IF([@ScoreA]>[@ScoreB],[@TeamA], [@TeamB]))
Go ahead and copy this down for the entire column.
For the Loser column we essentially want to use the exact same formula but reverse it looking something like this:
=IF([@ScoreA]=[@ScoreB], “”, IF([@ScoreA]<[@ScoreB],[@TeamA], [@TeamB]))
Finally for the Draw columns we want to record the both names of the teams that came to a draw (excluding when they are equal because they are blank). For this I used the following formulas in Draw 1 and Draw 2 respectively:
and copy these down for the entire column.
Great so you got this far and haven’t quit all that is left on this page is some simple formatting. In my example I have applied some table formatting, changed some titles to have white format (and essentially disappear), hidden my helper calculation columns as well as turned off all the gridlines giving the whole sheet a nicer cleaner overall look.
Switching over to the group standings the first thing we want to do is to create tables out of every group (and name them appropriately, GroupA, GroupB, etc.) I have already grouped and set up the data for this so it should be pretty painless.
Wins, Losses and Draws
The next step is to create our calculation to count how many instances of that particular team show in the Winner column. For this we are going to use the COUNTIF formula. The COUNTIF formula takes 2 parameters, a range and criteria. For the range we want to select the entire Winner column from the GroupStage sheet. For the criteria we want to select the cell that contains the country name.
Copy down this formula for every Winner column in each one of the group stage tables. Then do the same for the Loser column using the formula:
We have two Draw columns so we need to sum up the COUNTIFs for both columns. For this case I used:
=COUNTIF(GroupStage[Draw 1],[@Country])+COUNTIF(GroupStage[Draw 2],[@Country])
once again we want to copy this down on every group table.
Goals in Favor, Goals Against and Goal differential
To calculate GF, GA and GD we need to sum up all the goals that a team has scored as well as all the goals that have been scored against them. For this I am going to use the SUMIF function which as the name suggests will only SUM if certain criterion is met. In our case I am going to look through the entire TeamA and TeamB columns (from the Group Stage worksheet)looking for a name match and if found sum up all the goals.
By slightly varying the formula we can also calculate the goals scored against by still looking up the team on either the TeamA or TeamB columns but doing the SUM on the opposing score:
For both of these you want to copy them down in every group.
The point differential is just a simple = [@GF]-[@GA].
Points and Ranking
Now we have all the information we need to calculate total points and ranking within a group (which will allow us to determine who qualifies to the next round).
Since the 1994 World Cup points have been assigned by the following criteria:
Draw 1 pts
Loss 0 pts
The top two teams by points qualify to the next round. In the case that two teams have the same number of points, goal differential is used to break the tie (in the case that they are also tied for goal differential a series of criteria come into play that I have not implemented in this template but can be found here).
Ok, so first let’s calculate the points (number of wins *3 + number of draws):
Second let’s add a tie breaker column to all our group tables. I have chosen to multiply the number of points * (Goal differential / 100) so:
=[@[PTS * (GD/100)]]
Now I need to rank the countries in every group. Under the Rank column I use the RANK formula which takes as criteria the Tie Breaker value for each individual row and the entire Tie Breaker range returning a value from 1 to however many entries I have in the range (in this case 4). The problem is that the RANK formula does not take ties into account (RANK.AVG that we shipped in Excel 2010 does) so I have to add some cleverness to get around this. In this case if the GD and the Pts are both equal to 0 (meaning that they haven’t played a single game yet) I am going to assign a random “RANK” of 99:
=IF(AND(IF(GroupA[@GD]=0,TRUE,FALSE),IF(GroupA[@Pts]=0,TRUE,FALSE)),99,RANK(GroupA[@[Tie Breaker]],GroupA[Tie Breaker]))
Note: With Excel 2010 we could have just used the RANK.AVG function and all we needed here would be =RANK.AVG(GroupA[@[TieBreaker]],GroupA[TieBreaker])
copy this down for every row in each group stage modifying the formula for each group so that it reads GroupA, GroupB,etc.
Let’s take it for a test drive, as you insert values in the Group Stage sheet you should see the tables update in the Group Standings sheet.
All that is left on this sheet is formatting. Once again I created a custom table style, turned off the grid lines and either hid the entire helper column or just changed the font to white so it wouldn’t show.
Wow, we’ve gone through a lot haven’t we? Don’t worry we are almost there. The next phase is to determine who advances in the knockout stages. After the first 48 games of the Group stage are played the top 2 teams of every group make it into the knockout stage. If we go over the Knockout Stage I have already set it up (1A indicates the team ranked first in Group A, 2B indicates the team ranked second in group B, W49 indicates the Winner of Match 49, etc.). Just go ahead and create new tables for each Stage and name them appropriately.
To automatically find which team is ranked first I am going to use the VLOOKUP function. The VLOOKUP takes as parameters the value I am looking for, the table array (area) where it should be looking, the column from the table array it should retrieve the value form and wither it’s looking for exact or partial matches. In case that I have no ranks I just want it do display 1A , 2B, etc. so I am going to combine my VLOOKUP formula with an IFERROR formula (VLOOKUP will return an error if it cannot find what it is looking for). So I am going to go ahead and replace the cell under Team A for Match 49 with:
Slightly modify the formula to make it look for the correct position and in the correct Group for the remaining cells in the Round of 16.
If you Show Formulas (Ctrl + ~) you should end up with something like this:
Quarter Final, Semi Finals, 3rd Place and Final
At this stage it’s pretty simple to determine who goes on and who falls out since every game is a knockout game. So to determine for example who will play Match 57 we just need to figure out who were the winners of Match 53 and Match 54.
In my case simple IF function will do here:
I can now repeat the same idea for all the remaining games including the Champion.
For the bracket on top (just the same information but better laid out) all I did is make each cell = to its corresponding cells in the Round 16, Quarter Final, Semi Final Tables.
You will notice that in my finished template once again I have created some custom table styles, hidden some columns, disguised other and added the same conditional formatting rules for scores and empty cells just like I did in the Group Stage.
One last thing, you might have noticed that in my template I actually display the Group Standings on the Group Stage page. I did this using a somewhat unknown tool that has been shipping with Excel for a while called the camera tool.
To find this tool:
1. Hit the Office jewel in the top left corner.
2. Select Excel options then click on the customize button.
3. In the Choose Commands from drop-down , select Commands not in Ribbon.
4. Scroll to find “Camera” and double click on it so its automatically added to the Quick Access Toolbar.
Now go ahead and select all your data in the Group Standings sheet and hit the Camera icon. Go back to the Group Stage sheet and select the area for a picture representation of the Group Standings page to show.
Voila, everything on one page!
Congratulations, you made it. Just in time to crack open your favorite beverage, lie on that couch and be fully ready to immerse yourself in the World Cup fever with your handy Excel companion. Truth is there is a dozen more ways this template could have been created, and I am sure our readers will come up with better, more efficient and cooler solutions than mine I definitely would love to see what you create, feel free ping me with your creations at firstname.lastname@example.org).
Here’s the finished template (World Cup 2010 Template.zip)