The Robservatory

Robservations on everything…


An even more improved run-tracking Excel workbook

2019 update: I’ve uploaded new files (in one zip archive this time) with a few changes and fixes. These files are also set up as “master” files: The idea is you duplicate one, rename it for the current year, then use it. When the next year rolls around, repeat the process. This way, you don’t have to use the macro-enabled version to delete data at each new year. Download the new files.

About two years ago, I created a basic-but-functional run tracking workbook (created in Excel). It worked well, and helped me through my 2,016 mile year in 2016. I didn’t run nearly as much in 2017 (on purpose), but 2018 is upon us, and I’m going to up my mileage this year—probably not to 2,018, though!

In preparing this year’s version of the workbook, I addressed a few things that bugged me about the first one: It was ugly, changing years was difficult, and it was ugly. It was also really ugly. Did I mention it was ugly? Anyway, here’s what I’ve changed with the new version:

  • Years are now easily handled; just input the year you wish to track, and the workbook does the rest, including leap years.
  • All run data can be deleted with one button click—and yes, there’s a confirmation first. (Requires macro version of workbook.)
  • The pace calculator is no longer a separate worksheet; it’s integrated into the Overall worksheet.
  • It’s not nearly as ugly as it was before—layout is improved, gridlines are gone, tables are cleaned up, etc.

As noted, there are two versions of the workbook—one contains a macro that can erase the run data from each monthly worksheet, the other does not contain that macro. This is something you’ll only do once a year, but it’s much easier with the macro version.

To begin, download the zip archive of both files. Expand the archive, and duplicate the macro or non-macro version (as you wish; the macro version needs a newer version of Excel) for the current year. When the next year rolls around, repeat the process.

The instructions haven’t changed much from the original post, so refer to that for assistance if needed. Here’s what’s different:

Input year: The workbook will now handle any year, so there’s a cell to fill with the year you’d like to track. Input this as a number, not as text. Once input, all formulas will adjust, including adding a day in February for leap year, if necessary. Also, I’ve set it up 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. I need to test this more, though, so there may be updates forthcoming.

Clear run data: If you have the macro version of the workbook, at the bottom of the Overall worksheet you’ll see the big bold ugly button at right. This will do exactly what it says: Delete all your run data. It will warn you first, and give you a chance to cancel. But be warned, there is no undo if you run this and then want your data back!

While dangerous, this button makes it very easy to set up for a new year of running: Copy the current year’s workbook, rename the copy for next year, open it, change the input year, then click the big red button.

Pace calculator: As noted, it’s been integrated into the Overall worksheet, near the top. Fill in the blue-shaded cells, and you’ll get your pace in both minutes per mile and miles per hour. Sorry, no metric support.

Note that there are some behind-the-scenes calculations (some quite ugly) that need to be done to make this work. Those calculations—and others—reside in columns M and N, which are currently set to zero width (but not hidden via the menus). If you delete those columns, pretty much everything will stop working. So don’t do that.

Location: As before, I check only for the word “outside” in this column on each month’s worksheet. If it’s not there, the run is assumed to be inside, as I do a lot of treadmill running.

Shoes: You can track up to three pair of shoes (because that’s all I use and had room for in the worksheets). Just give each one a name on the Overall chart, and they’ll carry over to the monthly worksheets, and you’ll be able to track miles run per shoe.

That’s about it…I’m pretty sure I’ll have to fix a few things as the year progresses, given how much I changed. But I like this version much more than I did the first.


Add a Comment
    1. You may want to download the updated files – there are a couple bug and layout fixes.


    1. I think all you need to do is to change “Miles” to “KMs” wherever you see it—the calculations for pace don’t care what units they’re working in, and everything else is just a number. So it should work just fine to enter KMs everywhere; changing the labels will just make it also look right.

      I won’t be doing this, though, as I don’t have the time and don’t need the KM version myself.


    1. It’s a manual thing; just increment each time you need one or more. I’ve thought about having a way to code them into the worksheet itself, and maybe I’ll do that for next year.


Leave a Reply

The Robservatory © 2018 • Privacy Policy Built from the Frontier theme