How to Create a Marathon Training Plan With Excel
Pinterest Whatsapp
Advertisement

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 How to Create Interactive Excel Charts With the INDEX Formula How to Create Interactive Excel Charts With the INDEX Formula We'll show you how to use the INDEX formula and a basic drop-down menu to create interactive Excel charts. This simple process will turn static charts into dynamic information hubs. Read More 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.

setting up your marathon training plan

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 How to Make a Graph or Chart in Excel How to Make a Graph or Chart in Excel Excel has robust visualization features, making it easy to create powerful graphs and charts in Excel. You can even use Excel to create self-updating graphs! Read More 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.

setting up your marathon training plan

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 How to Create a Checklist in Excel How to Create a Checklist in Excel A checklist is a good way to keep track of tasks or processes. We'll show you how to make a checklist in Excel, one simple step at a time. Read More .

Head to the Developer tab and select the Check Box.

adding checkboxes to your marathon training plan

Place this in the Done? column.

adding checkboxes in your marathon training plan

Get rid of the text label, then right-click the checkbox and click Format Control.

checkbox format control in excel

On the next screen, make a Cell link to the cell that it is placed in, which in my case is H2.

cell link in excel

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.

adding checkboxes in your marathon training plan

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:

=COUNTIF(H2:H11, FALSE)

I’m also adding a text cell that reads DAYS UNTIL THE RACE. You should end up with something like this.

add a countdown to your marathon training plan

This formula will count however many unticked boxes are in the specified area, thereby calculating how many days away our race is.

Recording Runs

Now, we’re going to add some data so that we can set up our self-updating charts How to Create Self-Updating Excel Charts in Three Easy Steps How to Create Self-Updating Excel Charts in Three Easy Steps We'll show you how to make your Excel charts self-update. Just add new data and watch how they automatically show up in your graph. It's quick and easy. Read More . 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:

00:28:32

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.

recolor certain cells in excel

Now that your data has been properly formatted 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions matter. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know about making your charts attractive and engaging in Excel 2016. Read More , you can set about adding some visualizations.

Making Charts

Next, highlight the Date and Calories columns, head to the Insert tab, and select a Line graph from the Charts section.

making charts for your marathon training plan

Go to the Chart Design tab and click Select Data.

chart design in excel

First, use the dropdown menu to select Connect data points with line.

connect chart data points in excel

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.)

making charts for your marathon training plan

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.

making charts for your marathon training plan

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 How to Calculate Basic Statistics in Excel: A Beginner's Guide How to Calculate Basic Statistics in Excel: A Beginner's Guide Microsoft Excel can do statistics! You can calculate percentages, averages, standard deviation, standard error, and student's T-tests. Read More 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.

using conditional formatting in your marathon training plan

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.

using conditional formatting in your marathon training plan

Change the cell reference to wherever you put the formula.

conditional formatting rules in excel

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.

using conditional formatting in your marathon training plan

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.

customize your marathon training plan in excel

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 3 Excel Dashboard Tips You Have to Try 3 Excel Dashboard Tips You Have to Try Use our Excel dashboard tips to organize and show off data from a complex spreadsheet. With your neat dashboard, you'll see key data points at a glance. Read More .

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?

Explore more about: Microsoft Excel, Microsoft Office Tips.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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

  1. csm2
    February 19, 2018 at 1:33 pm

    Couple of years ago I created a similar training chart using LibreOffice Calc. It included weather information for the run and comments on how I felt during the run. However, I found that after about 30 days worth of entries, the spreadsheet got unwieldy so I switched the application to LibreOffice Base. I am able to generate graphs and reports based on any field I choose.