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:
- Diet Tips or How To Lose Weight with a Spreadsheet and a Web Site
- The Diet Plan and The Three Habits
Next installment:
Posted by jzawodn at June 06, 2006 07:43 AM
Thanks Jeremy. I'll be downloading and giving this a try.
Do you still eat Black Cherry Yogurt for breakfast everyday?
Loren:
Pretty often. I *love* my black cherry yogurt! :-)
I think the best part is your meals Jeremy :)
I was using the full FitDay app (which rocks, and does all of this), but I think a change is needed, so I'll give your sheet a whirl :)
Thanks for putting up the spreadsheets. It's an interesting exercise to record everything you eat. That alone makes you think carefully about if it's worth it.
Right after I cleared all of the meal data I thought how funny it would be if someone went through the process eating the exact same meals every day. You never know.
I must say, this transfers rather nicely to Google Spreadsheets with only the loss of the graphs.
Hmm. How to keep track of my data...online or offline?
Strictly, the 5-day average should be centred on the third day, not the fifth - it is a backward looking measure.
I'd be interested to hear your thoughts on the Shangri La diet - I think it would complement this, as a way of helping with the 'eat less' part.
Kevin:
Yeah, I can see centering on day #3. In reality, it probably doesn't matter. But I found it useful to always have a full 5 days worth of data represented in the latest average since the dataset grew by one point every day.
As for the Shangri La diet, I know nothing about it.
Something tells me that a web search will find the answer. :-)
Cool, but you need to add a candlestick stochastic plot. How else are you supposed to predict upcoming breakouts?
My but you were a porcine little lard butt. Congratulations on your success! Now excuse me while I shake the potato chip crumbs off my shirt.
Perhaps you mainly cut out the soda, the doughnuts and all the other junk food (defined as pizza, burgers, lasagna, greasy Chinese, fried potatoes, etc.), and you lost weight. This model works for people like me, who are somewhat lazy, but perhaps good at having the "that's taboo" mindset. Just categorically deny the "bad stuff" and eat whatever you want of the good stuff (consisting mainly of grilled chicken, vegetables, yogurt, low-carb health bars, etc). Mind the not full/not hungry gap, and you're in!
Well, I still have some of the "bad stuff" now and then, but I know that I'm trading off something else when I do. It means eating less of something else to offset it.
Kinda like pollution credits. :-)
I created a surprisingly similar spreadsheet a couple years ago to help lose weight for my wedding (yeah yeah, it's usually the chicks who lose weight for the wedding, but my fiance/wife is a professional athlete so she didn't have that problem. I, on the other hand, had gained 60 pounds within about 4 months when I quit running at the elite collegiate level. When you go from running 75 miles a week for 7 years to doing NOTHING, but keep all your eating and drinking habits the same, you're bound to gain weight!)
Anyway, I started counting calories and tracking my weight with a spreadsheet and graph and found my biggest motivation for not eating things I shouldn't was that I didn't want to have to put it in the spreadsheet. Before, if I wanted to stop at Taco Bell on the way home from work and get a burrito (before still eating a normal dinner) or getting a king size Skittles, I would - but with the spreadsheet I knew I'd have to write it down. Worse, I knew that there was the possibility other people would see the spreadsheet and see the kind of crap I ate (they didn't - no one else cares, it's all in your head. Plus most other people eat like that too, they just don't admit it) .
I went from ~195 to 172 in 3-4 months (142 being my prime "running weight", and 150-155 probably a good "fit weight") and looked much better in the wedding than I did in the engagement portraits. After living in Germany for a year, drinking heavy beer and eating lots of meat, I'm back up to 185, so it's time to start the spreadsheet anew - and thanks to this blog, I think i actually will now!
A great online resource for calorie & nutrition info is the USDA Nutritional Database @ http://www.nal.usda.gov/fnic/foodcomp/search/
I did almost the exact same thing as you - I started in October of 2005 and just reached my goal the other day - I used a spreadsheet that I kept on a thumb drive and Fitday.com and lost about 80 lbs in 8 months.
I can't believe how easy it was (and nobody else seems to believe me either) - it was so nice to feel like I cut through all the fad-dieting BS.
I think the real epiphany for me was learning that 3500 = 1-pound. Having that knowledge made everything about losing weight seem so incredibly simple.
I've been looking for something to track the same things but in a PalmOS app form. I'll give this spreadsheet a try.
I agree with the dieting plans set into action here. I frequently fluctuate widely with my weight, moving between 225 and 185 with seasonal regularity. My family often accuses me of ballooning out, and telling me that I will become like my cousin (who is about 150 pounds overweight).
I have tried tracking calories before, but my lack of motivation stems to even that amount of effort. I suppose I will have to commit to this level of regularity, so that I can see some real and steady results. I would interested in tracking day to day progress during the transistion phases in my weight.
This screams using MySQL with a PHP frontend. I figured you of all people would go that route... :-)
Something which is online, easy to use, pretty gd graphics, etc....ok, sounds like a lot of work. if only using your brain used up so many calories.
Maybe I can take the hint and do it...
www.physicsdiet.com is also based on the Hacker's Diet spreadsheet example. It does everything except the tallying of daily calories (it does have a spot to enter and track daily totals). Take a look at the Biggest Losers page to see how others (with public profiles) have done.
I use fit-day, a great food, weight, excercise log program (fitday.com)
its great, great reports, and huge food database
With Google spreadsheet, one could record everything eaten and then invite ones 10 best friends for read-only access....
Talk about peer pressure!
Good job, kinda like this guy....
http://johnstonefitness.com/
Hi Greg,
You can find a PalmApp for something similar at The Hacker Diet. Google Hacker Diet and Bob's your uncle.
David
I did something similar once (tracking calories and weight in a spreadsheet) and it was the ONLY thing that ever really worked for me. I lost 20 pounds in a few months and it felt almost effortless. Like Jeremy says, it really does become second nature to "ballpark" the number of calories in a meal after a while, and when you get into the habit, it's almost like a game. Especially for those of us who love playing with data.
Great work, Jeremy! Thanks for being an inspiration.
I lost 40 pounds couple years ago by geeking the process up with a spreadsheet. I used OpenOffice, but I saved a version in Excel, too. While I applaud Jeremy's geekiness, my spreadsheet was geared more toward simplicity while still giving me every conceivable statistic...
This is so nice of you to share. I saw it listed as a "hot site" in a local newspaper and plan to send it to my daughter and granddaughter in hopes they will like it too.
Appreciate it.
Wow! This is great. I don't consider it geeky. I use something similar that I created a couple years ago. I lost about 20lbs and then unfortunately became ill and stopped using it. I just updated mine this morning and happened to run accross your site. You've given me some great ideas for improvement. I spent a long time fooling myself into believing I could keep a calorie count in my head and lose weight. But this is a great way to go!
Congrats to you!
This spreadsheet got me inspired to start losing weight 9 days ago. The problem being that I had it at work and not home, so I had to remember to put info in it later. My wife was using www.sparkpeople.com to log calories and weight, so I gave that a shot. I now use it to track things as I eat them, however I still put the total calories per day and weight into the spreadsheet since I can control the charts. I do recommend sparkpeople though, as I've already lost 8 pounds (222 to 214) with my first real goal of 175. Once I get there, I might shoot for 150! It's free, and if you sign up, put "lostdog" in as the person who you heard about it from.
WOW...the calorie/weight tracker is awesome! I'm so excited to start using it. Thank you!!
As you say, you're not an Excel whiz, but there's a few things you can do to make this sheet work better.
You can ditch the Average column, the graph can plot a 5-day moving average. Right-click on the series (the blue line), and click 'Add Trendline'. Pick 'Moving Average', and set the period to 5. Hit Ok. The trendline will appear, and should overlap the existing pink line.
To change the colour, right click the trendline, hit 'Format', then 'Patterns'. Note the 'Options' tab, and the ather interesting 'Forecast' option. Doesn't work with Moving Average, but does with the opther trendlines, like Linear or Polynomial. Polynomial is fun.
You can only have one trendline, but there's nothing stopping you plotting the data twice and add a trendline to the second series.
You could add a BMI column and plot that on a secondary axis.
The next big thing is to automatically compensate for data being added, you've probably noticed you need to keep adjusting the chart to grab all the data. This is solved by named ranges, specifically what known as a self-expanding range. (Office 2003 has Lists, a similar thing.) It's a bit long-winded to explain (it's not exactly obvious either), but Google should find a few pages explaining the technique.
Changing the chart colours would be nice too, Excel defaults are a bit ugly :)
Hi Jeremy,
Just wondering what you did if you found yourself away from a scale for a day (or more) in terms of tracking on the sheet.
Thanks!
a
If I am away from a scale for a day or two, I simply use the average of my last weight and my next weight.
If you wanted to track exercise on your calorie spreadsheet, would you just estimate calories expended and show as a negative number?
Hello,I am one week out of RNY weight loss surgery and need to accurately track my weight loss. Your spreadsheet looks like just what I am looking for but when I download it fromuyour aite I am getting the meals page only. Is the weight loss portion available for download.
Thanks, W...
Nice overview :) I'm not using any of these "tables or graphs" but I loss a lot of weight just eating without bred ...
Regards
I love it....I'm starting today. Simple....straight-forward...the spreadsheet it great. I've downloaded it and already entered breakfast. As a full-time nursing student, I need something to keep track of my eating...you can develop really bad habits when you're studying, because you lose track of what you eat. Thanks!
Just wanted to send you a big Thanks! I've been following your plan for a little over two months, and it is working great. I've gone from 310 to 280 pounds.
Many Thanks!
Hi Jeremy,
Congrats on a great blog! Lot's of useful information here.
I saw that you have interest in Dieting and diet plans. Since you have expertise also in the field of Linux and OpenSource I would ask you to review our site http://www.2000Cal.com. I understand if you're too busy, but I would appreciate some feedback from you both on the diet part of the site but also the usability etc.
If you have any questions or suggestions, feel free to contact me at my email address.
Best regards
Rolf Brandsar
2000Cal.com
Jeremy,
Some very useful inforamtion and I appreciate the time it took to put this together. I have a question, I recently turned 50 and I have a question on what my average caloric daily intake should be? I'm 6'4 and currently weight 236 pounds. I'm trying to get down to 225. Even though I work out seven days a week with a combined weight and cardo regimen, I have noticed that my metabolism has slowed down and the weight is harder to take off. I plan to record all my daily calorie intake as you suggest. Anything else you can recommend? I eat very little sweets and drink on average less than four beers a week. Thank you.
Nice tool ! Any advise for us Mac users? Been out of windows environment for years...
How do I download and/or convert for use on my Mac?
can anyone help ?
I can not download the spread sheet .
thank you
Heya,
Just found your site. Started converting John Walker's Hacker's Diet files over to Open Document Format for use with OpenOffice.org. Also made my own weight tracker, with exponential smoothing and forecasting.
I am excel illiterate. I put the dates and weight in, but the other numbers don't blow in automatically. The graph looks great, though. thanx.
I just decided that I needed to lose weight. I just turned 20, and I weigh about 180 pounds. The most I've ever weighed.
I found the website through a simple google search and Im glad that I did! The best part is Im currently taking courses in Excel so, I tweaked it a bit for me to understand in my own terms.
Im still a little lost on how to do the chart though,
Thats always the part kicking my butt in class!
Thanks! I'll post to update!
Hi Jeremy,
I want to thank you for sharing your information with people. I just wondered acrossed your website while googling and found it very informative. I am a 38 year old female that desperately needs to lose weight. My problem is that I work 2 full time jobs from 8:30 am to 5pm and then from 10pm to 6:30am and don't have to much time for sleep let alone time for exercising and eating right. I'm hoping that once I try your method that it will help me to lose the necessary weight that I need to lose. Any advice that you can give me would be greatly appreciated.
Sincerely,
Janine
Hi Jeremy,
Congratulations on losing the weight and keeping it off. About 3-4 years ago I lost 60 pounds. I found it very interesting that the methods you described where exactly what I followed that allowed me to lose the weight. I counted calories daily, I got on the scale daily, and I tracked my weight by charting it (not in Excel, although, my husband highly encouraged me to do so.) I never denied myself any specific type of foods. I ate anything I wanted just in moderation. After I lost the weight, I actually lost more than I had targeted, I slipped and fell into bad habits again. Mostly, I stopped tracking my daily calorie intake. As a result, I have gained back 15 of the 60 pounds in 2 1/2 years. Needless to say, I'm going back to counting calories. :)
Loved it! I am the husband of Jo above. I am sitting here right now at 137.5 pounds. In my mind I am still a super athlete. HA!
So you got me motivated. I love excel too. I will have to try this.
Thanks for the motivation!
dk
Duh! Obviously the fat has gone to my head. I am 237.5 pounds, not 137.5 pounds. Wishful thinking hugh?
dk
Jeremy, thanks fro the great tracker . could you please send me the weight one also. it would help me much to lose.
vic..
@ Vic:
The weight spreadsheet is there as well, just look at the bottom of the spreadsheet and you should see two tabs, one marked Calories and the other marked Weight.
Don't feel bad, I scratched my head for a while before I noticed that as well!
Cheers,
captnkurt
I am excited to start trying to use this spreadsheet. I have been trying to lose some weight for about a year, but nothing seems to have worked. Does anyone have any suggestions for incorporating exercise into the equation?
Hi...thanks for posting this. it will help a lot..Great job. I've been struggling with weight loss for years and I just like to read posts like yours to get some inspiration every once in a while. Keep it up...
Diet Program by Crizzia
Hi Jeremy
I am stuggling to lose weight, I haven't been recording calories, sometimes I have written down the foods, but I am going to start an excel spread sheet to keep track of it all now, as the last time I lost weight that is what I did,
your tip about feeling hungry, rather than feeling full, is a good one, and I do need to focus on that, as I know I am eating for other reasons, which isn't good for you body,
I have 56 lbs to lose.
thanks for the website,
Inspired by this spread sheet and my need to have an online version, I coded up Diet Tracker (http://srijith.net/codes/diet_tracker). Version 1.5 was released just couple of days ago with cool auto-complete feature, among others.
So, if you are in market for an online version, do give DT a try. It is GPLed too.
I really appreciate your thoughts. I have found that dealing with diets spreadsheets can be so stressful it can really make you crazy. Just reading how other people deal with their weight loss issues helps me deal with mine. And i've been struggling with weight loss for years and I just like to read posts like yours to get some inspiration every once in a while. Keep it up.Thanks for posting this.Goodluck!
Hello Jeremy,
I only want to say a big thank for the spreadsheet and dieting articles. My mom uses the spreadsheet regularly and she already lost 15 pounds. User comments were also very helpful. So thanks to everyone. :)
I wish you all the best.
Just wanted to say thanks. Excel and I have no love for each other and I really needed a straight-forward method to track weight and weight-loss progress. Thanks again for posting it for all to use!
hi jeremy...i downloaded the meal chart, but i can't find where to download the weight chart...can you e-mail me and let me know, or e-mail me the link. that would be so great, i'd really appreciate it, thank you so much.
lol, ok, never mind, i read someone's post and found the weight chart, thanks anyway =)
Thanks a lot Jeremy for sharing your exprience and the spreadsheet
hi. i just wanted to say, i think this spreadsheet is really helping me out.
however, on the weight chart, it wont show my progress. it's stuck at 345-165, and im under that, so i cant see where i am. is there a way to fix this? or will it fix itself out?
I'm not all that Excel-savvy. Is there an easy way to update the chart for 2008 and change the weight values?
Hey Jeremy, I used your spreadsheet when starting out and have went from 28% body fat to 11% body fat in 3.5 months. Check out my progress here...
www.scottysbody.com
Scotty
Hi there
I do appreciate what you have done in your way of planning the diet systems
My current weight is 198 Lb and Ineed tomake it 166 Lb. I have only one probelm and that is my blood sugar is 60. Advise how I can make such diet as per your recomendation without lowering blood sugar level.
Thanks in advance
Regards
waleed amin
this is amazing
thank you!
I love being organized, and keep spreadsheets for everything, but never thought about using it for my weight loss
I love the idea of this spreadsheet and have used it for 3 days... and now I can't use it anymore, as it wants me to enter a code. My son tells me it's because I don't have Microsoft Office or something, though I thought I did, but was told it's probably a trial version. Hmmmmm... nobody else seems to have this same problem. Thanks for all your help Jeremy. I appreciate all you've done for us.
Deb
How do you download the weight page?
Thanks for the info! I just changed the way I eat and I think this will help me meet my goals!
Don't mean to sound stupid, but I have not been able to download your spreadsheet. When I click on the weight-traker...I am sent to a Microsoft page that has nothing to do with your spreadsheet. Any help would be appreciated.
Thanks.
Try Desizeme.com - it provides a chart and diary online - no more Excel! Its very easy to use.
