By
In this tutorial, we’re going to take a blank canvas and then build our very own weight loss tracker. We’ll be using BMI calculations to figure out what range your target weight should be in, creating a simple table that you can drop your dates and weights into on a regular basis, and displaying all this info in a smart chart.
We’ve also got a ready-made version you can download, because we’re nice like that.
Step-by-step: Make a weight loss tracker
1 Set your target
To help work out what your target weight should be, we’re going to work backwards from the healthy BMI range. Make a 2×4 table and set the font and colours. Now input your height.
2 Calculate ideal weights
BMI is weight divided by height squared, so we rearrange that to calculate weight. In the next cell (Min), type: =18.5*(A2*A2). For Ideal and Max, replace 18.5 with 21.7 and 24.9, respectively.
3 Show your goal
Now make a 2×2 table and type in your start and target weights (use the previous table to pick your target weight). After that, make a third table with four columns and lots of rows.
4 Set up your record
For your headers, type in Date, Weight, Target and Progress. The first two are for you to fill in on a regular basis. Link all the cells under Target to your target weight in the 2×2 table.
5 Calculate your progress 1
For Progress, we’re going to work out how far from your target weight you are as a fraction of the total weight that you want to lose. To start with, type in weight B2 minus target C2.
6 Calculate your progress 2
Put that in brackets and divide it by (also in brackets) start weight A2 minus target B2 (both in the 2×2 table). Put the whole thing in brackets and then subtract it from 1. Repeat the formula.
7 See how far you’ve come
Set the Progress column’s cell format to %. You should now be at 0% on day one and 100% when you reach your target. To see how we added the colours, check out the anon guide at the bottom of this post.
8 Make your chart
Next, select your weight and target columns and then make a filled line chart (2D). If the weight line is covering up the target line, swap the columns over in the table.
9 Tidy it up
To finish, go to Format>Axis>Value (Y) and set the Min Scale value to 60. Under Category (X), delete the Label References then select your date cells, and set a Right Diagonal Label Angle.
Styling your weight loss tracker
Click the anno guide below to bring up the full-size version, or right-click to download the image then open it Preview.
