Tracking Habits In Google Sheets

Post number 2 of “The Practical Google Sheets Guide”

These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.

TLDR: This post walks you through the steps to create a basic habit tracker. The Google Sheet can be copied directly from the link:
https://docs.google.com/spreadsheets/d/1lRwOKNlrbFqx8g8vx8OUccAXkQC__ugaUAoUfpZXUUo/copy

Creating Habits Through Habit Trackers

Habit trackers are a great way to build a habit and track your progress over time. Building a basic habit tracker in Google Sheets is easy and almost made for spreadsheets. In the following post we will go through a simple habit tracker to track and monitor a potential new goal. I am trying my best to get a certain number of exercises a day, to help me take a break from my work, but you could potentially apply this template for anything.

What You Need To Know To Get This Done?
For todays post, to create our simple habit tracker in not too much time, with some handy features you can bring to a lot of other work you might be doing. In this piece of work, you will need to be able to:

• Add checkboxes to your work and recording the results from these checkboxes
• Use the SPARKLINE to create visualisations to show your progress at a glance
• Use COUNTIF to provide data on the number of checkboxes that have been ticked each week
• Use the SUM function to then generate a basic chart to display a count of your monthly habit totals.

All the functions mentioned can be found at the following reference page, in case you need to double check how the function works: @strava2hive/my-google-sheets-function-reference

Setting Up Our Habit Tracker

The following steps will walk you through how to create the habit tracker on your own Google Sheet, but of course we always have a template for you to use, if you need it quicker.
1.Start by setting up the basic format of the habit tracker. In my example, I have set up a 5 week habit tracker, where I have created a checkbox for each day of the week.

• Set up the main heading in the A1 cell and format it to bold and a larger font size
• Have your weeks running from cell A3 to A7, in the example below, I have 5 weeks listed. Across the top in row 2, I have the number of days running from 1 to 7.
• Add the checkboxes, by highlighting the area range from B3 to H7 and select “Tick Box” from the Insert menu.
  

2.We can now set up a count for each week and a progress bar to allow us to see a visual progress for each week.

• We add our COUNTIF function in cell I3 as a count of the checkboxes from B3 to H3, and only if the value of the checkbox is “TRUE”:
  =COUNTIF(B3:H3,TRUE)
• Copy this formula to the rest of the cells in column I
• Add a sparkline with the formula to display the data visually in I3. This formula will be in cell J3 as a bar charttype in the form of:
  =SPARKLINE(I3,{"charttype","bar";"max",7;"color1","green"})
• Copy the SPARKLINE formula to the rest of the cells in column J. In the image below, I have set the “color1” option to a different value for each week

3.We can now create some extra visualisations at the bottom of the habit tracker. In the final image, we have a single value chart showing the total of the entire habit tracker, as well as a column chart showing the totals for each week.

• The value for the single value chart will be copied over, and in my example I have added it to the A10 cell. This will be a simply SUM of the values in column I as:
  =SUM(I3:I7)
• We can now add the chart to the sheet by selecting the A10 cell and select Chart from the Insert menu. Select a “Scorecard Chart” from the types menu, and name the chart as “Monthly Total”
• For the column chart, we could do something similar to the previous step, but instead we will us another SPARKLINE. We can start by merging the cells by select cells C9 to I14 and selecting “Merge Cells” from the Format menu. Then select “Merge All”
• Then add a SPARKLINE to the merged cell, using the values in column I as
  =SPARKLINE(I3:I, {"charttype","column"; "color", "blue";"max", 7})

This was a great and quick little tutorial. We started with a blank canvas and set up a basic format for the habit tracker, in an easy to use and follow format. We added in checkboxes for the user to track their habits daily. We added in formulas to help visualise progress, including the use of Sparklines, with a totals chart to see overall success.

About The Author

The post is written by Vincent Sesto, a Aussie Software Engineer, living and working in Auckland, New Zealand. If you are interested in my authors page on Amazon, feel free to checked it out at the following link: https://us.amazon.com/stores/author/B073R3VW2G