Euro 2016 Planner – Excel Template

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.

Euro 2016 Planner Template
Continue reading

Women’s World Cup 2015 Schedule – Excel Spreadsheet

The women’s World Cup is currently underway in Canada and at the time of writing have just finished the group stages of the competition.

This blog post provides a Women’s World Cup Schedule spreadsheet to automatically calculate the group stage rankings, and knockout stage schedule as the results are entered.

Download the Womens World Cup spreadsheet.

This spreadsheet is ready for use. All you need to do is enter the fixtures and results and it will take care of the rest.

What’s in the Workbook

The workbook is made up of 7 worksheets. Some of the worksheets are hidden as they are not needed when using the workbook. You can unhide these sheets by right clicking a sheet tab and selecting Unhide.

The following worksheets can be found in this World Cup schedule.

Teams – Stores the list of teams competing in the competition split into their groups.

Data – The number of points awarded for a win and draw in the groups stages.

Group Fixtures – The fixtures list and results during the group stages of the competition.

Calculations – Calculations for the group stage league table statistics such as number of wins, draw, losses goals for etc. It also calculates the teams league table rankings.

Third Place Rankings Table – Used to calculate the four best third placed teams during the group stages. It also calculates which teams they will face in round 16. This was quite complicated calculations and an explanation of how the schedule works can be seen below.

Group League Tables – The final group stage league tables. The VLOOKUP function is used to pull the information from the Calculations sheet, and use the ranking to list the teams in the correct order.

Knockout Stages – The fixtures and results for the knockout stage of the tournament. Formulas are used to determine which team progresses to the next round as results are entered.

How the Women’s World Cup Schedule Works

The first round is a group stage. Twenty four teams are divided into six groups of four teams. Each team plays one match against the other teams of the group. The group winners and runners-up progress along with the best four third-placed teams.

The ranking for each team in the group is determined by points, then by goal difference and then by goals scored.

Womens World Cup Group Stage Tables

The spreadsheet also contains a table to calculate the best four third-placed teams. They are ranked in their own table using the same criteria.

This is similar to other sports tournament schedules I have created in Excel. You can learn all the skills used for this in our online course.

Scheduling the Best Four Third-Placed Teams

What really made this tournament schedule exciting to create were the tournament regulations for who the best four third-placed teams play in the next round.

These four teams are to be matched with the winners of groups A, B, C and D. However the group winners they play is dependent on which group the best four teams are from.

The image below of the ‘Third Placed Ranking Table’ sheet shows the rank order of the best four third-placed teams and the group winners they are paired against. This schedule is from section 28 of the tournament regulations.

Scheduling the best four third-placed teams

Row 18 displays the top four third-placed teams. A formula was used in column F to then calculate from this the correct schedule to use. This formula made use of the IF, NOT, ISERROR and FIND functions.

This combination was then extracted to a different area of the sheet using INDEX and MATCH. The ‘Knockout Stage’ schedule sheet then pulled the relevant team name across.

FIFA World Cup History – Excel Dashboard

On the eve of the FIFA World Cup 2014 I have created a world cup history dashboard full of fascinating statistics from the previous 19 world cups.

I find creating spreadsheets involving your passions a great way of developing and testing your Excel skills

This dashboard incorporates a few different Excel dashboard techniques which are explained in this post.

The spreadsheet is completely unprotected so all the formulas and code can be explored.

Continue reading

Create a Hangman Game in Excel

I created a hangman game using Excel VBA for fun, and thought I would post it here for all budding Excel VBA learners to look at.

I find creating games, quizzes and sports spreadsheets an enjoyable way of keeping my Excel skills fresh, and to develop them further.

The game asks you to guess a letter to complete the phrase. A hint is provided for a little help, and the phrase will be related to music, sports, people, film or geography.

The spreadsheet is unprotected so you can check out the code and play around it with it further.

The VBA used to create this hangman game includes;

  • Dynamic arrays.
  • Creating a userform.
  • Module scoped variables.
  • Lots of text work including functions such as Left and StrConv.
  • Lots of For Loops, IF statements and a Select Case statement.

