Cricket Batting Stats in Excel: Batting Average, Strike Rate and Player Rank

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: cricket-batting-stats-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Season figures (made-up values):

Team: 903 runs off 865 balls (strike rate 104.4) with 19 sixes. Shahrukh is the top scorer and has the best average. Zoya has the best strike rate, but she has never been out, so she has no average yet.

2. Build it step by step

Step 1. Season figures

1. In A4:M4 type Player, Matches, Innings, Not outs, Runs, Balls faced, Highest, Fours, Sixes, Average, Strike rate, Runs from boundaries, Rank.
2. Update the yellow columns after every match.

Step 2. Batting average

1. A batting average is runs ÷ number of times out, and times out = innings − not outs.
2. In J5 type =IF(C5-D5=0,"-",E5/(C5-D5)). The IF avoids a #DIV/0! error for a player never out (IF, error types).

Step 3. Strike rate and boundaries

1. In K5 type =E5/F5*100: runs per 100 balls. Shahrukh: 312 ÷ 260 × 100 = 120.0.
2. In L5 type =(H5*4+I5*6)/E5 as %: the share of runs from fours and sixes.

Step 4. Rank

1. In M5 type =RANK(E5,$E$5:$E$10): without a third argument, the highest runs get rank 1 (RANK).

Step 5. Team and leaders

1. In E11 and F11 type =SUM(...), and in K11 =E11/F11*100 for the team strike rate.
2. In C13 type =INDEX(A5:A10,MATCH(MAX(E5:E10),E5:E10,0)) for the top scorer, and the same with columns J and K for best average and best strike rate (INDEX and MATCH). MAX ignores the "-" text.

Step 6. Colours and chart

1. Select K5:K10 > Conditional Formatting > Color Scales (red-yellow-green) (color scales).
2. Select A5:A10 and E5:E10 > Insert > Column (column charts).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Dividing runs by innings. Average uses times out, not innings. Not outs do not count as dismissals.
2. Leaving #DIV/0! for never-out players. Wrap the average in IF and show "-" when times out is 0.
3. Strike rate without ×100. 312 ÷ 260 = 1.2; strike rate is per 100 balls, so ×100 gives 120.
4. Ranking with ascending order. For runs, leave out the third argument so the highest runs get rank 1.
5. Judging by one number. Shahrukh has the runs, Zoya the fastest scoring. Look at average and strike rate together.

5. More real examples

Example 1: Bowling figures. Overs, runs given and wickets: economy = runs ÷ overs and bowling average = runs ÷ wickets.

Example 2: Office league. Add a Team column and SUMIF runs by team for the points table.

Example 3: Kids' summer camp matches. Same sheet for Ruhi's friends, with simpler columns: runs and balls only.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate a batting average in Excel?

Runs ÷ (innings − not outs): =E5/(C5-D5), wrapped in IF for never-out players.

How do I calculate strike rate?

Runs ÷ balls faced × 100: =E5/F5*100.

What if a player was never out?

The average cannot be worked out (division by zero). Show "-" with IF.

How do I rank players by runs?

=RANK(E5,$E$5:$E$10) gives 1 to the highest score.

Does MAX work if a cell has "-"?

Yes. MAX ignores text in a range, so the best average is still found.

7. Quick quiz

Ten questions on this page. Sign in to save your score.