I’m putting together an Excel workbook as an exercise log. The current version focuses on running, with less developed tabs for cycling, yoga, swimming, and gym workouts. The spreadsheets work with formulas only, no macros or Visual Basic, so they are safe to download. The workbook requires at least Excel 2007 because some of the formulas use new functions of Excel. The workbook is a 5-year log. All of the sheets are made for a little more than 5 years.
The examples mostly use my actual data as of the date I snapped the image, with the exception of some places where I do not have data (for example, in the Initial PRs). The image itself may not be an accurate reflection of the latest version of the log since I’m in the process of updating it. It should, however, be quite close enough for the purposes of this post. The initial configuration for the example is set to start the workbook on January 1, 2016. The Marathon Target is set to January 15, 2017.
I’ve used some conventions in how the dates and values are filled in. Items that are earlier than today generally fill in with a teal color. Some of the tabs put the color in the date (for example, the Runs tab) and others place the color in the actual value (the Runs by Week and Marathon Targets tabs). Input cells use the default “Input” for Excel (orange fill color and a custom font color). In the Calculations and Conversions tab, the calculated values also have the standard Excel colors (light gray fill with an orange font).
On the Configuration tab, there are places for the starting date, for a running race date and previous miles (running and cycling), and a current and target weight. I did everything as if I were starting January 1 of 2016. The previous values are included in case you’ve already been tracking miles for a while. The “Streak Breaks” toggles a highlight function on the Runs tab. There is an organization online that tracks streaks where each day of at least a continuous mile of running counts as a continuation of the streak. Whether the toggle is set on or off, the actual streak values on the Runs tab calculate the number of days in a streak and resets any time no run of a mile or more is entered. If you don’t plan to streak, you may not want to have a lot of yellow highlights on the runs. See the Runs tab below for an example of the yellow highlights.
I think that the Weight Tracking tab is pretty obvious. My scale offers all those details that are filled in and the “Pounds to Target” uses the weight as configured. There’s a link to a weight calculator that I made, based on formulas I found online. So far they’ve been pretty close to my scale values.
The Runs tab has all the fun bells and whistles since it’s what I have spent most of my time tracking. All the dates fill in from the initial date. There’s a drop down in the columns for “Race?” and “Race Distance” so I can pull the races out onto a separate tab. Most of the values are pretty obvious. There’s a place for additional miles. 3 columns cover the monthly totals, the yearly distance since the start, and the calendar year. Each month end and both year ends are marked and automatically start the next intervals. By starting the example on 1/1/2016, as I did, the year ends are the same, but when I started tracking my own miles, it was on May 1st so the two year ends were different.
There is also a place for tracking my shoes, plus links to cycling miles and a couple other tabs that give the time spent swimming and doing yoga. I will probably add one more for gym workouts when I put that tab in. Also, there’s a streak tracker. If you run less than a mile, it puts a yellow highlight on the day and resets the counter in the Streak column. I put a switch on the configuration tab for the highlighting.
Runs by Week
The Runs by Week tab gives weekly information at a glance and also has a total miles since starting to track mileage There’s a configuration item that sets an initial mileage if you have been tracking your totals by other means prior to using this log. There is no input on this tab except for the Comments column.
The Races tab is a list of the races run. The values fill in from the Runs tab by finding the “Race?” = “Yes” items. No input fields are available on this tab.
The Marathon Target tab is the one that I am proudest about, given that I didn’t use code. At the top of the sheet, there’s a 3-week snapshot of the actual work done versus the training plan (last week, current week and next week). That way you can see them side by side and know what’s coming up while not having to scroll a lot. Below that is the actual training for the full length of the training (filled in from the Runs tab) and below that the training plan (filled in manually – the example is a modified version of the Galloway beginner model with additional miles added for my exercise of my dog). The date of the marathon (initialized on the Configuration tab) sets the dates all the way back to the beginning of training. While the example training plan is from Jeff Galloway’s site you could put any training plan you wanted in there.
The Cycling tab has much the same general layout as the Runs tab but fewer bells and whistles. Given all the tabs, I am thinking about making one sheet that focuses on Running and another on Cycling with a basic level of information on the “minor” sport. Given that I have done the heavy lifting on the running side, it wouldn’t take a lot to get the Cycling to duplicate it. There are some differences (the METS column, for one) because cycling is a different beast. At the same time, maybe I need to add a column to the Runs tab…
Cycling by Week
The Cycling by Week tab is an easy place to see the week and total miles.
Yoga, Swim and Gym
The Yoga, Swim and Gym tabs are pretty plain and self-explanatory. The tabs each report Total Time to the Runs tab so you can see if you did an hour of yoga and 30 minutes of lap swimming the same day that you went for a 4 mile run.
Calculations and Conversions
The C&C tab has a bunch of little conversion tools. There’s one for impact on pace at altitude. There’s one that gives a quick temperature change at a glance so if it’s 80 degrees, you should add a full 2 minutes per mile to your training. And then I took Galloway’s Magic Mile tool and added the temperature adjustment to it, so if you run 15:00/mile at 80 degrees in training you can look at this and think, “hmm, at 55 degrees I should be able to get down into the 13 minute range…” It may not be true, of course. I’ve created a Table of Contents that offers hyperlinks to the tools and there are hyperlinks that jump back to the list of tools.
The the Calories Example is something I found online a while back. It shows calorie expenditures for several exercises by weight range. I like that it includes some calorie counts for big people, but I’m not sure I’m going to leave this in because I can’t find its source, because it’s pretty simplistic, and finally because it is one more tab in a fairly large number of tabs.
The next tab is the Reference tab. It’s a place where you can make notes about your routes so if you put “Canal Route from FAST Tempe” in the Runs tab you can spell out what that means in this one. I’ve also included links to a set of Primal Workouts that give a basic set of core workouts with increasing difficulty.
Months and Years
The Months and Years tab currently only has running information, but it is a simple table of the month and year totals. The top month and year are highlighted. I should probably do the same for the Cycling totals. There are also a couple tools showing the day of the year, the remaining days in the year, and their respective percentages. The highest values are highlighted.