Hey, thanks for stopping by! If you're new here, you may want to subscribe to the RSS feed. You can also subscribe to BikeHacks by email. Thanks for visiting!

I am guilty of being a dork concerning a great many things. Bikes are at the forefront of my dorkiness but my obsession with Microsoft Excel is at the top of the list as well. So why not combine the two? If you are a hardcore Excel user, skip all the text and just look at the pictures and you’ll get the point. If you only have cursory knowledge of Excel, this entry should give you the basics to get started.

I am a stats dork for sure. It all started with tracking mileage as a distance runner years ago. Personal computers existed, but were rare and my first PC Junior did not even have a hard drive. I relied upon spiral bound notebooks with graph paper to track mileage and created rudimentary tables and graphs. When I finally realized that cycling was much cooler than running because there was lots more stuff to buy (several stress fractures and major tendinitis were great motivators to move to wheels as well) personal computers were all the rage. Excel was a part of my everyday work life and I decided to extend it to my cycling life.

Pictures can be great motivational tools and if you want to be a bike mileage graph dork like me it’s super easy. With four columns and one simple formula you are good to go. The columns I use are date, miles, total, and day. You can see what I am talking about here:

The “drag down” feature in Excel can be used a few times in creating a sheet like this one. First, on the date just type in the first date, click on the cell and then hold on one of the bottom corners and drag down the column and it will auto fill the dates for you. Column B is where I track the miles I ride on the given date (you can see that my daily commute with no detours is a comfy 8.4 miles). The next column keeps a running tally for me. Simply type the “=C2+B3″ formula (as is displayed in the formula box in the picture) and use the drag down feature again to copy the formula down the entire column. I put in the days in column D just ’cause sometimes I don’t track miles for a while and the days help jog my memory.

Once you have it going, click on all the data in columns A and B and hold the shift key to highlight the contents. Then either click on the chart wizard button in the tool bar or use the “Insert” button from the tool bar menu and choose the “Chart” feature. The line graph works best and if you simply follow the prompts in the wizard you end up with something like this:

You can choose your date intervals and what not - play with it until you like what you see. I also keep a simple running count down at the top of the sheet (see picture 1, column F). Sure the total mileage is at the bottom, but each time I open the sheet it reminds me of how far I am from my goal. I like to shoot for around 3,000 miles per year and a simple formula acts as an auto countdown for me. At the bottom of column B use the AutoSum button (the one that looks like a funny “E” in the tool bar). Enabling the AutoSum at the bottom of column B keeps a running total and then I just use a simple formula to subtract my total mileage from my 3,000 mile goal. In my case the formula is:

=3000-B368

B368 is the cell with the AutoSum of my daily miles. Creating a graph like this might seem like a pretty dorky thing to do, but if you give it a whirl, it can be fun and motivational. I’m sure there is some fancy software out there that will do it for you, a la’ Nike+Ipod, but opt for the Excel hack and go for max dork factor.

Kinda related posts

• You Say You Want A Velorution (3)
• Weighted Wheels (0)
• Security: Padlocking your Quick Release (1)
• Modification: Black Label (0)
• China: Bike China! (3)

Filed Under: commuting