hb-EXCEL-Logo
English flag   German flag

Website last updated: 23.02.2024

Overview of the result entry and the overall table

New (since 17.02.2024): Crucial help for tournament plan hobbyists ▸see below

 

Tournament schedules - overview

The tournament schedules are suitable for the sports of football, handball, basketball, volleyball and other comparable sports. The overall tables are automatically calculated according to the criteria of points, goal difference, goals scored and, if necessary, direct comparisons.

Free download - no advertising - everything open and changeable - no usage restrictions

Tournament schedule for 1 group with a maximum of 9 participants    (Last update: 09.02.2024) Download .xlsx

 

Tournament schedule for 2 groups with a maximum of 6 participants per group    (Last update: 09.02.2024) Download .xlsx

 

Tournament schedule for 3 groups with a maximum of 6 participants per group    (Last update: 09.02.2024) Download .xlsx

 

Tournament schedule for 4 groups with a maximum of 6 participants per group    (Last update: 09.02.2024) Download .xlsx

 

These are four Excel files (.xlsx) that do not use any programming with Microsoft's own programming language VBA (that means no macros), so that they can also be used with free spreadsheet programs (tested with LibreOffice 7.4.2.3) or on company computers where macros are deactivated for security reasons.

The four tournament schedules at a glance:

Number
of groups
Teams
per group
Number
of fields
Options for
final rounds
1. schedule 1 3 - 9 1 - 4 - - -
2. schedule 2 3 - 6 1 - 6 4
3. schedule 3 3 - 6 1 - 6 4
4. schedule 4 3 - 6 1 - 6 4

The special feature of these tournament schedules (in addition to the high quality of the user interface) is their diversity and flexibility. You can find a lot of tournament schedules on the internet. Almost all of them offer a specific option such as "Tournament schedule for 4 groups of 4 teams with semi-finals and finals on 2 fields". If there are to be 3 playing fields or 5 teams per group, you have to keep looking for a suitable match plan.

The four tournament schedules available here cover most of the common options. For example, if we take the second tournament schedule (for 2 groups), it alone already offers a lot of options: if you only count the options in which the number of teams in both groups is the same and only take into account the options in which there are no time conflicts for the number of playing fields, you come to 64 different ways to hold a tournament.

* * *

Similarities of tournament schedules

All four tournament schedules are highly automated. Each time you enter a new team, the fixtures, the distribution of the individual matches across the playing fields and the calculation of the kick-off times are immediately adjusted. Every time a match result is entered, the final tables are updated immediately.

If you change the number of playing fields, you can immediately see the new distribution of the individual matches across the playing fields and the new kick-off times. If you choose too many playing fields, time overlaps can occur because a certain team cannot play on two playing fields at the same time. In this case, a warning appears and you are shown the team in question and the numbers of the affected matches - not all of them, but the first critical case found. The two affected matches appear in red font.

Planning the tournament - time conflict in the schedule

In the settings you can decide whether 2 or 3 points are credited for a win. You can also choose whether the direct comparison is evaluated before the goal difference (UEFA mode) or whether the direct comparison is only carried out if there is a tie in points, goal difference and goals scored (FIFA mode). In this respect, these tournament schedules are not only suitable for football, but also for handball and other sports.

Especially in UEFA mode, it can be interesting and informative to show the hidden tables with the tables of direct comparisons (table sheets 'DirComparison_A', 'DirComparison_B', etc.) in order to see why a team with the better goal difference is still in a worse place in the table.

The tournament schedules are so flexible that the number of teams per group can also be different. So if a team cancels at short notice, you can definitely play with two groups of five and one group of four. Theoretically, for example, it wouldn't be a problem to play with a group of six, a group of five and a group of three.

In the event of time delays during the tournament (e.g. due to a necessary penalty shootout or additional breaks), the additional time can be entered in the tournament schedules after each match. The kick-off times of all subsequent matches will then be adjusted accordingly.

The four tournament schedules are described in more detail below.

* * *

Tournament schedule for 1 group

Planung des Turniers - Begegnungen und Zeitplan

The names of the teams are first entered on the “Planning” spreadsheet shown here. If at least 3 teams have been entered, the corresponding fixtures will appear automatically - either with or without second legs.

If you would prefer to enter your own fixtures instead of the automatically generated fixtures, you can delete the formulas in the two corresponding columns and enter your own fixtures manually. For detailed instructions on deleting these (array) formulas, see the “Info” worksheet.

