World Cup soccer in Excel: filtering with slicer objects
- Jul. 05, 2014
 | Example files with this article: |
Introduction
For all those soccer fans out there… the World Cup 2014 in Brazil is bound to get started! No, I'm not telling you any news there. And I set up a template in Excel to follow the games. I won't be the first to do this. But as I was planning on whipping up an article on how to use slicers in Excel, well… why not combine the two? Below you will find my template and explanations on the use of slicers. Microsoft introduced the slicer objects in Excel 2010 for pivot tables. Since Excel 2013 we can use slicers on formatted data tables. Therefore, to use the file at the top of the page, you need Excel 2013. See here with Excel MVP Jan Karel Pieterse if you want read up on slicers in Excel.
Below is a screenshot of the template. The theme is pink, but I have no objection if you change the theme to what you prefer. Not that I prefer pink that much, it just happened to give a good outcome, in my opinion. On the left, you see the (filtered) data table with 8 columns/fields. Two fields have been purposefully hidden, users do not need to see all fields to use the application correctly.
-
The columns are as follows:
- My time: the time that the game starts in my local (Brussels) time. See below.
- Your time: the time that the game starts in your local time. Since I cannot (and certainly don't try to) determine the timezone you are in, see below on how to change the times.
- Date (hidden): the date without the time, based on column "Your time". A slicer is created for this column.
- Group (hidden): a mix of group letters (A to H), and abbrevations of the later stages.
- Game: obviously.
- Score: at least as obvious.
- Location: where is the game being played?
- Stage: an improved version of the Group column. See below for details.
Timezone offset
Setting the timezone offset to match your time zone: you can simply press the green spinbutton to increase or decrease the offset. While doing so, you will notice that in column B the hours (and possibly the dates as well) will change to accommodate. I used a simple function to add or subtract the chosen hours:
=[@[My time]] + TimeZone_Offset / 24
Every cell in column B has the same formula. In the past, I blogged about data tables and their structured referencing for formulas. Here is an example of that. For every row, take the value from the field "My time" (Wim's time) and add the number of hours (divided by 24 since for Excel, 1 day of 24 hours equals a value of 1). The green spinner is linked to a cell which is named TimeZone_Offset. My current time is CEST, which is UTC/GMT + 2 hours.
Slicer objects
-
On the screenshot above, you can see the 3 slicer objects:
- Stage
- Location
- Date
As you can imagine, Slicer objects allow you to filter the data in the Table. It's as simple as that, yet it gives us a clear advantage over existing functionalities like AutoFilter and Advanced Filter. People are usually very graphical. If you can offer boxes/rectangles/shapes where they can click and see the immediate result of the filter action, people get excited! Immediately after a filter action or a change in filters or even turning off a filter, Excel shows the correct rows in the Table based on all 3 slicers. Slicing (only) on Group H shows us that the 4 teams play in 6 different locations, on 3 different days (dates are based on "Your time", not mine). See the slicer above. The filter action on Group H invalidates a whole lot of other filter choices: 7 locations in Brazil are not used for Group H, and a lot of dates are not possible anymore/ are not associated with matches in Group H. Continuing to filter on, say, June 17, means that only 2 games in Group H remain:
- Belgium - Algeria
- Russia - Korea Republic
Only Belo Horizonte and Cuiaba remain as locations. And so on, and so on, I guess you get the picture :-) Note that you can multi-select slicer items by dragging the mouse, or even use the Ctrl key to select non-contiguous slicer items! Clearing a filter can be done with the "funnel and red cross icon" in the top-right corner of the slicer. Slicer items without data are greyed out, and can even be hidden. For example, I hide the slicer items with no data for the slicer "Date".
Inserting a slicer is done by selecting a cell in the Data table, then using Insert > Slicer in the Filters group. If you think that the column "Score" is a good candidate for a new slicer:
Slicer object properties
When the slicer is shown in the worksheet, use the "Slicer Tools" to format the slicer and/or change its settings. A right-click on the slicer also reveals interesting properties. Slicer settings (through right-click on the slicer) brings up this dialog screen:
- Displaying a header is optional.
- You can sort the slicer items in a number of ways, see below.
- You can "Hide items with no data". That's what I did for the Date slicer but not for Stage and Location.
- If you do not hide them, there are still some properties to be determined, if you want to. Specifically, greying out items with no data, and showing the items without data last.
The second area where you will set a number of options for slicers, is this (again, through right-click on the slicer object, then choose "Size and Properties…", or throught the Excel ribbon on top):
It's all pretty self-explanatory. The 2 near the cursor is the number of columns: this is often set to higher than 1 to conserve space.
Sorting slicer items
As depicted above, slicer items can be sorted in alphabetical order (ascending or descending). This is appropriate for Locations and Dates in our case. However, sorting the Stage slicer items alphabetically is not very useful. Therefore, one can also sort through a Custom list. For information about how to create a Custom list, see here with yet another Excel MVP, Debra Dalgleish. So, create the unique items of your slicer on a blank sheet (Gr. A, …, Gr. H, Round Of 16, Quarter-Finals, …, Final), sort the cells and create the new Custom list. After that, indicate in the slicer properties that you want to sort using a Custom list. Done! You first get the group stages, then Round of 16, Quarter-Finals, up until the final! Very convenient if you ask me. For your information: fields in pivot tables can also be sorted through custom lists.
Hiding slicers
If you want to hide a slicer, you first go to the "HOME" tab of the ribbon, group "Editing" and then click on the eye of the relevant object:
A lookup function for Stages
For the sake of completeness, inspect the formula of the Stage column in the data table. An interesting example of the LOOKUP function in Excel using arrays:
=LOOKUP(
TRIM(LEFT([@Group],3)),
{"3P","A","B","C","D","E","F","FIN","G","H","QF","R16","SF"},
{"Third Place","Gr. A","Gr. B","Gr. C","Gr. D","Gr. E","Gr. F","Final","Gr. G","Gr. H","Quarter-Finals","Round Of 16","Semi-Finals"})
)
Alternatively, using 1 array with in the array each time "couples of items":
=LOOKUP(
TRIM(LEFT([@Group],3)),
{"3P";"Third Place","A";"Gr. A","B";"Gr. B","C";"Gr. C","D";"Gr. D","E";"Gr. E","F";"Gr. F","FIN";"Final",
"G";"Gr. G","H";"Gr. H","QF";"Qarter-Finals","R16";"Round Of 16","SF";"Semi-Finals"})
)
These LOOKUP functions take away the need for many (nested) IF formulas. See here. Make sure you sort the lookup values ascending: either sort them in the first array if you have 2 arrays, either sort the first item of each "couple" in the solution with 1 array.
An additional benefit of slicers, certainly during the setup of the spreadsheet, is that they can help spotting typos and other errors. For example, mistyping a Location in Brazil will show up in the slicer items:
If you then select Fortalezza with double "z" in the Location slicer, you get the record(s) that was(were) mistyped, and hence you can easily correct.
Exercise for the reader
-
If you want to excel and improve your ability to work with data tables and slicers, here is an exercise.
- Add a column to the table to see if a game is finished or not. For example based on a comparison of NOW() and [@[Your time]] + 1.75 / 24 (the latter is the starting time plus 1 hour and 45 minutes)
- Create a slicer to filter on games that were finished, or not finished, or both.
Enjoy!
Enjoy the tournament and take care!
