How to Make a Round-Robin Tournament Table in Excel

Running a small tournament is fun until the paperwork starts. Who plays whom in round four? Is it goal difference or goals scored that breaks a tie? And somebody always asks for the table five minutes after the last whistle. A spreadsheet answers all of that and recalculates after every result.

This guide covers the schedule, the points formulas and the tiebreakers for a round-robin, where every team plays every other team once.

How many matches and rounds

With n teams, the number of matches is n x (n - 1) / 2. Eight teams play 28 matches. If n is even, there are n - 1 rounds with n / 2 matches each. If n is odd, add a "bye" so someone rests every round, and you get n rounds.

Check your venue and time before you commit. Ten teams with a 20-minute slot each is 15 hours of play.

The circle method for the schedule

The standard way to build a fair schedule is the circle method. Fix one team in place and rotate the others around it.

1. Write the teams in two rows. For six teams: top row 1, 2, 3; bottom row 6, 5, 4.
2. Pair teams that are above each other: 1-6, 2-5, 3-4. That is round one.
3. Keep team 1 fixed and rotate the others one place clockwise. Pair again.
4. Repeat until you have n - 1 rounds.

Alternate home and away for the fixed team so it is not always on the same side.

Points and standings

Decide the points before the first match and write them down: a common choice is 3 for a win, 1 for a draw and 0 for a loss. Put those values in cells, not inside formulas, so rule changes are easy.

Put results in a match list with columns Team 1, Team 2, Goals 1 and Goals 2. For a team in A2, wins are counted with SUMPRODUCT:

=SUMPRODUCT((Matches!B:B=A2)*(Matches!D:D>Matches!E:E))+SUMPRODUCT((Matches!C:C=A2)*(Matches!E:E>Matches!D:D))

That is rather long, so many people use helper columns on the match sheet, which is easier to audit. Points are =Wins*WinPts+Draws*DrawPts.

Tiebreakers

Teams level on points need a rule. A common order is:

1. Goal difference
2. Goals scored
3. Head-to-head result (if agreed in your rules)

To rank in one formula without complicated sorting, build a score key: =Points*10000+(GD+500)*100+GoalsFor. Then position is =RANK(key,keyRange). The numbers must fit your scale, so adjust the multipliers if scores can be very high.

Worked example

Four teams, 3-1-0 points:

Common mistakes

• Counting blank scores as 0-0. Test that a match has both scores before counting it as played.
• Changing the rules halfway through. Publish the tiebreakers before the first match.
• Uneven team counts without a bye.
• Hard-coding points in formulas.

Ready-made template: round-robin table

The template does the schedule and the table. It is paid, works in Excel 2007 and newer and in LibreOffice, and has no macros. It suits football, table tennis, volleyball and school competitions, and chess too, if you enter results in score format.

• Teams: the list of participants, up to 12 teams.
• Matches: a schedule by round, built from the number of teams, with score entry.
• Standings: games, wins, draws, losses, points and positions.
• Settings: points for a win, a draw and a loss.

Ties on points are resolved by goal difference and then goals scored. It is a good fit for PE teachers, coaches and organizers of competitions and sports clubs. Adjust the points to your competition rules.

FAQ

Should the schedule be single or double round-robin?

Single is a good fit for one-day or short events. Double (home and away) is fairer but doubles the number of matches.

What if two teams are still level after all tiebreakers?

Agree a final rule in advance: a playoff, a coin toss or a shared position.

How do I handle a forfeit?

Decide the recorded score ahead of time, such as 3-0, and enter it like any other result.

How do I share the table?

Print the standings sheet or save it as PDF after each round.