Better Living Through Spreadsheets

Over the last year I've lost a considerable amount of weight (roughly 18kg as of this morning) and have been tracking my progress on a spreadsheet. Having tried a number of online services I just got lazy as none did exactly what I wanted, also being an Excel developer before becoming a postgrad I do rather enjoy a nice spreadsheet.

I use the spreadsheet for the purposes of tracking weight loss, but it'll also allow you to set goals of muscle mass or percentage, and fat mass or percentage. The only requirement is a set of scales; if you have the type that gives you muscle and fat percentages you can track those too. It's worth noting that such scales do a terrible job of determining what your body fat percentage is, but are at least consistent if used at the same time of the day with similar hydration levels, so can be used to measure change if not absolute numbers. Being neither brave nor free I track my weight in kilos but there are no units used anywhere.

Setup

The setup is done on the first sheet and is hopefully self explanatory. Fill out the start and end dates, goal type, goal value and your daily nutrition limits. You need to set a goal date for the rest of the sheet to populate but bear in mind that this should not be seen as the date a which you can return to being a slob.

Nutrition

The nutrition tab allows you to track calories and so forth over the course of a day. This doesn't keep a record of calories consumed over a period of time, though I may get round to adding this in at some point (I don't track calories properly at the moment because I'm a) making good progress without doing so, and b) a maths postgrad so I keep reasonable track of the numbers in my head). Clicking the New Row button adds a new row to the table with the current time and date and as it's filled out the numbers are added to the 'Eaten' row of the top table.

New rows are added by a macro, which raises a security issue. If you were to allow the workbook to run macros, and I was feeling malicious, I could cause you untold misery and embarrassment by silently sending emails from you account or deleting all your files. Suffice to say, whenever you download any workbook with macros in it, take a look under the hood at the Visual Basic code to check that there's nothing scary happening. All you'll see in this workbook is the following, which hopefully you trust is completely benign.

Option Explicit

Sub Insert()
wksFood.Range("B11:M11").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
wksFood.Range("D11:E11").Merge
wksFood.Range("B11").Value = Date
wksFood.Range("C11").Value = Time
End Sub

Weight

The weight sheet can be a bit overwhelming to look at but by filling in the setup sheet everything is ready to go. The main table will be the right length and with the correct row labels. The only columns that need filling out are C, D and E with your mass, body fat percentage and muscle percentage.

Fat and muscle mass are calculated from mass and the appropriate percentage, the Week column tells you how long you've endured, mass change gives daily changes in mass, and goal aim shows the weekly progress that you should be making to reach your goal. The small progress table allows you to celebrate getting a quarter or a third of the way and so forth.

Chart

The chart sheet may need a bit of setup, depending on how the axes appear to you. I've set it up so that fat and muscle mass are tracked on the primary y-axis and body mass on the secondary y-axis. Mine is also set up so that the units are consistent over the two axes. The x-axis range is also set automatically so you may need to change this too.

The numbers are shown as follows: fat mass is in green with a black linear best fit line, muscle mass is blue with no best fit line, body mass is red with a red linear best fit line and finally goal aim is in purple.

When you start filling it out it should look a bit like this:

Download

Download the spreadsheet here. It is released under a GPLLOLWTFBBQ license.

How to lose weight

Weight or fat loss is a relatively straightforward process but requires discipline. For advice on getting fit and healthy I recommend the reddit Fitness FAQ, and for a great community of people offering encouragement and advice join reddit (you don't need to give an email address) and sign up to the loseit subreddit here.