Getting ready for a big race? This can often take months of training, and the best way to get the most out of it is to keep a close eye on your performance.
You can use Excel to create a spreadsheet where you can record all your runs for future reference. Also, if you configure it 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 will show you how to use the INDEX formula and a basic menu. Down menu to create interactive Excel charts. This simple process will turn static graphics into dynamic information hubs. Read More
Here's how to use Excel to improve your marathon training.
First, you need to set up the basics of your spreadsheet. Open Excel and create a new document. Then add your column headers.
I would recommend adding Date , run , Distance , Time , and Done? at least. The other columns are optional, and in fact you can swap them out for other things you'd like to track if you wish.
However, it might be beneficial to keep track of things like how much you had to eat before you went out or how much water you drank. Let's Produce Charts How to Make a Chart or Table in Excel How to Make a Chart or Table in Excel Excel has robust display features, which make it easy to create powerful charts and tables in Excel. You can even use Excel to create auto-updating charts! Read More
Then fill in the columns marked Date, Race, and Distance with the appropriate information from your training plan. Since this spreadsheet will help you train for a marathon, we're assuming you have some sort of plan for how far you'll run each day on your way to 26.1 miles. If you don't have that information handy, there are several training plans available online.
Remember, this is for your use, so feel free to add more or less information as you see fit.
One of the main purposes of this document is to give a sense of progress towards the great race. To do this, we'll use 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 step at a time. Read more.
Go to the Developer tab and select the Checkbox .
Place this in the Done? column.
Get rid of the text tag, then right-click the checkbox and click Format Control .
On the next screen, do a Cell Link to the cell it is in, which in my case is H2 .
Click the check box. You should see TRUE appear in the cell behind it. I've made each row double to make it easier to click these checkboxes accurately.
Reading TRUE or FALSE is a handy way to verify that it works, but it doesn't need to actually display, so change the font color in each cell to white. Repeat this process for all cells in that particular column. After changing the font color in a cell, you can use the format painter (paint bucket icon) or Ctrl + Y (Repeat) to apply it to any other cell.
Every time you complete a race, you will mark it. 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 a temporary race day row (adding every run to my schedule could clutter the screenshots).
Next, we're going to use a function to do some behind-the-scenes work. Choose a cell that is out of the way and enter the following code:
= COUNTIF (H2: H11, FALSO)I am also adding a text cell that reads DAYS UNTIL RACE. You should end up with something like this..
This formula will count however many unchecked boxes are in the specified area, calculating how many days are left for our race.
Now, we're going to add some data so we can set up our auto-update tables. How to create self-standing Excel tables in three easy steps. How to create self-adjusting Excel tables in three easy steps. How to make your Excel charts update automatically. Just add new data and watch it automatically appear on your chart. It is fast and easy. Read more . This is pretty straightforward, but there are a few things to keep in mind to get our times recorded correctly.
When you enter a time in Excel, make sure you use the format hh:mm:ss. So, if your first run was logged at 28 minutes and 32 seconds, you would enter:
00:28:32Keeping this consistent will prevent difficulties with your charts later on. I'm going to enter some dummy data to use for now.
Once you've added some data, highlight the entire table and press Ctrl + T to turn it into a table. You may need to change the color of the TRUE and FALSE text cells to ensure they stay hidden.
Now that your data has been formatted correctly 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 to make your charts cool and engaging in Excel 2016. Read More
Next, highlight the Date and Calories columns, go to the Insert and select a Line graphic of the Cards section.
Go to Graphic 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 into the field. Horizontal axis labels (Category) and add it to Series2 , then do the same for 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 usage data to the same table, so I'll change both E's to F's).
We set up our chart to get the source data from cells we haven't filled in yet, which means that when we add new data, it will be recorded in the chart.
Obviously these lines are quite inconsistent as they take into account several different execution lengths. You may want to limit them to a regular run of a set distance or the ever-increasing runs that get you closer to 26.1 miles.
Finally, we will add a quick way to evaluate our individual performances. Throughout my marathon training plan, I'm going to go for a 5-mile run. I'm going to set up a way to check if 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 will require more work behind the scenes. Choose a cell that is out of the way and enter the following formula:
= AVERAGEIF (C2: C11, "5 millas", D2: D11)This is what is happening 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. Modify specific cell references if your spreadsheet is set up differently or has more rows.
Then highlight all the rows that will eventually have times for a 5-mile distance recorded in them, even if they are blank at the moment. Go to Home> Conditional formatting> Cell highlighting rules> More than .
Change the cell reference to where 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 color the average time blank to hide it or make it part of your reading section.
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 adjust things to your liking.
Above is my final version. It's not full of bells and whistles, in fact it's quite spartan. But it gets the job done and is easy to read. If you want to add more information, that's certainly an option. I have a couple of reads on the right hand side that introduce some basic dashboard-like features 3 Excel Dashboard Tips You Need to Try 3 Excel Dashboard Tips You Need to Try Use our Excel Dashboard Tips to Organize and display the data A complex spreadsheet. With your dashboard tidy, you'll see key data points at a glance. Read more.
It wouldn't be too hard to add similar readings for more of this type of data if you were logging it. Heart rate would be one of those additions. Think of this as a basic version that you can add. What will you focus on as you work towards your race? By modifying this structure, you should be able to keep track of that as well.
Do you have tips on how to use an Excel spreadsheet to prepare for a marathon or half marathon? Having trouble with any of the steps in this guide?