Alternatively, you can also generate matchups with a random number generator. There is an Excel file programmed in VBA for this - see
▶ tournament schedule for the Premier League - Create matchups.

In the final table, teams whose ranking is not clear are shown in red. In the event of a draw or a fair play rating, it is possible to clarify the ranking by entering a 'bonus point' for the preferred team.

Handball players have the option of setting the number of points for a win to 2 points on the “Settings” sheet. You can also choose here whether the direct comparison has priority over goal difference and goals scored or not.

An individual tournament schedule can be printed out for each participating team, which includes the kick-off times and playing fields for that team.

* * *

Tournament schedule for 2 groups

The schedule for two groups consists of a preliminary round (group phase) and a final round. There are four options to choose from for the final round:

  1. The two group winners play for places 1 and 2, the two group runners-up play for places 3 and 4, the two third-place teams play for places 5 and 6, etc.
  2. The two group winners and the two group runners-up contest a semi-final, a game for third place and a final. They occupy places 1 to 4 in the final table. The places from 5 downwards are played out in a single knockout game, as in the first option.
  3. The two group winners and the two group runners-up contest a semi-final, a game for third place and a final. They occupy places 1 to 4 in the final table.
  4. The two group winners and the two group runners-up play in a group of four against each other for places 1 to 4. The two third in the group and the two fourth in the group also play in a group of four each against other for places 5 to 8. The fifth and sixth in the group (if available) play for places 9 to 12.
    The number of teams per group may also be different.

The following figure shows the second option for the final round in the case of 2 x 5 teams:

Ansicht Endrunde 2

* * *

Tournament schedule for 3 groups

After the group phase, there are four options for the final round:

  1. The three first in the group play in a group of three for places 1 to 3, the three second in the group play in a group of three for places 4 to 6, the three third in the group play in a group of three for places 7 to 9, etc.
  2. The three group winners, the three group runners-up and the two best third-place teams qualify for the knockout round. This consists of quarter-finals, semi-finals, third place game and final. In this way the first four places are determined.
  3. The three group winners and the best runner-up qualify for the knockout round. This consists of semi-finals, third place game and final. In this way the first four places are determined.
  4. The teams play for places in groups of four (each against each other).
    Final round group E1: The three group winners and the best group runner-up
    Final round group E2: The remaining two runners-up and the two best third-place teams
    Final round group E3: The remaining third-placed team and the three fourth-placed teams
    Final round group E4: The three fifth-placed teams and the best sixth-placed team
    Final round group E5: The two remaining sixth-placed teams
    Group E1 plays for places 1 to 4,
    group E2 plays for places 5 to 8,
    etc.
    This option can also be played with fewer than 3 x 6 teams, for example 3 x 5, 3 x 4 or 3 x 3 teams. The number of teams per group can also be different.

The following figure shows the second option of the final round in the case of 3 x 5 teams:

Ansicht Endrunde 2

* * *

Tournament schedule for 4 groups

The group stage is followed by a final round, for which there are four options:

  1. The four group winners will play a semi-final, a game for third place and a final. They occupy places 1 to 4 in the final table. The four runners-up play in the same way for places 5 to 8, the third in the group play in the same way for places 9 to 12, etc.
  2. The group winners and runners-up qualify for the knockout round, which consists of the quarter-finals, semi-finals, third-place game and final. The four semi-finalists take places 1 to 4 in the final table.
  3. The four group winners qualify for the knockout round, which consists of the semi-finals, third-place game and final. In this way, the occupancy of places 1 to 4 in the final table is determined.
  4. The four group winners play against each other in a group of four for places 1 to 4, the four group runners-up play in the same way for places 5 to 8, ..., the four sixth-placed teams compete equally for places 21 to 24.
    This mode can be played with groups of three, four, five or six. Different group sizes are also no problem.

The following figure shows the first option of the final round in the case of 4 x 5 teams:

Ansicht Endrunde 1

* * *

Multilingualism

Tournament Planning - Matches and Schedule

The languages German and English are available for selection. In addition, however, you can add the translations of any language, so that these tournament schedules can also be adapted to any other language.

* * *

 

For tournament schedule hobbyists:

Help with creating your own tournament schedule - Part 1

Ranking within a group using macros/VBA

If you don't use macros/VBA look at ▸Part 2.

