Gearing up for a big race? This can often take months of training, and the best way to make the most of it is to keep a close eye on your performance.
You can use Excel to create a spreadsheet where you can log all your runs for future reference. What’s more, if you set it up correctly, it will create graphs and other readouts that are useful for planning out your runs, plotting your journey toward race day, and looking back on your performance over the entire training period.
Here’s how to use Excel to supercharge your marathon training.
Setting Up Your Document
First, you need to set up the basics of your spreadsheet. Open up Excel and create a new document. Then, add your column headers.
I would recommend adding Date, Run, Distance, Time, and Done? at the very least. The other columns are optional, and in fact, you can swap them out for other things you might like to track if you so desire.
However, it might be beneficial to keep a record of things like how much you had to eat before you went out, or how much water you drank. We’re going to produce graphs that tie these metrics to your performance, so you might be able to discern things about the impact they have on your ability to run.
Next, fill out the columns marked Date, Run, and Distance with the appropriate information from your training plan. Since this spreadsheet is going to help you train for a marathon, we’re assuming that you have some kind of plan on how far you’re going to run each day on your way to 26.1 miles. If you don’t have that information to hand, there are various training plans available online.
Remember, this is for your usage, so feel free to add more or less information as you see fit.
Adding Check Boxes
One of the main purposes of this document is to give a sense of progress toward the big race. To do that, we’re going to employ checkboxes.
Head to the Developer tab and select the Check Box.
Place this in the Done? column.
Get rid of the text label, then right-click the checkbox and click Format Control.
On the next screen, make a Cell link to the cell that it is placed in, which in my case is H2.
Click the checkbox. You should see that TRUE appears in the cell behind it. I’ve made each row twice as high in order to make it easier to click these checkboxes accurately.
The TRUE or FALSE readout is a useful way to check that it’s working, but we don’t need it to actually display, so turn the font color in each cell white. Repeat this process for all the cells in that particular column. After changing the font color on one cell, you can use the format painter (paint bucket icon) or Ctrl + Y (repeat) to apply it to any other cells.
Add a Countdown
Each time you complete a run, you’re going to check it off. With that in mind, you can use your checkboxes to create a countdown to race day. For the purposes of this section, I’m going to add in a temporary race day row (adding every single run to my schedule would clutter the screenshots).
Next, we’re going to use a function to do a little behind-the-scenes work. Choose a cell that’s out of the way, and enter the following code:
I’m also adding a text cell that reads DAYS UNTIL THE RACE. You should end up with something like this.
This formula will count however many unticked boxes are in the specified area, thereby calculating how many days away our race is.
Now, we’re going to add some data so that we can set up our self-updating charts. This is fairly straightforward, but there are a few things to keep in mind so that our times are recorded properly.
When you enter a time into Excel, make sure you use hh:mm:ss format. So, if your first run was logged at 28 minutes and 32 seconds, you would input:
Keeping this consistent will avoid any difficulties with your charts later on. I’m going to enter in some dummy data to use for the time being.
Once you’ve added some data, highlight the whole table and press Ctrl + T to make it into a table. You may need to recolor the TRUE and FALSE text cells to ensure they stay hidden.
Now that your data has been properly formatted, you can set about adding some visualizations.
Next, highlight the Date and Calories columns, head to the Insert tab, and select a Line graph from the Charts section.
Go to the Chart Design tab and click Select Data.
First, use the dropdown menu to select Connect data points with line.
Next, add a new series using the plus button. Copy and paste the text in the field Horizontal (Category) axis labels and add it to Series2, then do the same with the text in the field labeled Y values. Change the references in the latter to match the desired column. (I want to add my water consumption data to the same chart, so I’m going to change both E’s to F’s.)
We set up our chart to source data from cells we haven’t filled yet, which means that when we add new data, it will be recorded on the graph.
Obviously, these lines are pretty inconsistent since they take into account various different run lengths. You might want to limit them to either a regular run of a set distance or the steadily increasing runs taking you closer to 26.1 miles.
Implementing Conditional Formatting
Finally, we’re going to add a quick way to assess our individual performances. Throughout my marathon training plan, I’m going to be going on 5-mile runs. I’m going to set up a way to check whether these runs are better or worse than my average at a glance.
This is going to require some more behind-the-scenes work. Choose a cell that’s out of the way, and enter the following formula:
=AVERAGEIF(C2:C11, "5 miles", D2:D11)
Here’s what’s going on above. Excel checks the first range for the specified string, which is 5 miles. It then averages the corresponding cells in the second range, which are the times for those runs. Amend the specific cell references if your spreadsheet is set up differently, or has more rows.
Next, highlight all the rows that will eventually have times for a 5-mile distance recorded in them, even if they’re blank at present. Head to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
Change the cell reference to wherever you put the formula.
Now, do the same but this time choose Less Than from the Highlight Cells Rules menu and make sure to choose a different color for the highlight. You can either color the average time in white to hide it or make it a part of your readout section.
Feel Free to Customize
The techniques discussed above are just a starting point. Everyone’s preparations for race day will be different, so don’t be afraid to experiment, and tweak things to your liking.
Above is my final version. It’s not full of bells and whistles — in fact, it’s pretty spartan. But it gets the job done, and it’s easy to read. If you wanted to add more information, that’s certainly an option. I’ve got a couple of readouts to the right-hand side that introduce some basic dashboard-like functionality.
It wouldn’t be too difficult to add similar readouts for more data of this kind if you were logging it. Heart rate would be one such addition. Think of this as a basic version that you can add to. What are you going to focus on as you work toward your race? By modifying this structure, you should be able to track that, too.
Do you have tips on how to use an Excel spreadsheet to prepare for a marathon or half-marathon? Are you having trouble with any of the steps in this guide?