Thursday, March 10, 2011

using charts & spreadsheets to track my health

Who doesn't know I love spreadsheets? NOBODY. That's who.

I've long created, used, abandoned and recreated Google spreadsheets to track my activity and food intake, but I've only recently (finally!) understood how to use Google's charting options to visualize the data. Which inspired me to go beyond the simple calories-in/calories-out tracking and start exploring my: heart rate, water intake, weight change and muscle/bone/fat/water percentages that I can borrow from our Homedics bathroom scale.

So how's about I just dive in and show you what I see everyday?

the charts

For the most part the charts I've included below display one week's worth of data (for the span between Feb. 28 and March 6).


the mothership

health tracker mothership

Here are the raw numbers, which I fill in every day. I have some ideas for more data to add (a count of visits to the gym per week, separate tracking for bike rides, inch measurements to accompany my weight numbers, etc.), but for now this is giving me a lot of great analysis. A note: You'll notice I leave the heart rate/speed numbers blank on days I don't exercise, but I take the time to mark "0" calories on those days. When you see the visualization of those numbers later on I think you'll see why.


daily water intake

daily water

Water! Simple and straightforward. I'm still trying to figure out the best volume of water to keep me properly hydrated every day. I've set a goal of 81 oz. for now (my Klean Kanteen is a 27 oz. bottle and to keep things simple, I aim to drink three of those a day, which brings me to 81 oz.).

I still feel thirsty, and wake up thirsty, so I don't know if I'm overdoing it, need more water, or need to examine something else in my diet. If I come to find that I need to increase or decrease my daily water goal, I'll adjust my chart accordingly.


weekly points consumed

daily & weekly points consumed

I already track my Weight Watchers points through my login, but I thought I may find some benefit one day (for some reason I have yet to determine) of having these numbers readily available in my charts.

Maybe one day I'll get curious about how my weekly activity performance compares with my overall food intake through that week? Or if my heart rate during exercise seems to spike on days I've consumed more points (or fewer)?

I really don't know yet, but the numbers are there to be crunched ...


daily & weekly calories burned

calories burned

I use this chart to keep an eye on my weekly goal for exertion: I'd love to burn 2,000 calories each week. You can see that in the week displayed here, I fell short of that goal.