Continue reading

Excel Gantt Chart Template for Tracking Project Tasks

A Gantt chart is used to plan and track the progress of a project. Although Excel does not contain a Gantt chart feature (maybe one day), its tabular structure and wealth of tools provide us with the means of creating one.

A Gantt chart can be created in many ways to match your requirements.

Using the Excel Gantt Chart Template

This Excel Gantt chart template uses fixed scheduling on its tasks and provides a timescale of 1 full year from the project start date. To use the template;

  1. Enter the project start date in cell E1.
  2. Enter the ID and name for the tasks of your project.
  3. Enter the task’s estimate start dates and durations.
  4. Enter the % completion to update the chart with the progress of the project.

Continue reading

Christmas Movie Quiz Spreadsheet – Test your Knowledge

Think you know your Christmas movies? Why not try this Christmas movie quiz.

There are 20 movies to recognise from a picture. Select the film from the list below the picture. A running total is kept at the top of the sheet.

Download the Christmas movie quiz spreadsheet.

Continue reading

Lotto 6/55 Number Generator Spreadsheet

Following my Excel lottery number generator video I have had frequent requests for a Lotto 6/55 version. So here it is.

This spreadsheet demonstrates some fantastic Excel techniques, and as always the file is unprotected so that it can all be investigated.

The following Excel features are used:

  • The RAND function is used to generate the random numbers
  • The RANK and COUNTIF functions are used to rank the random numbers from 1 to 55 for retrieval of the balls
  • The Vlookup function is used to retrieve the ball numbers
  • Some VBA is used to perform the formula calculations when a button is clicked (Manual calculation has been chosen over automatic for the workbook)

Download the Lotto 6/55 number generator spreadsheet.

Excel Templates – Mileage Claim Form

Many people use Excel to create forms such as invoices, holiday requests and mileage claim forms. Excels’ various tools to enable calculations, powerful formatting and validation rules make it perfect for form creation.

This post provides a mileage claim form template that demonstrates many useful Excel features in all their glory. Please download and check it out.

Excel templates – Download the mileage claim form template.

The spreadsheet has both worksheet and workbook protection applied for improved functionality. It can easily be unprotected and explored though as no password has been used.

This spreadsheet makes use of the following Excel features;

  1. Data Validation has been used to create a drop down list of offices to select from, and also to select either own car or company car.
  2. A range name is used so that the Data Validation tool can access the list of office on the hidden sheet.
  3. The Vlookup function is used to return the distance in miles of the selected office
  4. If functions are applied to remove the #N/A error message from the Vlookup when the form is blank, and also to control when the calculation of expense is applied.
  5. Workbook protection is used to prevent the hidden sheet from being shown (however there is no password on the download so you can unhide it)
  6. Worksheet protection is used to lock the cells containing formulas and to generate a tab order making the form more user friendly to complete.

Learn Excel Basics – New Baby Checklist Spreadsheet

My wife and I are expecting our first child in just 5 days now. It’s a very exciting time in our household and it has taken a few months to get everything ready for the newborn.

To ensure I have everything I need, I made a checklist (in Excel of course) and worked my way through it.

If you want to learn Excel basics, this spreadsheet features a few fantastic tools that I thought you may be interested in seeing.

Download the new baby checklist.

  1. Check boxes to make it easy for the user to mark of items as they go
  2. Conditional Formatting to cross out the items with a strikethrough when it is checked
  3. A Thermometer Chart to show progress through the list and how close it is to completion (See how to create a thermometer chart in Excel).
  4. The COUNTIF function to count the items as they are checked off

The items in the checklist have come courtesy of www.newparent.com.

Excel Fixtures and League Table Generator

Due to popular demand I have created an Excel fixtures and league table generator.

Following on from my fixture list creator and league table generator files, I was asked to create a combined file to create a fixture list and also generate a league table from results entered.

Download the Excel fixtures and league table generator.

To use the file;

  1. Enter the teams competing and how many times they will play each other on the “Teams” sheet
  2. Enter the results from fixtures on the “fixtures” sheet
  3. View the league table on the “League table” sheet