Create an Excel World Cup 2010 Planner

Data - Points awarded for a win and draw are stored on here, and a list of all the teams involved in the competition and the group they are in.

Group Fixture List - This sheet is where the group stage results should be entered. The sheet has been protected so that users can easily navigate the sheet for data entry using the Tab key.

Calculations - Calculations for the group tables are done on this sheet. The formulas below are done to determine each team's position within the group

• COUNT function and COUNTIF function are used to add the number of games, wins, draws and losses of each team


• The RANK function is used to give each nation a table position based on their points total


• The SUMPRODUCT function is then used to assess the goal difference and then goals scored of teams with the same number of points, and then the same goal difference


• A points ranking + goal difference ranking + goal scored ranking formula is used to get a team's final group table position

Group A-H Sheets - These worksheets automatically update with the results entered onto the Group Fixtures List sheet. They display the results and group table.

• The IF function is used to test if a fixtures result has been entered yet, and if it has it is automatically entered into the cell


• The VLOOKUP function is used to complete each group table with each team and their statistics such as number of wins, number of losses, goals scored and conceded etc


• The MIN function is used with VLOOKUP to retrieve the statistics of the best team in the group (ranked number 1)


• The SMALL function is used with VLOOKUP to retrieve the statistics of the 2nd, 3rd and 4th ranked teams in the group

Group Statistics - A summary of all group results and tables. This data is automatically updated from the individual group sheets. The Excel Camera Tool is used to insert a live picture of the group sheet data.

Knockout Stages - Results for the knockout stages are entered onto this sheet. Each team reaching the knockout stages are automatically updated on here from the group tables on the group sheets.

• Linked cells are used to pull across the teams for the last 16 fixtures


• Nested IF functions are used to pull across the teams for the quarters to the final

Download the Excel World Cup 2010 Planner.

Every sheet of the planner is protected, including 2 hidden sheets (Calculations and Data), but no password has been used and all the formulas are visible so the inner workings can be seen.

See Also:

Create an Excel Football League Table

Create an Excel football league table. Enter the results to the fixtures and watch the table calculate the new league positions and statistics.

Create an Excel FA Cup Draw

Create an FA cup draw using Excel. Click the buttons to run the draw for each round.

Microsoft Excel Training

Microsoft Excel training guides and quizzes. All versions and levels of Excel covered.

Software training videos

Hundreds of online software training videos covering Excel, Word, Photoshop, DreamWeaver, Flash and many others.

Excel Formulas E-book

E-book explaining Excel functions in simple terms. Fast track your way to success with formulas.