You need two things. A sheet where people type raw results, and a separate sheet that ranks and displays them. Keep those apart and the rest is four formulas: RANK for positions, LARGE with INDEX/MATCH to sort, COUNTIF to turn times into points, SUM to total.
We ran our events on a version of this for years. The template at the bottom is that file, cleaned up. It handles points, times, quantities and finishing positions in one event. It's free and there's no form.
The second half is about where it stops working. That part is usually left out, and it's the part that decides whether you're still trusting it at your fortieth event.
Separate Entry From Display.
This is the one decision that determines whether the file survives a real event.
Sheet 1, Score entry. One row per team, one column per game. Raw numbers only, exactly as they come off the clipboard. No formulas. No formatting.
Sheet 2, Leaderboard. Reads from Sheet 1, converts, ranks, displays. Nobody types here.
Most event spreadsheets fail because one grid was doing both jobs. Someone types a number over a formula halfway through the afternoon. The totals stop matching. You find out during the prize-giving.
Add a third sheet for setup, team names and game names and scoring methods, as soon as you pass four games. It means changing a game name in one place instead of eleven.
Ranking, And The Tie Problem.
Totals in column J. Positions are:
=RANK(J4,$J$4:$J$23)Absolute reference on the range so it doesn't drift when you fill down.
Now the part that catches people. RANK gives you 1, 2, 2, 4. Two teams tied for second and nobody is third. Mathematically correct. A problem on a stage, because two teams are walking up for one trophy.
Decide the tie-break before the event, not during it. If it's "whoever scored higher on the last game", put that game's points in column K:
=RANK(J4,$J$4:$J$23)+SUMPRODUCT(--($J$4:$J$23=J4),--($K$4:$K$23>K4))That counts how many teams tie with this one and beat it on the tie-break, then pushes the position down by that many. 1, 2, 3, 4, no duplicates.
If the answer is "we'll decide on the day", put it in the rules you hand the teams. Deciding a tie-break once you know who it benefits is the fastest way to lose a room.
Sorting The Display.
First place should be at the top. Not team four, because team four is on row four.
Excel 365 has SORT(). If you're the only person who ever opens the file, use it. It's a dynamic array function, and dynamic arrays behave badly in files that get emailed around, opened in older Excel, or edited by three people at once. On event day, use the older pattern. It works everywhere.
Helper column to make every total unique, otherwise MATCH returns the same tied team twice:
=J4+ROW()/100000Far too small to change a ranking. Then pull the teams out in order:
=INDEX($B$4:$B$23,MATCH(LARGE($L$4:$L$23,ROW()-3),$L$4:$L$23,0))ROW()-3 assumes your first result row is row 4, so it evaluates to 1, then 2, then 3 as you fill down. Adjust to your layout.
The Hard Part. Games That Don't Score The Same Way.
Straight points are easy. Real events are not straight points.
A tug of war produces a finishing position. A relay produces a time, lowest wins. A ring toss produces a quantity, highest wins. All three have to land in the same table as a puzzle challenge that produced 240 points.
The approach is rank, then convert. Rank the teams inside that one game. Turn the rank into points on a scale you set once.
Timed game in column C, lowest wins:
=MAX(0,$B$5-COUNTIF(C$4:C$23,"<"&C4)*$B$6)$B$5 is points for first. $B$6 is points lost per place. COUNTIF counts how many teams beat this one, which is the rank minus one. MAX(0,…) stops a big field producing negative scores.
Quantity game, highest wins. Flip the operator:
=MAX(0,$B$5-COUNTIF(C$4:C$23,">"&C4)*$B$6)Finishing position typed straight in:
=MAX(0,$B$5-(C4-1)*$B$6)One formula that reads the method off a setup sheet and handles all four. The setup row steps down per game, so game one reads Setup!$E$8, game two reads Setup!$E$9, and so on. Copy it across without changing that and every game will be scored by game one's method:
=IFERROR(IF('Score entry'!C4="","",
IF(Setup!$E$8="Points",'Score entry'!C4*Setup!$F$8,
IF(Setup!$E$8="Rank",MAX(0,Setup!$B$5-('Score entry'!C4-1)*Setup!$B$6)*Setup!$F$8,
IF(Setup!$E$8="Time",MAX(0,Setup!$B$5-COUNTIF('Score entry'!C$4:C$23,"<"&'Score entry'!C4)*Setup!$B$6)*Setup!$F$8,
IF(Setup!$E$8="Quantity",MAX(0,Setup!$B$5-COUNTIF('Score entry'!C$4:C$23,">"&'Score entry'!C4)*Setup!$B$6)*Setup!$F$8,
""))))),"")It works. It's in the template.
It is also four nested IFs, two COUNTIFs and an IFERROR, repeated across every team and every game. Change how timed games convert and you are editing one cell and copying it across a hundred and sixty. Worth knowing before you build it, not after.
Type Times As Seconds.
Two minutes fourteen point six is 134.6.
Type 02:14.6 and Excel stores it as text or as a time value. COUNTIF with a "<" comparison will ignore it or rank it wrong. It does not error. It produces a leaderboard that is quietly incorrect, which is worse than one that breaks.
Convert on the way in. Whatever you choose, make sure the person keying at 2pm knows which it is.
Multipliers, Bonuses And Penalties.
Multiplier column on the setup sheet, referenced by the conversion formula. That covers "station 4 is double points now".
Build it in from the start even if every game is 1×. Retrofitting a multiplier mid-event means editing every cell.
Keep bonuses and penalties in their own column, added at the total. You want to see that a team lost 20 points for a safety breach, not wonder why their tug of war looks wrong.
One exception, and it matters. On a timed game, a penalty goes on the time, not on the points. A team running 84 seconds with two ten-second penalties is entered as 104, and the 104 is what gets ranked. Deducting points afterwards gives you a different winner, because ranking is relative to the field and a flat points deduction is not. The template has no bonus column, so add one for points games and enter the penalty-adjusted time for timed ones.
Making It Look Like Something.
Three things get you most of the way.
Conditional formatting on the position column, so first, second and third look different. Use a rule, not manual fills, or it breaks the moment the order changes.
Big row heights, big font. Size it for the back of the room, not for your laptop.
Hide the working. Nobody needs to see =INDEX($B$4:$B$23,MATCH(LARGE(...))) on a projector.
The Free Template.
Two versions, because most events do not need the complicated one.
Download the simple leaderboard (.xlsx)
Twelve teams, six games, straight points, and a bonus column. It sorts itself, so first place is always at the top. Three sheets, no nested formulas, and you can see how all of it works in about a minute. If every game on your day produces points, this is the one.
Download the full leaderboard (.xlsx)
Twenty teams. Eight games. Four scoring methods in the same event. Yellow cells are yours to fill, everything else calculates. There's a read-me sheet.
The conversion formula is in it, wired to a dropdown on the setup sheet, so changing a game from Time to Rank is a menu rather than an edit.
One thing above is not in it. The full file ranks with plain RANK and lists teams in entry order with a position column, because the tie-break is a decision about your event rather than a default we can pick for you. The simple file sorts itself and breaks exact ties by entry order, which is an order rather than a rule. Either way, decide your real tie-break before the event.
Free, no form, and you're welcome to rebrand it and use it commercially.
What Actually Goes Wrong.
Everything above works. Here is what happens anyway, in roughly the order you meet it.
Someone breaks the file. Not maliciously. An event manager drags a cell to tidy it up, or deletes a row that looked empty, or types over a formula. The sheet keeps working and keeps producing numbers, and they are wrong. This is the most common failure and it is almost never noticed at the time.
Scores overwrite each other. Two people keying results from two stations into the same file. One of them wins. You do not get told which. This one is not fixable in Excel, and it is the main reason the Google Sheets version of this guide exists.
It isn't live. The word "live" in the search that brought you here is doing hopeful work. Whoever has the file open is the only person who can see the standings. Everyone else is waiting for a screenshot in a group chat. OneDrive co-authoring gets closer, with a lag and the occasional conflict dialog, which is not what you want on a ballroom screen.
The client cuts a game. Not adds one. Cuts one, at 11am, because the morning ran long. Every number in the file was pre-programmed around six games and now there are five. The weightings are wrong, the conversions are wrong, and someone is rebuilding formulas while the teams walk to the next station.
It takes people to check it. Ours took several. One person keying, one person checking, and the checking is not optional, because of everything above. At ten events in a week that is a real headcount, spent on arithmetic.
And once, the wrong team came first.
That is the one that matters. A team took several games, clearly, in front of everyone. The standings put someone else on top, because a number had been tallied wrong somewhere in the middle of the day. The file did not error. The maths was internally consistent. It was just wrong.
We caught it early. It still cost us a replacement prize, because by then the result had been read out and the only right thing to do was buy the trophy twice.
That is the honest version of the risk. Not that you will hand the wrong team a trophy and never know. That you will catch it, and it will still cost you money, and you will spend the rest of the day wondering what else is in there.
So Should You Use A Spreadsheet?
If you're running two or three teams, don't overthink it. A whiteboard is fine. Genuinely. Some events do not have a scoring problem and adding software to them makes them worse.
If you run a handful of events a year, the template above is enough, and per-event software is a cost and a thing to learn for a problem you have twice.
The maths changes with volume. We run 200 to 300 events a year, sometimes ten in a week, occasionally with five thousand people on site, and at that scale a spreadsheet is not free. It costs an event manager's afternoon per event, plus a second person checking, plus the risk that one of ten events this week has a number wrong in it that nobody catches.
That's the point at which we stopped patching ours and built GamesTally Score. Facilitators enter results from their phones at the station. On tournament match scoring, if two people save the same match at once, the second gets an error rather than silently overwriting the first. That check is being extended to the other scoring modes. Every change is logged with who made it and when. The standings go on the client's screen in the client's branding. USD 4,000, once, rather than per event.
Get to that volume first. Until then, take the template.
Related: Live leaderboard in Google Sheets · Putting a leaderboard on the client screen with PowerPoint · How to run a lucky draw in Excel