Many would like to develop their own tournament schedule using Excel, but then fail because of the core problem of calculating the ranking order within the individual groups.

In Excel there are the cell functions SUMIF, COUNTIF, VLOOKUP etc. with which you can do beautiful things. Perhaps some tournament schedule excellers have already dreamed that Microsoft would release a GROUP_TABLE function that you type into a cell - with a few parameters, of course - and then, as if by magic, the desired group table appears - bang, it's there - nicely after that FIFA criteria sorted.

Of course Microsoft doesn't do that, the target group is far too small and therefore not lucrative enough.

 

But the new GROUP_TABLE function has been available since February 17, 2024!!

No, no... this isn't an April Fool's joke. Look at it!

You can download the example file here:
▸UDF_Group_table.xlsm [70 kB] (Version 1.0, last updated: 2024-02-20)

You actually enter in the edit line at the top: "=Group_table(...)", with a few parameters, and the complete group table appears - not just with all the usual columns like rank, name, pld, W, D, L, goals, diff and pts, but also optionally the tables of direct comparisons and the cross tables of all match results for first legs and second legs.

But that's not all. With the first parameter (0, 1 or 2) you can choose whether the FIFA regulation (goal difference and goals scored before direct comparison) or the UEFA regulation (direct comparison before goal difference and goals scored) or even (attention basketball players!) the regulations of German Basketball Association (▸§42 of the ▸DBB regulations) is applied.
[Does anyone know a link to the FIBA regulations?]

The idea of programming such a function arose when a head of basketball department at a Baden-Württemberg club asked me for help in implementing the DBB regulations for basketball tournaments using Excel.

The second parameter (usually 2 or 3) allows you to choose the number of points for a win. This depends on the sport, for example 3 points for soccer, 2 points for handball and basketball.

As is usual with an Excel function, every time a game result changes, the table is updated immediately (in a fraction of a second).

A maximum of 9 teams per group are possible. The number of groups is of course unlimited, because Excel doesn't really care how often a formula is entered on a spreadsheet (if the number is less than a hundred).

 

Syntax

= Group_table ( mode; pts_per_win; teams_1; teams_2; goals_1; goals_2; team_names [;tie_breaks] [;penalty_points] )

Parameters Explanation
mode Decides which regulations will be applied
0: FIFA regulations
1: UEFA regulations
2: DBB regulations
pts_per_win Number of points for a win, e.g.
 3 for soccer
 2 for handball and basketball
teams_1 Range with the team names of the home teams
(must be single column)
teams_2 Range with the team names of the away teams
(must be single column)
goals_1 Range with the goals scored by the home teams
(must be single column)
goals_2 Range with the goals scored by the away teams
(must be single column)
team_names Range with the list of all team names in the relevant group
(must be single column)
tie_breaks
(optional)
Range with the tie break points
(If there is a draw after a tie, a plus point can be entered here for the favored team)
penalty_points
(optional)
Range with the penalty points
(Penalty points are entered as a negative number; they will be offset against the number of points achieved)

Example:
=Group_table(0,3,$J$6:$J$41,$L$6:$L$41,$M$6:$M$41,$O$6:$O$41,$C$6:$C$9)
The optional parameters 'tie_breaks' and 'penalty_points' are missing here.

Since it is an array formula, in older Excel versions (up to Excel 2019 inclusive) you must select the range in which the group table should appear before entering the formula, then enter the formula in the editing line and complete with CTRL-SHIFT-ENTER. The formula then appears in curly brackets in the editing line to indicate that it is an array formula. However, the curly brackets must not be entered. They are set automatically by Excel.

You can get an introduction to the topic of 'array formulas' here:
▸Introduction to the topic of 'array formulas'

And this is what the whole thing can look like:

Worksheet '1 x 9' of the sample file

The range Q6:AA14 was preselected before the formula was entered into the edit line.
The formula shown here also includes the two optional parameters, the ranges $D$6:$D$14 and $E$6:$E$14 for the tie break points and penalty points.

The sample file contains two examples: the 1x9 tournament schedule shown above and a 3x4 tournament schedule. This one looks like this:

Worksheet '3 x 4' of the sample file

The formula for group A can be seen in the edit line above. The following formulas were entered for groups B and C:
=Group_table(0,3,$J$6:$J$41,$L$6:$L$41,$M$6:$M$41,$O$6:$O$41,$C$13:$C$16,$D$13:$D$16,$E$13:$E$16)
und
=Group_table(0,3,$J$6:$J$41,$L$6:$L$41,$M$6:$M$41,$O$6:$O$41,$C$20:$C$23,$D$20:$D$23,$E$20:$E$23)

