How To Build Your Own Health Tracker Using Google Sheets

Most applications on Android or iOS cant fulfill all of your health tracking needs in one application. Some allow you to track your gym progress, while some count your calories. If you find an application that fits all your needs, chances are that it’s a paid application. So what is the best way to do this?

 

It’s simple, just create your own fitness tracker on Google Sheets. The main reason to use Google Sheets is that it is free, and you can use it on almost all platforms like Android, iOS, and PC. We are going to be using several fitness formulas to achieve this. Read on to learn how to make a fitness tracker in Sheets.

What Do I Need to Use the Google Sheets Health Tracker?

This basic health tracker aims to log the calories you burn per day with steps. But, once you learn how to do this it’s easy to add extra parameters too. We recommend having some sort of step counter. You could also download a health app like Google Fit or Apple Health on your phone. 

 

A device compatible with Google Fit can import directly into sheets too with some pretty complicated scripts.

How to Build a Health Tracker Using Google Sheets

First, we need to add dates to our spreadsheet. Although you can use the DATE function to create the dates, I recommend simply writing the first date and using the suggested autofill feature.

1. Here are the steps to do this:
2. Click on the cell where you wish to input the date.
3. Type in the date following the MM/DD/YYYY format.
4. Click on Format in the top bar and click on Number with the cell selected.
5. There, click on Date. This will ensure that the data the cell contains is taken as a date.
6. In the next cell, type in A2+1. This will add one day to the starting date
7. Finally, click on the blue dot at the bottom-right edge of the screen and drag it down to fill in the column with the dates.

 

The next column I’m adding to the spreadsheet is the steps taken during the day. These can be monitored using a mobile application or a fitness tracker, as I mentioned before. You can log these in manually every day, and there is no need to enter a formula here. But, you could use Google Apps Script to import Google Fit steps.

The next column in this spreadsheet will calculate how many calories are burned depending on the steps taken.

In general, 0.04 calories are burned per step for the average person. Keeping that in mind, we can use a simple multiplication formula to calculate the calories burned per day.

Here are the steps to do this:

1. Click on the second cell in the column. We want the formula to apply to the entire column other than cell C1.
2. Use the Ctrl+ Shift+ Down keyboard shortcut to select all the cells underneath.
3. Type in the formula in the FX bar. In this case, the formula is =B2*0.04
4. Press Enter to execute the formula.
5. Now press Ctrl+ D to copy the formula in the entire column.

 

To test whether tho formula works properly, I have added some dummy values in the Steps Taken column. The formula I used works flawlessly, and the best part is that you don’t have to manually reenter the formula when you add the steps.

Now, let’s find the distance traveled using the same method we used above. If you’re male, assure 2.5ft. and use 2.2ft. for females. In this case, I will be using 2.5ft.

Here are the steps to do this:

1. Click on the second cell in the column. We want the formula to apply to the entire column other than cell D1.
2. Use the Ctrl+ Shift+ Down keyboard shortcut to select all the cells underneath.
3. Type in the formula in the FX bar. In this case, the formula is =(2.5 * B2) / 5280
4. Press Enter to execute the formula.
5. Now press Ctrl+ D to copy the formula in the entire column.

To test whether tho formula works properly, I have added some dummy values in the Distance Travelled column. The formula I used works flawlessly, and the best part is that you don’t have to manually reenter the formula when you add the steps. 

Adding Conditional Formatting

If you want to, you could add some conditional formatting and a Google Sheets if then formula to set a goal for steps and highlight the cell in green if you’ve hit the desired amount. Here’s how to do that:

1. Highlight the steps taken column by clicking the B row header
2. Navigate to Format > Conditional formatting
3. In the Conditional formatting rules pane on the right, change the Format cells if dropdown menu to Greater than or equal to
4. Type the desired amount of steps in the Value or formula box

Wrapping Up

Of course, you could add more parameters to your sheet if you were making your own health tracker. For example, you may want to add your calories consumed, whether you’ve been to the gym, etc. You could also add similar conditional formatting to those parameters too.

 

Want to unlock greater wellness?

Listen to our friends over at the Wellness + Wisdom Podcast to unlock your best self with Drew Canole of Organifi: