- *** For the newer version of this tutorial, just click here! ***
Step 1: Blank canvas
In a new doc, double-click Sheet 1 on the left and enter a name. Rename Table 1 ‘Personal Details’. Delete all but two columns and six rows. Select ‘Blue Headers’ from Styles and delete the Header Row.
Step 2: Enter personal details
Label the rows: Gender, Age, Height (in metres), Start Weight (in kg) and Target Weight. Drag the table to the right, resize it to around 8cm wide by 6cm high and enter your details.
Step 3: Create main table
Add a Basic table, named ‘Weight / BMI Tracker’ with about 25 rows and six columns labelled Date, Weight (in kg), Target Weight, Difference, Total Weight Lost and BMI. Select the Blue Headers again.
Step 4: On target
Drag the table out to 18cm wide by 23cm high, then click on cell C2 (the first in the Target Weight column) and type =IF(ISBLANK(B2), “”,Personal Details :: $Target Weight) into the formula window.
Step 5: Copy, paste, format
Drag the lower-right corner of the cell downwards to copy and paste this formula down the column. Hit the Command+A keyboard shortcut to select both tables, then centre the text within the cells.
Step 6: Difference engine
Enter start date and weight in cells A2 and B2. In the first row of the Difference column, enter the formula: =IF(ISBLANK(B2), “”, B2-C2). Tick, then drag down to copy and paste the formula to the cells below.
Step 7: Running total
In cell 1 of Total Weight Lost, enter the formula: =IF(ISBLANK(B2), “”,$’Start Weight (kg)’-B2) and tick. Click the orange triangle in the formula bar and select ($‘Start Weight (kg)’), then accept.
Step 8: Calculate BMI
In the BMI column, use formula: =IF(ISBLANK(B2), “”,B2/ ($’Height (m)’*$’Height (m)’)). This takes weight from the B column and divides it by the square of the height in Personal Details. Set to 1 decimal place.
Step 9: Weight loss chart
Shift+select Weight and Target Weight columns. Click the Charts button. Select Line Chart and name it Weight Loss. Click the Inspector’s Axis tab and enter 60 as the Min Y Axis value.
(Click on the image below to zoom in and view the annotations)