habit tracker

There are three stages in a habit;

1. Trigger – something that starts your habit routine.
2. Routine – the habit behaviour, the action your take.
3. Reward – the benefit you gain from the behaviour.

When trying to adopt a new habit, I find the hardest stage is the trigger. Remembering to do the routine and reward. To solve this problem, I created a habit tracker in Google Sheets. This post describes that habit tracker, and the formula within it.

habit tracker basics

If I am to stick with the tracker, it needs to be;

1. easy to complete
2. beautiful
3. available everywhere (all devices)
4. simple

I chose Google Sheets, because of it’s power and simplicity.

one tab per month

I created a Sheet with the days as columns, and the habits to track as rows. I use one tab in the sheet for each month. Each to “track” the completion of a habit, I insert an “x” into the cell.

I added a column at the end that counts the number of “x” entries, and displays how many times that month I have completed the habit.

1

=COUNTIF(B3:AF3, "x")

not all habits have the same frequency

I quickly discovered that it was hard to see when I was doing well, or poorly, on a particular habit. Not all my habits are daily. I should water the plants every week or so, and use liquid plant food every other week.

To solve this, I introduced a “success range” column. This column tracks the number of days until I need to re-do the habit. With this column, I added Conditional Formatting for the green sections, based on the “x” tracking.

Apply to range:

1

B3:AF993

Custom Formula is:

1

=COLUMNS($B3:B3)-MATCH("x",$B3:B3, 1)<$AH3

track warning and failure zones

I added extra formatting ranges to show the “warning” and “failure” zones. These are defined as 2x and 3x the “success range”.

When I track a habit, the Conditional formatting updates to show the success, warning, and failure ranges.

Warning range:

1

=ISBETWEEN(COLUMNS($B3:B3)-MATCH("x",$B3:B3, 1), $AH3, $AH3*2, TRUE, FALSE)

Failure range:

1

=COLUMNS($B3:B3)-MATCH("x",$B3:B3, 1)<$AH3*3

habits are visible

This tracker works because the habits are visible, so it your progress against them. It’s easy to use, and can be updated on all your devices (with access to Google Sheets).