Don't be confused if in the example file on the worksheet '1 x 9' Real Madrid is better placed despite a worse goal difference. In contrast to the image above, UEFA mode is set there and the tables of direct comparisons show why Real Madrid is better placed.

If you want to switch to FIFA mode, follow the steps below:

Real Madrid now falls down to a worse place and the direct comparison tables are empty as there are no more direct comparisons. The goal difference already decides the placements.

If two teams cannot be distinguished because they are tied in points, goal difference, goals scored and direct comparison, their ranking number in the table is the same. Therefore, you have the option of using conditional formatting to make those lines whose neighboring lines have the same rank number appear in red.

Teams with the same rank number

If you enter a bonus point in the 'tie breaks' range as a tie break for one of the two teams, the ranking numbers will be different again because the ranking order has now been clarified. The team with the bonus point is then in the better position within the group table.

 

Size of the range to be selected before entering the formula

If you only want to display the actual group table without the tables of direct comparisons and the cross tables for home and away games, you only select an area with 11 columns and as many rows as there are teams. Then only the first 11 columns of the results table appear (rank, name, pld, W, D, L, GF, hyphen, GA, diff, pts).

These first 11 columns are followed by an empty column as a spacer and another column with the names of the teams. Then there are 4 x 5 columns for the 4 tables with direct comparisons. There are 4 tables because with 9 teams a maximum of four direct comparisons can occur at the same time.

After this fixed number of 33 columns, the two cross tabs for the first and second legs follow. Each of these two crosstabs has as many columns as there are teams. In the case of 6 teams, for example, there would be a total of 33 + 12 = 45 columns. If you want to display the full group table, select a range with 45 columns and 6 rows.

If you do not want to display the 20 columns of direct comparisons, you can hide these columns, for example. But there is an even better method:

You have maximum flexibility if you create the entire table with, for example, 45 columns far to the right in an invisible area using the formula "=Group_table(...)" and then copy the columns that you want to display in the visible area from there. In this way, any selection and order of the desired columns is possible.

Instead of entering a formula of the form "=BF15" in every cell, you can also copy a specific area with a single array formula. Suppose the crosstab with the first leg match results is in the range BF15:BK20 and you want to copy it to the range M15:Q20. Then select the target range M15:Q20, enter the (array) formula "=$BF$15:$BK$20" in the editing line above and complete the entry with CTRL-SHIFT-ENTER.

Copying using array formulas is only possible if there are no connected cells in the target range. If so, you have to copy using ordinary formulas.

Different forms of representation of the group table

In the example file, the Group A table is displayed in full length. A range with 41 columns and 4 rows was preselected.

For the table for group B, an area with 18 columns and 4 rows was preselected; for the table for group C, an area with 11 columns and 4 rows was preselected.

If you want to expand a group table, for example to display the first direct comparison for group C as well as for group B, select the existing group table plus 8 additional columns to the right of it. Then click in the editing line at the top and immediately close the editing mode with the key combination CTRL-SHIFT-ENTER.

If you want to display less, for example only the first 11 columns in group A, follow the following steps:

 

Integrate the GROUP_TABLE function into your own Excel file

In order to integrate the GROUP_TABLE function into an existing Excel file, it is sufficient to copy the module with the name 'UDF_Group_table' into your own project in the macro/VBA area. To do this, open the example file and your own Excel file at the same time, switch to the macro/VBA area with ALT-F11 (see following figure) and drag the name 'UDF_Group_table' with the left mouse button onto your own project name in bold.

Copy modul 'UDF_Group_table'

If you want or have to do without macros/VBA for any reason (e.g. because you use open source software like 'Libre Office Calc'), you won't go away empty-handed here. You can look into the following part 2 of this hobbyist's help. I don't use any macros/VBA in my tournament schedules, so that the tournament schedules not only run on Microsoft Excel, but also with open source software.

* * *

 

For tournament schedule hobbyists:

Help with creating your own tournament schedule - Part 2

Finished module for ranking within a group
without the use of macros/VBA

