How to Create and Use Weight Loss Tracker in Excel?

In this article, you will learn how to make an Excel weight loss tracker. You will get the basic concept of the calculation of weight gain or loss. To live a healthy life, everyone should keep an eye on body weight. Along with the calculation, we will explain how to create a graph for visualizing the weight situation.

Moreover, we will show you the calculation for different units of weight like kilograms, stones, and pounds. So, you can find your preferred calculation process. We will also explain the BMI (Body Mass Index) calculation. Which indicates whether you are underweight, normal, or overweight so that you may gauge your present health condition properly.

Seeing the above image, you may think that making a weight loss tracker in Excel is difficult. But if you follow the steps one by one then you can not only use the tracker but also create your own tracker. So, let’s see the details below.

Here, we have used Microsoft 365 to prepare this article. However, you can do the same for other versions also.

Download Practice Workbook

Creating Weight Loss Tracker as template.xlsx

How to Create an Excel Weight Loss Tracker?

Creating a weight loss tracker in Excel depends on the type of tracker and the unit you are tracking in. In the following sections, you will see a few procedures for creating a proper weight loss tracker in Excel.

Whether you want to lose weight, build muscle, or just stay fit, this tracker helps you to reach your goals. Here, you will get both the calculations of the weight loss percentage and BMI so that you can get your health situation. You will know the conversion of kilogram to stone-pound. For more visualization, we will create a graph of a tracker. Also, we’ll show you how to use it effectively.

1. Calculating Weight Loss Percentage

Here, we will use simple subtraction, division, and multiplication with the help of cell references to get the weight loss percentage.

Calculating weight loss percentage is a simple way to see how much you’ve lost compared to when you began. It helps you stay excited and focused, whether you’re making small changes or big ones. Let’s learn the easy steps to find your weight loss percentage.

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

Use the following formulas for the respective cells.

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%

Then use the Fill Handle icon for other cells of the F column.
Use the following formula in the H5 cell to get an overall weight loss percentage.

=(D4-OFFSET(E11,COUNT(E11:E14)-1,0))/D4%

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

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.

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.
Lastly, the “st” and “lbs” strings are joined with their corresponding values using the Ampersand (&) operator.

3. Inserting a Weight Loss Chart in Excel

Creating a weight loss chart involves inserting an Excel chart. Then change the chart type to make it clear and understandable. Format the chart with titles, then save the workbook for regular updates.

Whether you’re losing a little weight or a lot, making an Excel chart can keep you motivated. Let’s learn the simple steps to create your own chart.

Select the whole dataset >> go to the Insert tab > select the Recommended Charts.
Then, you will get the Insert Chart window. 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.

Below, I have attached the chart. Also, I have added some Chart Elements.

4. Calculating BMI in Excel

BMI (Body Mass Index) helps you understand if your weight is healthy for your height. Here, we will use an Excel formula for calculating BMI and then will apply the Conditional Formatting.

The basic formula for BMI (Body Mass Index) is: BMI = weight(kg)/ height(meters)^2.
So, we are going to use a formula that will convert the units too.

=CONVERT(C4,"lbm","kg")/(CONVERT(C5,"cm","m"))^2

Note: Here, I converted the lb unit to kg and cm unit to m. Then 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.

Then 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 (18.5) >> click on Format.

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

Then, press OK to the New Formatting Rule dialog box.
Similarly, set the colors using Conditional Formatting for other values.

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

Now, we will assemble the above findings for creating the weight loss tracker.

Follow the formulas of calculating weight loss percentages.
Then, add a new column named Target Weight >> where link G4 cell as absolute reference.
After that, use the formulas for overall weight loss percentage and BMI.

Now, add the chart for tracker. Here, we used Current Weight and Target Weight columns for the chart. So, you are done.

6. Checking the Output of Weight Loss Tracker in Excel

To use a weight loss tracker, enter your starting weight and goal weight, then record your daily or weekly weights. The weight loss tracker puts all your information in one place. Let’s walk through the simple steps of using it, so you can see how far you’ve come and stick to your healthy goals.

Here, you will need to manually fill out the form (marked red from the figure below). Then, you will get the weight loss percentage, and overall weight loss percentage automatically.
It will also automatically calculate the BMI and format the cell according to the color code.
Also, it will add the chart lines to understand the difference between current weight and target weight.

Frequently Asked Questions

1. What is the formula for weight loss?

You have to calculate the weight loss percentage for usage. Which is {(Starting Weight – Current Weight)/ Starting Weight} *100. It is normal to have a 5% change in body weight.

2. How many Calories need to burn to lose 1 kg?

You need to burn 7700 calories to lose 1 kg.

3. How many km to walk to lose 1kg?

Suppose you have a healthy diet. Then, you need to walk a total distance of 117-164 km at a moderate speed. Keep in mind that this depends on various other factors.

4. How much walking to lose 1kg per week?

You have to burn around 500 calories per day. So, you should exercise at least 150-200 minutes per week and walk 10,000 steps per day. Keep in mind that this depends on various other factors.

Conclusion

Here, we have explained how to make an Excel weight loss tracker. We have discussed both the stone-pound and kilogram units system. Also, you have seen how to calculate the weight loss in percentage. Then, we have explained the calculation of BMI. Hopefully, you find it helpful and can track your health for a better life.

Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website. Happy learning!

Excel Weight Loss Tracker: Knowledge Hub

Weight loss spreadsheet in stones and pounds

<< Go Back to Tracker in Excel | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!