As noted yesterday in The Diet Plan and The Three Habits, I'm presenting the spreadsheet I used to track my weight loss progress.

Before digging into it, I'd like to point out that this isn't terribly fancy. It met my needs and did what I wanted. But I'm no Excel whiz, so there's a really good chance that you could make this prettier, more useful, and somehow better than I can.

Overview

The spreadsheet has two worksheets, selectable via the tabs at the bottom. One is labeled Calories and the other is Weight. Both worksheets also contain charts that provide a visual summary of the data collected.

In both case, there are two lines on each chart. The blue line represents the data you recorded: morning weight or total calories for the day. The pink line is the one we pay attention to. It is the 5 day moving average for the data we're recording.

By using the 5 day moving average, we smooth out the daily fluctuations in weight and the variations in calorie consumption. This is very important. Using the 5 day moving average helps to factor out the daily ups and downs that might otherwise discourage you into thinking that it's not working.

The Weight Page

The weight page is where you record whatever the scale tells you each morning without judging your progress. This is scientific data collection. Don't let it affect your mood for the day! It's just a number.

As you can see in the thumbnail above or in the full-sized version, there are 5 columns of data on the left side of the page: date, weight, average, change, and total loss. You need only supply the date and weight. All the others populate themselves.

The "average" is the 5 day moving average, so there's nothing there for the first few days. The "change" represents the day to day positive or negative change. The "total loss" column represents how much weight you've lost to date.

There are two other data points above the chart that are not strictly necessary early on. I added them very late in the process once I had a few months of data. The "daily average" is intended to provide an idea of how much weight I was losing each day. From that the "avg daily calorie deficit" cell is derived (to the right).

How do I compute that? Since we know how much weight is vanishing each day (on average) and how many calories are in a pound of fat (roughly 3,500), it's a simple matter of division.

This comes is useful later on. Once you've lost the weight you'd like to, it's safe to start increasing your daily calorie intake. But you need to know how far to go. Using this data, you'll be able to figure out how many calories you need each day.

The Calorie Page

Like the weight page, the calorie page has some data collection and a chart. The data collection is a bit more involved since I wanted to record my calorie intake for every meal and snacks. You don't need this level of detail here, but I wanted all the data in one place. All you really need is the dataset that's under the chart: date, calories, and average.

Again, all you really need to do is put in the date and number of calories. The average will be computed for you. There's also an "average/day" cell off to the right that I found useful later on to see how I was doing overall. Odds are that you're not going to hit your exact calorie targets every day. By looking at the overall average, you'll know how close you're getting.

Some folks might record more quantity information (portion sizes or weights) while others may do less. If you're eating mostly pre-sized packaged foods, it's pretty easy. Otherwise you may be doing some estimation or using a kitchen scale and CalorieKing.com to figure out what numbers to record.

As on the weight chart, the calorie chart has two lines. The blue simple connects the dots between the daily calorie intake points. The pink line is a 5 day moving average of that data that smoothes out the day to day peaks and valleys, showing you how you're trending (up or down).

Download It

As promised, I've provided a copy of this spreadsheet for download: weight-tracker.xls (300 over downloads already!)

Use at your own risk, modify to suit your needs, and all that stuff. :-)

Tomorrow we'll look at goal setting, some of the numbers in my spreadsheet, and factors you might consider in your own weight loss plan.

Oh, feel free to use the data in my spreadsheet. But, please, no comments on my particular food choices. Some days I ate whatever was handy rather than planning a "good" meal.

Update: Wow. I'm impressed. Nearly 300 people have already downloaded the spreadsheet. Had I known it'd be popular, I'd have tried to pretty it up a little. Maybe.

Previous installments:

Next installment:

For more recent diet and health tips, see our new blog: How To Eat And Live

Posted by jzawodn at June 06, 2006 07:43 AM