How to Create and Use Weight Loss Tracker in Excel?

The image below is a sample Weight Loss Tracker.

How to Create and Use Weight Loss Tracker in Excel


Download Practice Workbook

Step 1 – Calculating Weight Loss Percentage

Enter the relevant information in their corresponding cells in the form above the tracker.

Creating Table for Weight Loss Percentage

  • Use the following formulas for the respective cells.

Formulas for Weight Loss Percentage

  • In cell C12, enter >> =ROUND((B12-B11)/7,0)+C11
  • In C13 cell, enter >> =ROUND((B13-B12)/7,0)+C12
  • C14 >> =ROUND((B14-B13)/7,0)+C13
  • In E11 cell, write >> =D4
  • In E12, write >> =E11
  • In E13 >> =E12
  • In E14 >> =E13
  • F11 Use >> =(D11-E11)/D11%
  • Use the Fill Handle icon for other cells of the F column.
  • Enter the following formula in the H5 cell to get an overall weight loss percentage.

Calculating Overall Weight loss percentage

Note: To keep the % sign with the number, don’t use % symbol within formula. Change the Number format of the cell from General to Percentage.

Step 2 – Finding Weight Loss in Stones and Pounds from Kilograms

  • Insert the following formula to convert kg to stones and pounds.
=INT(CONVERT((D6-E6),"kg","lbm")/14)&" st "&ROUND(MOD(CONVERT((D6-E6),"kg","lbm"),14),0)&" lbs "
  • Press ENTER and use the Fill Handle icon to fill the other cells of the column.

Converting the units of Weight

Formula Breakdown

  • The CONVERT function uses these 3 arguments. The D6-E6 is the number argument, next “kg” is the from_unit argument, and lastly, “lbm” is the to_unit argument.
  • Then, the INT function returns only the integer value. INT(CONVERT((D6-E6),”kg”,”lbm”)/14)—> turns 0.
  • The MOD function gives the remainder of the division. The number argument is CONVERT((D6-E6)),”kg”,”lbm”, and the divisor argument is 14.
  • The ROUND function rounds the above value to the nearest integer. ROUND(MOD(CONVERT((D6-E6),”kg”,”lbm”),14),0)—> gives 11.
  • The “st” and “lbs” strings are joined with their corresponding values using the Ampersand (&) operator.

Step 3 – Inserting a Weight Loss Chart in Excel

  • Select the whole dataset >> go to the Insert tab > select the Recommended Charts.
  • You will get the Insert Chart Go to All Charts there >> Select Combo.
  • From Custom Combination, choose the Chart Type for Measured Weight as Stacked Area and set the Chart Type for Target Weight as Line >> press OK.

Creating chart

In the image below, I have attached the chart and some Chart Elements.

tracking weight through Excel chart

Step 4 – Calculating BMI in Excel

  • The formula for BMI (Body Mass Index) is: BMI = weight(kg)/ height(meters)^2.
  • Enter the following formula:

Calculating BMI

Note:I converted the lb unit to kg and cm unit to m and used the basic formula of BMI.
  • For using color codes in BMI value, select the cell >> from the Home tab, go to Conditional Formatting >> choose New Rule.

Using Conditional Formatting to Color the BMI

  • You will get the dialog box named New Formatting Rule. Select Format only cells that contain >> go to Format only cells with: options >> choose less than >> set the value (5) >> click on Format.

Making Rule to apply formatting

  • From the Format Cells dialog box >> go to Fill >> set a color >> press OK.

Set the Fill color

  • Press OK to the New Formatting Rule dialog box.
  • Set the colors using Conditional Formatting for other values.

Used other formula for formatting

Step 5 – Combing Weight Loss Percentage, Chart and BMI to Make Tracker

Assemble the findings above for creating the weight loss tracker.

how to create tracker

  • Follow the formulas of calculating weight loss percentages.
  • Add a new column named Target Weight >> link G4 cell as absolute reference.
  • Use the formulas for overall weight loss percentage and BMI.

Including Chart for weight loss tracker

  • Add the chart for tracker. We used Current Weight and Target Weight columns for the chart.

Step 6 – Checking the Output of Weight Loss Tracker in Excel

  • You will need to manually fill out the form (marked red in the image below). You will get the weight loss percentage and overall weight loss percentage.
  • It will also calculate the BMI and format the cell according to the color code.
  • It will add the chart lines to understand the difference between current weight and target weight.

how to use tracker


Excel Weight Loss Tracker: Knowledge Hub

<< Go Back to Tracker in Excel | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF