Weight Tracker in Excel

So in this video, we're going to make a weight tracker which will look something like this

So you have the depth there, you have your actual weight, your height, your body mass index and the status

So this status is based on the body mass index. So if your BMI is beyond 30, it will show a piece here

For example, if we change this one to 100, you can see the status changes as well

Then if your BMI is below 18.5, it will show that you are underweight

So let's change this one, let's say to 30. And you can see it says underweight here

So we're going to learn how we can find the BMI and how we can use conditional formatting for this status

You can also visualize your weight over time using this graph here

So this shows how your weight has been changing over time. For example, if we take back this to, let's say 70, you can see it's reflected here on the graph

So let's see how we can make this one. So to save some time, I've already created this table here

I've also gone ahead to add all the dates that we're going to use. So I'm going to assume that we're checking our weight weekly

So these dates are based on a weekly checkup. So first of all, we're going to format these dates

I'll go on the format here and I'll change it to long date here so that it's more clear like that

So this means that I'll be checking my weight every Saturday like so

Then let's try it for this one. So let's say the weight of 70 and the height is 1.69 meters like so

Now to calculate the body mass index, we need to divide the weight by the square of the height

So we're going to say equals weight here divided by, then in the brackets

I'll say height multiplied by height so that it becomes height squared like so

So this is our body mass index. I just leave it to two decimal places like so

Then for our status, we're going to be checking this body mass index value

If it is less than 18.5 here, we're going to say underweight

If it is above 30, we're going to say obese. So we're going to use the is function here

We'll say equals is. So for the logical test, we're going to say if this value is less than 18.5

then we're going to say underweight here. For the next test, we're going to look at this value again

So this time, we want it for the normal weight. If it's above 18.5, all the way to 24.9

So to do that, we're going to use the and function inside the is function

And we'll say if this value is greater than 18.5, the same value is less than 25

If this is the case, we're going to say normal. Then if it's above 25 to 29.9, so again, we'll use the and function

We'll say it's greater than 25. And this same value is less than 30

Then we're going to say overweight. By the way, this should be normal

And for the last condition, we're going to check if this value is greater than 30

And we're going to say obese. Then we'll close the is function

So just to quickly go through what we are saying here is, we're checking if this value is less than 18.5

If that's the case, we're going to say you're underweight. If this value is between 18.5 and 25, we're going to say the BMI is normal

If this value is above 25 but less than 30, we're going to say overweight

And if it's beyond 30, we're going to say obese. So just press enter

And here you can see because it's less than 25, we're going to say normal

So we can add another entry here. Let's say the weight increased to 76

The height is still at 1.69. And here we can just drag down the formula

You can see now this is beyond 25. So we expect the status to be overweight

So again, here I'll drag the formula. And we can see here it's overweight

So let me just quickly add in the weight and the height for the rest of this

Then we can just drag down the formula for the rest. OK, now that we have entered the weight and the height here

we can simply drag down the formula. And just double click here and the formula will be applied

Again, for the status, I'll do the same. There we go. Now the next thing we're going to do is to use conditional formatting

to highlight this status here. So when the status is normal, it should be highlighted in green

When it's obese, it should be highlighted in red. When it's underweight, it should be highlighted in yellow

So to do that, we just select everything. Then I'll click on conditional formatting here

Say highlight cell rows, then we'll go to echo tool. So here I'll type in normal

Then I'll choose from here, I'll choose green fill and click OK

So you can see everything that is normal is highlighted in green

I'll select this column once more. Go back to conditional formatting, highlight cell rows, then echo tool

This time around I'll choose overweight. And here I'll use the custom format

So for the fill, I'll choose this orange here. And for the font, I'll choose this one here and click OK

So you can see it's done there. Again, while this column is highlighted, I'll go back to conditional formatting, echo tool

Then this time around, I'll say obese, then I'll choose the dead fill and click OK

And finally, go back to the same one. This time around, I'll say underweight

Then I'll choose yellow from here and click OK. So if we are underweight, let's say this one is 40, for example

You can see this one becomes yellow like that. Now that we have this data for our weight

let's do a graph that shows how our weight changes over time

So just put the date and the weight. To do that, just highlight the date and the weight like so

Then go and insert here. Then you can choose what type of graph that you want

So I'll just click on this drop down here and I'll choose this 2D area like that

OK, let's just scroll down for more space. OK, so just click on this graph, right click, then go to format data series

click on the package here and in the field drop down, I'll just choose gradient

In the border, just choose a solid line and I'll leave it in black like that

Now we're able to see how our weight is changing over time

So if I change this back to say 80, so we can see how our graph is moving

So this will help us visualize how our weight is changing. At the moment, you can see it's increasing

and this will tell us that we need to keep our weight in check. So I'll leave a link to this weight tracker in the description below

If you want, you can download it for free. Thank you so much. I'll see you in the next one