(And regarding that "0" calories burned I mark on days I don't go to the gym: You can see how that "0" displays on my chart.)


heart rate zones per workout

heart rate zones per workout

I need to further educate myself on what different heart rate zones mean for fitness, but some preliminary reading suggests that if I keep my heart rate in a lower (though elevated) range, I'll be in my "fat-burning" zone.

And that the further I push that heart rate the more it will push my body beyond that benefit and into ... I'm not sure. That's where I need to read more so I can understand what the higher exertion does for fitness training.

In the meantime, I've been trying to keep my heart rate in the first (teal) and second (green) heart rate zones (as determined by my smart heart rate monitor). And that pinky red you see in my earlier workouts? It's me nearly maxing out my heart rate before I started reading about healthy zones and how to approach aerobic training ...

(And regarding those nil data sets for days I don't work out: For this chart and the next, it would be funky to see "0" for my heart rate those days. I think it's more useful to mark progress from one workout day to the next, whenever it may fall.)


overall heart rate & speed

overall heart rate & speed

And here's a big-picture look at my heart rate.

As far as I understand, heart rate is a good measure of overall health. What I do with this chart is keep an eye on my maximum and average heart rates for each workout, as well as the average speed for those workouts.

My thought? That putting those two numbers together will help build a more complete picture of wellness: If my heart rate goes down over time and my average speed goes up, then I'm getting in good shape.


weight tracking

weight over time

I had to do a little trickery here, as Google's charting seems to have a glitch: I can't set my X-axis to a number higher than "0."

Why do I want to adjust my X-axis? Because the narrower the range of numbers displayed, the greater the difference appears when I go up or down a pound.

What I discovered: I *can* set my X-axis at a number lower than "0." (Silly Google.)

So I've set the chart range to display between -130 lbs. and -114 lbs.

Which is a long way to explain why you see the line designating my goal weight (115 lbs.) hovering above my daily weigh-in line (which creeps *up* as I lose weight).

*Phew* Did you get all that?


bathroom scale numbers

bathroom scale numbers

The number I'm most interested in here is my body fat percentage, which I would love to get down to 18 percent one day (which would mean me basically being an athlete and shifting my activity into high gear). Right now it's hovering around 28 percent.

I'd like to read up on healthy percentages for these other numbers. Right now, I'm around 52 percent water, 36 percent muscle mass and 3.5 percent bone mass.

A note about this chart: imagine that the walls of color you see are stacked one behind the other, rather than on top of one another. Which is why that little strip for muscle mass looks so tiny (36 percent), even though it's greater than the body fat (at 28 percent).

what does it all mean?

I'm trying to look at these numbers more in the spirit of scientific observation than fire-under-my-ass motivation.

I know I'm making good, healthy decisions. I know that my weight and various body percentage numbers will slowly approach a healthier range. I know my heart rate will slowly decrease as I get more fit.

But isn't it interesting to watch those things happen quantitatively? And couldn't these numbers be helpful to observe hiccups or to help me get in front of bad habits rearing their ugly heads?

I think so.

And I hope you think so, too. Because I plan to produce weekly updates using these charts. Maybe some monthly updates to analyze bigger shifts. And wouldn't it be great to see where these charts rest in one year?(!)


  1. Oh my goodness.. these are beautiful and *so* detailed! I think it will be awesome to see the progress after a year. Are these all Google docs? I didn't know you could do so much with them!

    I'm really interested in changing my body fat % as well. I think muscular, athletic bodies are beautiful!

    From what I understand of the fat burning zone, you will be burning a greater percentage of fat calories. In the fitness zone the total number of fat calories is more because you burn a much higher number of calories overall.

    In one of Jillian Michaels' books I picked up (Winning By Losing) she explains it like this:

    "In half an hour of low-intensity exercise, you might burn 100 calories. Of those 100 calories burned, roughly 80% will be fat calories, meaning you've burned 80 fat calories. In half an hour of high-intensity exercise, you might burn 300 calories. Of those 300 calories, roughly 33% will be fat calories, meaning you've burned 100 fat calories."

    There's so much to know about exercising and fitness that it gets a little overwhelming and hard to know what's best since there's so much to know.

  2. Why thank you, ma'am!

    Data and spiffy visualization are *so* motivating for me ... and yes those are all done in Google! Go try it out. You'll have fun :)

    I think I may have to pick up Michael's book. Is it a good read? Lots of good, verifiable info?

    Oh, and my brother gave me some great information about heart rate during exercise that I plan to post later ... it complements what you quoted from Winning By Losing.

    I'm at once overwhelmed and exhilarated by how much there is to learn.

  3. Holy crap, I love these! I would love to do something similar to this when I'm losing the baby weight, though there may not be time in the day...

  4. Time is the thing, right? I'd be happy to make a charting tool for you, Meg, with whatever parameters you're interested in tracking. I find I spend about five minutes a day entering new data (the bulk of my work was in creating the tool in the first place).

    You just say the word ;)

  5. I just started reading her book. I found it at a used bookstore for $3 so decided to pick it up on a whim. It's a lot of basic information, but it's really easy to understand and there's also a ton of photos that show proper form for exercises. There's also a few recipes but they aren't very appealing. I'd definitely pick it up if you find a cheap copy. It's an older book (2005), so you could probably find it for almost nothing.

  6. Oh my science! They look gorgeous and so damn techy. Love'em! I love that, for you, making spreadsheets is like doing a word puzzle. Amazing.

  7. It is so like a puzzle, Mela! Also, I find myself determined to hit the elliptical in the mornings *just so* I can fill out my numbers. Same with drinking water. While I'm trying to keep these as observational tools, they're definitely working their little motivational magic ...