2020 update: Everything here is out of date now, and has been replaced with my post on the 2020 version of the worksheet. In there you'll find a download link and full instructions. I'm leaving this article up only because it may be linked to from other places.
To help with my 2,016 miles in 2016 running project, I created an Excel workbook to track my progress. A couple people have asked for the workbook, so here it is…with some caveats and instructions.
First off, this was written for Excel 365, though it should work fine in recent versions of Excel. There is no Numbers version, there is no Google Sheets version…this is it.
Start by downloading the worksheet and opening it in Excel.
The first thing you'll notice is that this is a really ugly workbook. The only thing I spent any time "prettifying" at all was the actual vs. goal chart, as that's the thing I tended to look at most often. The second thing you'll notice, depending on when you open the workbook, is that it appears nothing is working. The formulas will not work properly until January 1st, 2017.
The workbook consists of a number of tabs; here's what each one does.
This is a very simple pace calculator:
Just enter time values in the blue cells, and read out the pace in mph and minutes per mile.
This is the summary page, and also where you enter your year-end mileage goal, your weight (if you're tracking that), and any sick/hurt and travel days. Click the small image below to open a fuller view of the sheet, with a couple days' sample data filled in.
As with the pace calculator, only input data in the blue cells; the others all hold formulas. Also note the area in the weight section in gray; the cells appear empty, but hold a formula to copy the prior month's end weight.
This chart tracks your progress against your year-to-date goal. The goal is represented by the diagonal gray line; each day's distance is added to the prior day's cumulative total, leading to a (hopefully) ever-increasing mountain slope. Here's how my graph looked near the end of 2016:
Jan through Dec
These are the sheets where you'll enter your run data. Here's a mocked-up Jan with a few days' running data (click image for full view).
Most everything on these sheets is optional, with two exceptions: Miles must have a numeric value (or a zero value) for each day of the month that has passed, and Location must be recorded as outside for any runs you want tracked as such. The logic for the "treadmill" vs. "outside" miles on the Summary tab is incredibly basic: It counts anything with the text outside in column H as an outside run. Everything else is an inside run.
The other columns aren't used in any calculations, and can be changed or ignored. I used Entertainment to keep track of what I was watching while on the treadmill, Shoes so I could try to split the miles between my two primary pairs of running shoes, and Notes to mark anything I wanted to remember later on (first half marathon, etc.).
You'll notice that the Jan tab has a green highlgiht; I did that just so I could easily find the current month in the list. (I also moved each month to the left of the tab bar). To change or remove a tab's color, right-click on the tab and choose Tab Color from the pop-up menu.
This is the sausage factory for the chart: Don't look in here unless you want to get sick to your stomach. There's a lot of ugly to make it work, but it does seem to work—the chart should adjust itself even if you don't use 2,017 miles as your goal.
Caveats and cautions
As noted, I built this for my own use and spent no time at all on making it fit for public consumption, so it's really ugly. There may be formula errors (though I tried to test it well before uploading). 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 became a big motivator, as I wanted to keep my "blue mountains" above the sloping gray line!
If you have any questions, please post them or contact me, and I'll do my best to answer them.