Many would like to develop their own tournament schedule using Excel, but then fail because of the core problem of calculating the ranking order within the individual groups. In my tournament plans, I use a spreadsheet called 'Calc' (Calc as an abbreviation for Calculation), which carries out these calculations for a specific group. In the case of multiple groups, I create multiple copies of this spreadsheet - one for each group. Only a few formulas then need to be adjusted in these copies so that the respective copy refers to the correct group.

So that someone who doesn't understand how this 'Calc' spreadsheet works can still use it to create their own tournament schedule, I have created an improved special version of it that can be used as a 'black box', so to speak.

A 'black box', such as a coffee machine, requires a certain input (water, coffee powder, milk, pressing a button for various options) and delivers the desired product (the selected type of coffee) as an output. You don't need to know how it works inside. It is the same with the 'Calc' spreadsheet. As input you have to tell it where it can find the group names, game results, tie-break points and penalty points, and as output it then provides the group table in the correct ranking order with points, goal differences, goals scored and the other usual information. You just have to know where the input has to go in and where the output comes out - like with the coffee machine: if you put the coffee cup under the wrong nozzle, the output will go the wrong way.

If you want your tournament schedule to have four groups, create four copies of the 'Calc' spreadsheet and adapt them to the four groups. The special thing is that you don't need any special knowledge of the internals of this spreadsheet for this adjustment. There is a clear and highlighted area on this sheet where input is made and output can be received. This area is the communication interface of this group sorting machine.

This version of the S-Class leaves little to be desired. Any number of groups are possible. Each group can consist of 3 to 9 participants. The number of points for a win (usually 2 or 3) is adjustable. You can choose whether direct comparison has priority over goal difference (UEFA mode) or not (FIFA mode). As an output, the machine not only throws out the group table sorted by rank with the usual information (Pld, W, D, L, Goals, Diff, Pts), but also the cross-tabs with all the game results - one crosstab for the first legs and one for the second legs. Even the tables of direct comparisons are calculated and made available in the output area.

An example file with three groups is intended to show the use of the 'Calc' spreadsheet and make it easier to get started. It can be downloaded here:
▸Calc_Tournament_schedule_1.3.xlsx (last updated: 05.02.2024)

Detailed and understandable instructions are available as a PDF:
▸Calc_Instructions.pdf (last updated: 23.02.2024)

 

The input area

Essentially, you insert formulas into the input area of the 'Calc' spreadsheet that refer to the list of participants in the group in question, the fixtures and the results. These formulas are very simple references, such as "=Schedule!C6". If necessary, you can also insert formulas with the addresses of the input fields for tie-breaks and penalty points. In this way, the group sorting machine is fed with data.

View of the input area

The list of game encounters is so extensive because in this example we play with three groups of four.

 

The output area

The output area of the 'Calc' worksheet contains the sorted group table and - if desired - also the crosstabs of all results as well as the tables of direct comparisons. To make this visible on the tournament schedule, simple formulas of the form "=Calc!B161" are sufficient too. However, it is more advantageous to copy the entire table to the desired location on the tournament schedule using a single array formula. This array formula is then, for example, for the group table "=Calc!$B$160:$K$169". More detailed information can be found in the instructions (see ▸PDF file).

View of the output area

 

The tournament schedule

For example, a tournament schedule with three groups using the 'Calc' sheet might look like this:

View of the tournament schedule

You have complete freedom with the arrangement of the elements. The three tables with the participants in a group can also be positioned next to each other at the top, the game results below and the final tables below. The group tables, the game results and the final tables can also be on different worksheets. There are no restrictions there.

In the example shown here there is still space for more than four group members. If you decide on four, you can also delete the remaining five rows and reduce the group tables accordingly. The same applies to the final tables on the right.

 

Generate fixtures automatically

Finally, a little addition.

Often you have a list of fixtures in the form 1-2, 3-4, 1-3, 2-4, etc. available. Now I know from my own experience that it can be very annoying to create a list of fixtures in the form A1-A2, B1-B2, C1-C2, A2-A4, B2-B4, C2-C4, etc. I thought it would be nice if you could simply specify the number of groups and the number of teams per group, and . . . voilà - you have the list of fixtures.

Create fixtures automatically

All it took was a few lines of VBA program. However, like all Excel files with VBA code, this small VBA program only runs under Microsoft Excel, not with open source spreadsheet programs.

Here is the download link:
▸Fixtures_several_groups.xlsm   (last updated: 31.01.2024)

* * *

Privacy

This website does not store any personal data

 

 

Contact and imprint