How to chart a tennis match with an Excel Spreadsheet

The return of live sports has been a welcome development during the COVID lockdown. We’ve enjoyed watching the US Open and seeing the new batch of young players that are going deep into the tournament.

Today’s post is for those parents who are dreaming of developing their kids into the next Roger Federer, Rafael Nadal, or more realistically (insert name of solid high school player who is able to get recruited into a highly ranked college).

The inspiration for this post comes from http://thetennisparentsbible.com/, a very in-depth (and intimidating) book on junior tennis development. One of the sections of the book is on match charting, or effectively keeping stats for your child’s match. Some of the benefits that author Frank Giampaolo cite are systematically evaluating performance, identifying strengths and weaknesses, and looking busy so you don’t have to talk to somewhat neurotic tennis parents (okay, I added the last one).

Spreadsheet Inputs

This spreadsheet is a bit tricky because of the amount of complexity that one could try for. In theory, every stroke could be a data point, including where on the court it was hit from, what spin it was hit with, and where on the court it was hit to. I’ll keep it relatively simple for now…

Basically we’ll keep track of who won the point, how many shots were in the rally, and how the point was won (winner or error, forehand or backhand, volley or groundstroke or serve). This system was inspired by someone named “Knotwilg” commenting on the TennisTroll Channel.

image

Here’s how our spreadsheet looks so far:

image

Here’s how I’d fill it out. If Player 1 hits an ace on the first point, I’d put a “W” into cell B6. Then if in the next point Player 2 dumps a forehand volley into the net, I’d put an “E” into cell J7. There will be lots of borderline situations (half volleys? forced error or winner? is a tweener a forehand or a backhand?), but out of simplicity we’ll keep it to just these five categories and winner or error, and let the user decide how to categorize the borderline cases.

Spreadsheet Outputs

Now that we have some raw data of how all the points in a match were won or lost, we can create some summary statistics to give us some insights into how and why the match was won or lost.

I’ll insert some rows above the match chart to calculate some of these summary statistics. I’m sticking to the basics here – first we’ll count Player 1’s winners:

image

The other formulas follow similar logic and break it out by strokes.  We then add percentages to the winners versus errors.

Finally, we add some data on the rally length, including the overall average and the average rally length for when each player wins to give a sense of whether a player does better drawing out rallies or ending them faster. This includes adding an intermediate step of figuring out which player won the point:

image

image

And that’s pretty much it for this simple version of a tennis match tracker. In theory, you could customize for statistics you think are most important (like where on the court each player was to calculate things like net points won, etc). Also, one could build something that tracks matches over time. All great exercises for building your spreadsheet skills!

Download the spreadsheet here: Tennis Match Tracker

Leave a Reply

Your email address will not be published. Required fields are marked *