Box league spreadsheet template for your club

Most box leagues start on a spreadsheet. It costs nothing, everybody knows how to open one, and for a league of twenty players it is often all a club needs. A well-built spreadsheet can run a league for years. A badly built one has the organiser up every Sunday night, working out who is top of box three.

The difference is almost entirely in the layout. This post covers what a box league spreadsheet needs, how to set it up so the table works itself out, and the mistakes that break one. If you would rather not build it, the free box league spreadsheet is all of this, ready to type into.

What a box league spreadsheet needs#

• The players in each box, typed once.
• A results grid: the players down the side and across the top, with one cell where each pair meets.
• A result in each cell from a fixed list, so a mistyped score cannot break the table.
• The table: played, won, lost, sets and points, worked out from the grid.
• The order, worked out too, so nobody sorts the table by hand.
• The points, and how many go up and down, in one place, so changing them changes every box.

For how to choose box sizes, points and tie-breaks in the first place, see how to run a tennis box league and the box league rules template.

A results grid, one tab per box#

Every box league sheet a club pins on the noticeboard is a grid, and a spreadsheet should be too. Put each box on its own tab, with the names down the side and the same names across the top. Where two players meet, that cell holds their result.

Each match has two cells, one either side of the diagonal. Make the cells above the diagonal the ones you type into, from the row player’s side. Let the ones below fill themselves in with the same result turned round. A 2-1 for Priya against Jo above the diagonal is a 1-2 for Jo against Priya below it.

If each pair plays twice, both halves become places to type: the first match above the diagonal and the second below it, each from the row player’s side.

Type each name once, in the column down the side, and let the names across the top refer to it. Type “Sam” once and he cannot become “Sam “ or “sam” somewhere else and be counted as two people.

A drop-down for every result#

Most broken box league spreadsheets were broken by a typed score: “6-4 6-2” in one cell, “2 sets to 1” in the next, “W” in a third. No formula can count all three.

The answer is data validation, which every spreadsheet has. Give each result cell a drop-down of the results there are, such as 2-0, 2-1, 1-2, 0-2 and a walkover either way, and refuse anything else. The cell then holds one of six values, and a formula can count each of them.

Colour them too, with conditional formatting: a win green, a loss having won a set amber, a loss red. A glance at the grid then shows who is having a good round.

Let the table work itself out#

Every number in the table is a count along the player’s row of the grid:

• Played is the number of cells in the row with a result in them.
• Won is the number that start with a 2, plus walkovers won.
• Points are wins times the points for a win, plus losses having won a set times theirs, plus the other losses.
• Sets add up from the same cells: a 2-1 is two sets won and one lost.

Then the order. Give each player a key made of their points, then their wins over the players level with them, then their set difference. Work out each player’s place from the keys. A second table can then read the players out in place order, so the table sorts itself. Nobody ever sorts it by hand, which matters because of the first mistake below.

The mistakes that break a box league spreadsheet#

Sorting the table by hand. It moves the rows, and every formula that pointed at a row now points at somebody else. Let a formula put the table in order instead.

The same name typed twice. “Jo Smith” in the names and “Jo smith” in a match is two different players to a spreadsheet. Type names once and refer to them.

Merged cells. They look tidy and break formulas, filters and copying. Use a bold heading instead.

A file nobody else can open. A spreadsheet on one laptop is a league that stops when that laptop is away. Keep it in Google Sheets or a shared folder, with a second organiser who can edit it.

Everybody able to edit everything. Players who can type their own scores can also delete the table. Share a view-only link with the players, and keep editing to the organisers.

No copy of last round. Before you start the next round, save a copy with the date in its name. It is the record of who went up and who went down, and the only way back if something goes wrong.

Sharing it with the players#

A table nobody sees does not get matches played. Post a picture of each box in the league’s group chat every week or two, or share a view-only link to the file. Name the matches still to play, kindly, and they will get booked. How to level up your tennis box league has more on keeping the tables in front of people.

A spreadsheet runs a league well until one person typing every score becomes the job.

When the spreadsheet stops being enough#

A spreadsheet works while one organiser can comfortably type every score. The signs it has stopped working are usually the same:

• The league passes forty or fifty players, and updating it takes an evening a week.
• Players ask whether their score has gone in yet. The table is only as current as the last time somebody typed.
• The league stops when the organiser goes on holiday.
• Arranging matches happens somewhere else, in a dozen private messages nobody can see.
• Promotion and relegation takes a whole evening at the end of every round.

None of these is the spreadsheet’s fault. It cannot let players enter their own scores without letting them change everything else.

Getting the free template#

The free box league spreadsheet has four boxes of up to eight players, each a results grid with a drop-down for every result. The table sorts itself, says who goes up and who goes down, and one Settings tab sets the points for every box. It opens in Excel, Google Sheets and LibreOffice. There is no sign-up and nothing to install.

Would rather not use a spreadsheet at all? The Sheet is the same box league as a free page that works in a phone’s browser, even offline. And when the league outgrows both, Clayside runs it. Players arrange their own matches and enter their own scores, and every table is live for the whole club.