At the end of 2016, I first posted about my run tracking Excel workbook. That first version was crude, but functional, and I used it to track every mile of my "2,016 miles in 2016" running goal. I posted a minor revision for 2017, then made some major updates for 2018. When 2019 rolled around, I made a few more changes, which I released by way of a note added to the 2018 post.
Now that 2020 is here—I know, it's a bit past January, sorry!—I've made yet more changes, and have decided it's time to replace both of the previous posts with one new all-in-one post. Here you'll find a link to the latest version of the workbook, as well as full instructions on how to use it.
=SUMIF(Jan!H10:H40,"outside",Jan!D10:D40)+SUMIF(Feb!H10:H37,"outside",Feb!D10:D38)+SUMIF(Mar!H10:H40,"outside",Mar!D10:D40)+SUMIF(Apr!H10:H39,"outside",Apr!D10:D39)+SUMIF(May!H10:H40,"outside",May!D10:D40)+SUMIF(Jun!H10:H39,"outside",Jun!D10:D39)+SUMIF(Jul!H10:H40,"outside",Jul!D10:D40)+SUMIF(Aug!H10:H40,"outside",Aug!D10:D40)+SUMIF(Sep!H10:H39,"outside",Sep!D10:D39)+SUMIF(Oct!H10:H40,"outside",Oct!D10:D40)+SUMIF(Nov!H10:H39,"outside",Nov!D10:D39)+SUMIF(Dec!H10:H40,"outside",Dec!D10:D40)
If you already know what you're doing with my workbook and just want the newest version, grab it here. If you're curious, here's what's new in this version:
- There's no more macro-enabled version, which was used to quickly delete all entries. Instead, keep the original file intact, and just duplicate the master each year for a new year's tracking. This is much simpler (and safer) than the prior macro-based way to start a new year.
- I worked around an issue where not entering a run distance—even if it was zero—for each day of the year would break the progress chart.
- Fixed a few layout issues (like "0" appearing for a shoe name if you didn't enter three pairs of shoes), revised a few formulas, and just generally tried to clean things up.
Note: This workbook will probably not function correctly in Numbers without some modifications—see my note to "Tom" in the 2018 version's blog post for the details. I have not tested that fix with this version of the workbook, however, so you're pretty much on your own if you're using Numbers.
If you've never used my run tracker and would like to know more about it, keep reading…
As a start, here's a portion of the Overall sheet of the workbook:
In addition to the overview, there are worksheets for each month, as well as a chart that tracks your progress to date against your full-year goal. It's quite easy to use, and for me, I like being able to see daily progress against my running goal.
To start using my workbook, first you need to download it and then expand the compressed file. After expansion, keep the original file somewhere safe and unmodified. Now create a duplicate of that file, and use that duplicate for the current year's run tracking.
When you open the workbook, it should be on the Overall worksheet; if not, click that worksheet in the list at the bottom of the window. There are a total of 15 worksheets in the workbook; here's a summary of each:
- Overall: Here's where you input the year you're tracking, your mileage goal, your shoe information, and your weight. You can also calculate pace if you know a distance and duration, and enter any hurt/sick or travel days. Down towards the bottom are some summary statistics on the lengths of your runs, and how many miles you've put on each of your shoes.
- Chart: The chart displays your YTD progress against your goal. The goal is the diagonal line, which is simply plotting the required number of miles per day. As you run, your distances will start filling the chart, hopefully tracking along or above the goal line.
- Jan through Dec: These are the sheets where you enter your actual run information.
- chart_data: This is where the ugly work required to make the chart happens. Don't touch this sheet.
Here's how to use each of these worksheets to track your running…
The first thing you need to do is enter some things the workbook needs to know…
Year to track: Enter the year that you'd like to track with the workbook. It is critical that you input this as a number (just type in the actual year), not as text. Once there's a value here, all formulas will adjust, including adding a day in February for leap year, if necessary.
I've tried to set up all the formulas so that your data shouldn't change (i.e. "days left" stops counting) once the year has passed, so you should be able to just archive the year's workbook as is, without fear that things will change over time. If you spot a cell that's not working properly when the year changes, please let me know.
Goal: How many miles you want to run in the defined year. Note that the workbook is not really set up for kilometers, but they're just numbers—in theory, if you enter everything in kilometers and switch any labels around, things should "just work," but I haven't tested this.
Shoes: You can track mileage on up to three different pair of shoes. If you have more than that, well, you're out of luck :). If you have only one or two pairs, delete the unneeded entries (in the blue area only).
Aside: The Pace Calculator
The Pace Calculator lives to the right of the variable inputs, and it's used to convert time-and-distance values into both miles per hour and minutes per mile pace info—the miles per hour value is what's used on each month's run tracking worksheet. Just enter your distance and time (in hours, minutes, and seconds), and it will spit out the results.
Note: The calculator relies on some formulas that are hidden in column M, which is set to zero width. Do not delete the column, or modify any of its formulas (some are used elsewhere, too).
Immediately below the inputs and calculator is an overview of the full year, which shows your year-to-date progress along with a month-by-month mileage and weight tracker. Here's what it looks like, using some fictitious data against a full-year goal of 900 miles (click to see the full image):
The Progress as of section shows you exactly what you've done against your goal—days left to go, and your progress against the year-to-date goal—as of today's date. Below that, a table shows each month's goal, and your progress (current month) or results (past months) against that goal. There's also space on the right to enter your weight twice a month, if you're interested in tracking that, too.
Finally, below the table is a full-year outlooking, showing you how many miles are left to the goal, how many days are left to get there, then comparing the average miles per day required to meet the goal versus your actual year to date average miles per day. The final value here is the adjusted required miles per day to exactly meet your goal—if you're currently ahead of goal, this will be lower than your target miles per day, and if you're behind on your goal, this figure will be higher than your target miles per day.
The bottom section of the sheet contains some summary statistics, plus two rows where you can input data (again, click for the large version):
If you enter values in the blue cells, those sick/hurt and travel days will be excluded from any of the "per day" calculations. Below that are a number of statistics for days you ran out of possible running days, total miles, inside vs. outside miles, and then a chart showing runs by distance—I used kilometers for this chart, as I like running 10Ks, so it made sense to me. Finally, the total distance you ran on each pair of shoes is shown—this is useful knowledge as the general advice is to replace shoes somewhere between 300 and 500 miles.
Not much to say about this worksheet; it displays a year-to-date chart of your progress against your goals:
The box at the top shows year-to-date totals, the diagonal line is a running total of your daily mileage goal, and the blue filled-in section are your daily runs.
There is no user-changeable data on this worksheet.
These worksheets are where you'll enter your daily run information:
At the top, the Progress, Results, and Shoes boxes do not contain any user-modifiable data—they simply summarize the month's running activity by progress against goals, days run (and 10K+ days), and miles put on each of your defined shoes.
Below that, Please do not modify the Date or Day columns. If you do, pretty much everything will stop working. Enter each day's mileage in the Miles column, then any other data you'd like to track in the associated columns. With the exception of the Location (outside will mark a run as outside, everything else is assumed indoors) and Shoes (text entered must match your defined shoes) columns, nothing else affects any calculations or is displayed elsewhere.
At the start of the year, the worksheets are arranged in month-order tabs at the bottom of the workbook window:
As months go by, I drag-and-drop completed months to the end of the list (and change their color by right-clicking on the tab and using the Tab Color menu item), so that the current month is always close to the left end of the workbook:
You do not have to enter a Miles value for each day in the month. Any blank Miles cells that are prior to the current date will be assumed to be zero. If you go back later and fill in values, everything will adjust as expected.
There's nothing you can do on this worksheet except break the chart, so please, don't change anything :). There are a lot of really ugly formulas to make everything on the chart work right, and this is where they live.
I originally created this workbook for my own use, and although it seems to work well and correctly for me, no warranty is expressed or implied. There may be formula errors (though I've fixed many that I've caught over the years). This spreadsheet will not help you physically get out and run—that’s all on you! But it may help you stay motivated to run—for me, looking at the chart each day is a big motivator, as I always want to keep the chart's "blue mountain" above its sloping gray line!
If you have any questions, please post them or contact me, and I’ll do my best to answer them.
I know you love numbers. You may enjoy this - https://veloviewer.com
hi, this is a great excel...i like it very much.
Was looking into the excel if it was possible to shift somehow the starting date.
we all know people dont start in Jan and would be good to shift the graph and data to monitor
Also. there is a small bug in your calcs from cell Overall!F49
Funny, I just found that one the other day; about to upload an updated version.
thanks;
-rob.
That would be ... very complicated. I might look into it later this year, in getting ready for next year, but it will take reworking the graph quite a bit, and adding a new start date field.
-rob.
Been looking to do something like this for a while. Love this, much better than me trying to figure it out! However, I do have one question: why do you put distance in miles in some places and km in other? That's the only slight criticism I have, having everything in miles would be awesome! Also I wish I could do pace based off of per mile instead of mph, but once again I love the layout and ease of it!
Dylan:
The only section that's in KM is the summary on the main page, and that's because I was focused on 10Ks when I originally made the worksheet. It'd be pretty easy to change it to miles - if you expand columns L through P, you'll see that column M holds the ranges for the table to the left. Change those to whatever you'd like in miles (you'll have to change the text labels in column B as well, as they're just typed.
Next, the forumulas in D54 to D63 reference column J on the chart_data worksheet; change those to all reference column F instead, and you'll be working in miles.
As for the pace, that data doesn't go anywhere at all—it's just there for reference. So change the header on each month's tab to mins/mile or whatever, and you're done, because whatever you put here won't go elsewhere. One reason I like MPH, though, is that Excel will want to convert "7:13" into a time, so you'll have to enter it as a text ('7:15) to make that not happen.
-rob.
Oh my gosh awesome! I’ll do that, thanks for the tip! This is awesome!
Great tool! I will be using this to keep track on my trainings for Ultramarathons. Would love to be able to integrate elevation for the runs as it's an important factor for training. Any advice would be appreciated! Thank you!
If elevation is just one number, the easiest thing to do would be to put it in the notes. As long as that's all you put in notes, then you could even do calculations on the values. If you need it more integrated, it's going to be some work—you'll need to add a column for Elevation to each month's input page, and then figure out how/if you want to integrate summary data for elevation on the summary page.
regards;
-rob.
Can't seem to get the chart working, something I'm doing wrong? :)
Chris:
It should "just work" as long as you got the updated version I linked last April. If you did, and it's not working, can you email me (my first name at this domain) a copy?
-rob.
Rob - Thanks for your reply. Discovered my mistake, please remove duplicate above.
Thank you for doing this!!!
Hey Rob, what would be your best recommendation for incorporating interval training into this spreadsheet? I could just total the distances and list the average pace; however, keeping track of paces for different distances (i.e. 100 meter sprints versus 400 meter sprints) could be valuable in my opinion.
Honestly, I'm not sure. You could add formulas that summarize things based on matching specific distances to create a summary table of the various views? (I never did much interval training, so I don't have a lot of direct experience to draw on.)
-rob.
Hey! Thanks for creating this :) I am using it for 2021. Considering there are only three months left this year, I was wondering if my shoe mileage can be carried over into the 2022 spreadsheet from this one once January hits. Thanks!
Probably the easiest way to do that is to take the 2021 total for the shoe(s) and just put that amount in the beginning of the formula at cell C70:
=530.5+Jan!J5+Feb!J5+Mar!J5+Apr!J5+May!J5+Jun!J5+Jul!J5+Aug!J5+Sep!J5+Oct!J5+Nov!J5+Dec!J5
That'll then give a running total, if you'll pardon the pun.
-rob.
Thank you, Rob!
Thank you, Rob! I did have a question for you. Would it be difficult to add a Walking Miles Column on the spreadsheet? I ask only because after my runs or on days off I walk a few miles and I wanted to have those incorporated as well. I appreciate all you've done. Happy New Year.
To add and fully integrate walking activity would be a pretty major undertaking. The monthly page would need to be laid out again, and figuring out how to include the data in the Overall and Chart tabs would be quite complicated.
And honestly, I just don't have the time to spend on it that it would require, so I have two alternative solutions to consider.
First, you could add the data yourself on each monthly tracking page, in column L. Just add a Walked column header, and then on days you walk, you can enter a value there.
Find somewhere in the header area (H7, maybe?) to total the Walking column. Then on the Overall page, you could write a formula to add up each month so you have a total. Or if you're feeling adventurous, you could add a column to the table showing miles and weight.
That'd be the easiest way to actually get the walking data in the same spreadsheet. It wouldn't be fully integrated, but it'd be there.
The second option: Use a second workbook. Duplicate the worksheet, rename it to Walking 2022 (or whatever), and just enter your walking data there. The two data sets won't be fully integrated, but you'd have full data on both runs and walks.
If I had the time, I'd tackle this, as I do more walking than running nowadays—or I'll mix a 5K run and a 5K walk in one workout—but I just intermix the two on my workbook, as I'm past caring about my pace :). But I just don't have the time, as it'd be a major redo.
-rob.
I appreciate you taking the time to answer my question. I'm not really familiar with how to use excel, so I'm going to go the easiest route and go with your second option, at least it seems simpler. Again thank you, and Happy New Years!
Comments are closed.