1. Sheet Layout
Use one tab per innings. Trying to fit both innings on one sheet means every formula needs range-juggling and you will get it wrong at some point during a live match.
Rough vertical layout:
- Rows 1–3: match header (teams, date, venue, toss)
- Rows 5–17: batting table (11 batters plus header and totals)
- Row 19: extras
- Row 21: team total
- Rows 23–31: bowling table
- Row 33: balance check
2. Batting Table + Strike Rate
Header row at row 5, batters from row 6 down. Columns:
| Cell | Column | Content |
|---|---|---|
| A | Batter | Typed name |
| B | How out | Typed |
| C | Bowler | Typed, blank for run outs |
| D | Runs | Typed |
| E | Balls | Typed |
| F | 4s | Typed |
| G | 6s | Typed |
| H | SR | Formula |
Strike rate formula
In H6, then fill down:
The IFERROR wrapper matters. Without it every empty row shows #DIV/0! and the sheet looks broken before the match has started. Format the column to one decimal place.
Batting total
In D17:
This is runs off the bat only — it deliberately excludes extras, which is why the balance check later works.
3. Extras and Team Total
Row 19, five separate cells so you can see at a glance where the extras came from. Label them in row 18.
| Cell | Extra |
|---|---|
| B19 | Byes |
| C19 | Leg byes |
| D19 | Wides |
| E19 | No balls |
| F19 | Penalty |
| G19 | =SUM(B19:F19) |
Team total in D21:
4. Bowling Table + the Overs Problem
This is where spreadsheet score sheets usually go wrong.
Cricket overs are not decimal numbers. An over figure of 3.4 means three overs and four balls — that is 22 balls, not 3.67 overs. If you calculate economy rate by dividing runs by the displayed over figure, every part-over gives a wrong answer. Convert overs to balls first, then work from balls.
Bowling header at row 23, bowlers from row 24. Columns A bowler, B overs, C maidens, D runs, E wickets, F economy.
Converting overs to balls
Use a helper column — put it in H and hide it later. In H24:
That takes 3.4, splits it into 3 overs (18 balls) plus 4 balls, and returns 22.
Economy rate
Economy is runs per six balls. In F24:
Now a bowler with 3.4-0-22-1 shows an economy of exactly 6.00, which is correct. Dividing by 3.4 instead would have given 6.47.
Bowling totals
That last one reassembles total balls back into cricket over notation, so 121 balls displays as 20.1 rather than 20.17.
5. The Balance Check
The one formula that makes the whole thing worth building. In A33:
And a second check on the bowling side. Team total should equal all bowlers' runs plus byes plus leg byes:
Add conditional formatting so the cell turns red when it says CHECK. Now the sheet tells you the moment something is wrong, instead of you discovering it at the end of the innings with ninety deliveries to re-audit.
6. Setting It Up to Print
You will still want a paper backup — phones die, laptops are awkward at square leg.
- Page Layout → Orientation → Landscape
- Scale to Fit → Width: 1 page, height automatic
- Set print area to A1:H33 so the helper column stays off the printout
- Hide column H before printing, or set its width to zero
What a Spreadsheet Still Cannot Do
The formulas above handle totals, strike rates and economy. What they cannot do is track the match while it happens — required run rate as the chase develops, current partnership, whether this is the bowler's best spell of the season.
They also cannot be shared live. Someone not at the ground gets your spreadsheet after the match, if at all. And across a tournament you are back to manually consolidating files to build a points table.
A spreadsheet is a genuine step up from paper for a single match. For a season, ball-by-ball scoring in an app keeps the same records automatically and produces a scorecard link you can share on WhatsApp while the players are still walking off.