The Robservatory

Robservations on everything…

 

A much-improved run-tracking Excel workbook

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 either the macro version or the non-macro version; to use the macro version, you’ll need a newer version of Excel that supports macros.

Jan 2 2018: If you downloaded these workbooks before today, please re-download them. I found and fixed a small error—as in it didn’t work at all—in the chart that didn’t become apparent until I’d added some real data.

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.

2 Comments

Add a Comment

Leave a Reply

The Robservatory © 2017 Built from the Frontier theme