Questionably Texan

Home » marathon training » Going Old School With A Training Log (Spreadsheet Style)

Going Old School With A Training Log (Spreadsheet Style)

Since I started running mid-2008, I’ve kept logs of pretty much every single run.  The problem is that those logs are spread between a slew of different websites: Nikeplus, Buckeyeoutdoors, DailyMile, Training Peaks, and Garmin Connect.  Those are just the ones I remember, there might be more.  I have yet to find one that satisfies what I’m looking for.  TrainingPeaks was the best match, but they are changing their website and a couple of the analysis features that I use seem to be going away with the free version (I primarily used the custom date ranges to show me my mileage over a specific time period).  Their paid version has a lot of graphs and charts, but at $129 for a year, it’s just not worth it to me.

What does my ideal training log look like?  I want to be able to:

  • graph weekly and monthly mileage for running, cycling, etc.
  • easily differentiate between commuting miles on the bike and “regular” cycling
  • compare my pace/speed throughout the calendar year
  • separate out pool swimming from open water swimming from tethered swimming (in my backyard pool)
  • see how the pace of my lunch runs changes over the course of a year
  • do all of the above for free

The more I thought about it, the more I realized that a (relatively) simple Excel spreadsheet fits exactly what I’m looking for.  I can have options for all of the different cycling that I do, all of the different swimming that I hope to do(!), and have an endless supply of graphs and charts by taking advantage of Pivot Tables in Excel.

Screen shot 2014-01-09 at 9.15.50 PM

My new “custom” training log. Not shown is the total hours for each activity (far right) and the sheets containing graphs and monthly summaries.

I figured that there’s no better time to start a new training log system than the beginning of the year.  So I sat down and created my ideal spreadsheet.  I can always change it later if I find I’m missing items, and I haven’t finished up with all of the graphs, but I can at least log data in the manner I want.  Here’s what the main data tab looks like:
All of my runs will be in the same grouping, but I can differentiate what type of run it was.  Pace is auto-calculated.  Later, I can use a Pivot Table to find my average pace of all lunch runs or how many miles I ran with one of our dogs.

Bike Commuting miles have their own column, and I’ll be interested to see how my speed changes throughout the year (my commuting speed is heavily affected by traffic, but if I have a rolling average, it should be relatively accurate of any long-term trends).  I also created a column for regular cycling and another for time spent on the trainer.

I have no good data for my swimming last year, but that’s ok since there really wasn’t much swimming to speak of.  But this year I plan on putting a lot more effort into my swim (and I picked up this book to help me with that).  I’m sure I’ll be geeking out over my swim data before too long.

I may add some columns for heart rate data (since I almost always run/bike with a heart rate monitor), but I’m still thinking through whether or not I want to go through that work.  I don’t think I’d regret it though, since I could easily compare my average January running pace and average heart rate with the same in August.  Ok, I just convinced myself that I should add columns for heart rate data.  Tomorrow.  Maybe.  If I have the energy.

Once I’m done tweeking the spreadsheet, I’ll probably throw it up on Google Docs so that I can access it from home, work, and my phone.  Probably the biggest benefit I’ll see is that I will now “own” my data.  No need to rely on websites and other for-profit services to keep track of my data for me.

What would your ideal training log look like?
Is there something that I’m missing that you think I should include?

Advertisements

13 Comments

  1. mtbader says:

    Looks like a pretty kick ass training log to me!

  2. Great post, inspiring and eye opening.

  3. I love this! I think you have all of your bases covered. That is a lot of columns and a lot of data! Maybe you should try and sell the idea?! 🙂

  4. I’ve used all of those methods, too! My data is all over the place. I use TrainingPeaks (the paid version) now, but I’m working on developing a custom database at the moment. The guy who’s working with me on it wrote a portion of the code in a programming language I’m not very fluent in, so it’s temporarily delayed. Interesting that you want to differentiate commuting miles from other miles–I might try to add something like that into mine!

    • I’m sure that a database would be much more powerful and efficient than a spreadsheet, but I’ve got no programming language knowledge (nor do I really care to learn something relatively simple like Microsoft Access). So, I’m stuck with Excel. But there isn’t anything that I’ve thought of yet that I want in a training log (besides a slicker user interface) that I haven’t been able to come up with a solution for in Excel.

      I’ve got a couple of reasons for differentiating commuting mileage:
      1) my average commuting speed gets slowed way down because of traffic and traffic lights. When I’m moving, I’m generally going over 20mph but my overall average is generally closer to 16mph (14 on the way home since traffic is significantly heavier). I just don’t want the commuting miles to drag down my regular cycling average speed!
      2) Last year, I think I rode less than 200 non-commuting miles (100 of those were on a single day). I’d like to see how much more cycling I do this year.
      3) I’d like an easy way to see how many times per week I’m actually commuting over the course of the year. It goes in waves where I’m commuting every day for a couple of weeks, then nothing for a week, then three days, …

      Instead of a separate column for bike commuting, I could have done something similar to my running column and just added the word “commute” to that day. Then I could use a Pivot Table to pull out the commuting date. But having a separate column makes it easier for me to see at a glance what my total commuting mileage looks like.

  5. I think Excel is great for things like this! You can customize it exactly the way you need to see the information you need. I totally understand your not wanting commuting to change your bike average for the negative too. As for your swim book, I recognize that title! I know you have the ebook, but can we get you the spiral bound version?

    • Since I’m not swimming yet (our pool temp was 3C/38f, and I’m still focusing on the ultra), I haven’t run into any issues with just having the ebook. I’ll let you know if it presents a problem for me later though. And I plan on posting a proper review once I’ve actually delved into it!

  6. Spreadsheets are awesome. I did a similar thing for my budget after researching and even trying some different online services. It just works and actually takes me less time then having to log in and figure stuff out on a site or computer program.

  7. jnkmiles.org says:

    I have a TrainingPeaks acct through my coach, but still use my own spreadsheets to track trends. Its the only way to easily see things….BUT, that being said….have you looked at Strava for your cycling and running data? They have some pretty cool analytical tools…it’s taking a lot to keep me form signing up!!

  8. Chris says:

    So I’m late to this party, but I’m currently working on something similar to what is going on here. I use Garmin for all my runs, but not rides. I’ve been training for the past few months and decided I wanted to geek out over the training cycle. I’ve exported my Garmin Activity for the past year (when I got a pair of shoes) to track mileage of my shoes, as well as isolate many of the activities. Not all rides are included over the past year, however I have used Garmin on each ride since I’ve been in this training cycle.
    My pivot tables now show numbers with a bar scale for random things like miles per week on trails or road. The max distance of road and trail. The combined miles of all running. Likewise for cycling, road bike or mountain bike. Hours spent cumulatively of rides and runs, etc. Percent difference of miles from week to week. I’ve made a few formulas to track shoe miles and number of uses in that shoe. Also calculates trail pace and road pace and overall pace between all runs. It’s endless. I don’t know how far to take it, I intend to keep it for the 4 month training I’m in then stop it…but who knows.
    Anyway, I think the pivot table works perfectly with just the data spit out by Garmin. With on additional column I added for shoes which is just a drop down list.
    Thanks for sharing.

Have Something To Say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

wordpress stats plugin
%d bloggers like this: