Habit Tracker Spreadsheet

You know how valuable habits are, and why it’s worth spending the time to track them.

(If you don’t – spend a few minutes reading why I think you should here, and then return back to this page.)

What are some ways that people track their habits?

  1. They don’t. These people are stuck in their subjective sense of how they think things are going (which impacts results and accountability), and they can’t visually see their progress over time.
  2. Pen and paper. Can look nice, but it is time consuming to do and very manual to analyse and keep a record of.
  3. Whiteboard. This gets erased so there is no history, and if you do keep a separate record then it is time consuming.
  4. Apps. These can work well, but it’s hard to find an app that ticks all the boxes for you and is easy to use.

I have spent many hours creating an alternative, which is my Habit Tracker Spreadsheet.

I have made it as automated as possible, and the end result is that it will only take 2 minutes to fill out each week, or under a minute if you use it each day. Whilst being quick to use, it also doesn’t hold back on features and can show you:

  • A simple summary showing you how you’ve been doing overall the past few weeks
  • A more detailed summary which shows how you do in each individual habit, as well as all of them combined
  • What habits you need to complete today or this week, and if each habit is fully completed for that week
  • Shows you how many weeks in a row you have “passed” a habit. Both your current record and your “best of all time”
  • Various graphs so you can visually track your progress over time

All you need to do is input habits when you first begin using the spreadsheet (such as how many times a week you want to do it), and then spend a few minutes each day or week making a note of how you did – and let the spreadsheet do everything else for you in the background. 

Here’s what we’re going to cover:

  1. Download Links
  2. Instructions
    1. Habit Input
    2. Quick Summary
    3. Weekly Summary
    4. Totals Graphs
    5. Analysis Graphs
    6. Habit Snapshot
    7. Tracker – Checkbox Version
    8. Tracker – Typing Version
  3. How To Use The Spreadsheet

On mobile? Bookmark this page, make a note of this website or email the link to yourself – so you can return when you’re back on a computer and can try out the spreadsheet.

I have created two versions of the tracker, and the difference is how you record that your habits are completed. After some initial feedback about the tracker, I learned that people prefer to record them in different ways – so I created both versions.

The difference between the two versions is that one has a checkbox style input (a bit quicker) and the other has a typing style input (some people prefer it). Have a read through the instructions so you understand how the spreadsheet works and you’ll know which one will work best for you.

Click the relevant link below, and you’ll have access to a read-only version of the tracker. To be able to use the spreadsheet, download your own copy by going to File > Make a copy.

Some of the formulas on the spreadsheet use your time zone to see what the current date is. The US version of the tracker is set to Eastern Time, and the EU version is set to GMT.

To change the time zone to your own, go to File > Spreadsheet settings > Time zone.

Some important points to mention:

  • This tracker won’t work over multiple years, so you will need to have a new tracker for each new year. If you start using the tracker in October, then you can only use that specific tracker until 31st December.
  • When you first open it up it might take a few seconds to load, and when you make changes you’ll notice the loading bar come up as well. There shouldn’t be too much of a time delay, there’s just a lot of different formulas and things going on in the spreadsheet that it’s something to bear in mind.
  • When you first open the spreadsheet and don’t have any data inputted, some of the tabs / graphs can look a bit strange. However it will return to normal and look like the below screenshots when you have some data entered.

Instructions

There are 7 tabs in the spreadsheet and they are grouped together by colours, so it’s a bit easier to remember what the tabs do.

Once you have set up a habit, there is only one tab that you need to input the habit data into each day or week, and all of the other tabs are just for your information.

  • Quick Summary (Red = Summaries)
  • Weekly Summary (Red = Summaries)
  • Habit Snapshot (Blue = Input)
  • Tracker (Blue = Input)
  • Totals Graphs (Green = Graphs)
  • Analysis Graphs (Green = Graphs)
  • Habit Input (Yellow = Changes to Habits)

I’m going to walk through setting the habit up, what the information tabs show you and then finally how you will actually record your habits each day / week.

The habit tracker has the ability to track 10 different habits over the course of a year. My view on habits is to track the ones you care about the most, and will have the biggest impact on your life. I have found that the small habits take care of themselves once you tackle the bigger habits.

So instead of trying to track every single habit you have (which would be too much work, without much benefit), track the impactful habits which have a “domino effect” on the smaller habits related to it.

The bigger habits tend to have a “domino effect” where you naturally start to do other beneficial things that are related to that habit. For example, if you make exercising a priority and form that habit – you will likely begin to eat better and go to bed earlier and because they are related. You want to have proper nutrition and eat the right amount of food, and you want your body to be well rested, so you recover quicker and are able to do well during your next gym session. This becomes self-perpetuating because your body feels better, you feel good about yourself, and have more energy throughout the day.

So my habit tracker is designed to target your top 3 – 10 most impactful habits, rather than obsessively tracking 20 – 50 habits.

You can change your targets and stop and start those 10 habits as many times as you want, but throughout the year there can’t be more than 10 different habits.

Action step: Have a think about what your most impactful and important habits are. Try to avoid taking on too many habits at once, and limiting yourself to choose a maximum of 3 to begin tracking.

Bear in mind that it’s not enough to just have an intention to follow through with these habits, you need to plan for them to make sure that you’re actually going to do it.

If you haven’t exercised in a year, you can’t just think “I will now exercise 3 times a week”. You need to create a workout routine, find your gym clothes, maybe cook some healthy meals in advance. You need to set yourself up for the best chance of success at following through with the habits you choose.

 Yellow

Habit Input

This tab is where you set up the habit when you begin to use the spreadsheet. First, input the year that you are going to use the tracker for (this is only relevant for the checkbox version).

When you create a habit, you fill in:

  • Start date (this needs to be a Monday)
  • Habit name
  • Type of habit (daily or weekly – double click the cell and a drop-down menu will appear)
  • How many times a week you want to do it
  • How many times a week you would consider a “pass”
  • Time spent per habit (this is only relevant for the checkbox version)
  • End date (this needs to be a Sunday, and only needs to be inputted when you are “closing” a habit line to create a new one)

My view on habits is that you should aim for 100% target, but be happy with 70% and consider that a “win”. Understanding that ten 70% wins in a row, is better than one or two 100%’s and then a load of 0%’s.

So the section “Target per week” is the 100% target and the “Pass target per week” is the 70%. However, you can decide what you would consider a pass for each habit. Maybe you actually think that both targets should be the same, and that you will only count 100% as a “pass”. Or you could choose 50% or 90% as a pass – it’s up to you.

When you set up a habit in this tab, that means it is now ready to begin tracking on the spreadsheet. What if you want to change the habit target, or “turn off” the habit so it is no longer active? You would just put the “End Date” as the end of the week that you’re turning off the habit, and then create a new habit line with the start date as the beginning of the week you’re going to begin that new habit.

You can change your targets as many times as you want, but I would avoid doing it too often. If you’re not doing well and you change your targets every week or two… then something is going wrong. You’re not being successful at maintaining the habits, and rather than fixing the root cause, you’re just lowering the bar for yourself each week.

I would recommend leaving at least a month between any changes, but it is up to you how to use the tracker. With leaving at least a month (or longer if you’re happy with your targets), you are setting an intention and then you are taking action towards that intention. If you “fall off” for a week, but then bounce back the following week and manage to maintain it, then it’s just part of the process of building that habit.

Habits are personal and individual, and only you know when you need to “turn off” a habit, change the target (increase it or decrease it), or leave the target as it is. And you will get better over time with knowing yourself and how you are with building habits. And having data to look back upon will be super useful because you will be able to spot patterns.

Action Steps: 

1) Input year of the tracker (if using checkbox version of the tracker)

2) Fill out habit information for habits you want to start doing (Start date should always be a Monday, and end date should always be a Sunday)

Red

There are two summaries which automatically summarise things for you, and you can dig into them when you’re curious or looking for insights.

The summaries are: Quick Summary and Weekly Summary.

Quick Summary

This is the more simple summary out of the two, and it is the first tab that will be visible when you open the spreadsheet each time. It lets you see some basic information about how you’ve been doing recently.

It looks at the last 4 weeks and shows you your total success rate, and how that changed from the previous week (whether it increased or decreased, and by how much). The total success rate is the average of all of the individual habit success rates.

So say if I have two active habits: Exercise and reading.

My targets (my 100%) are to exercise 3 times a week, and to read 5 times a week.

If I exercise 2 times during the week, my success rate would be 2 / 3 = 66.66%. And if I read 4 times during the week, my success rate would be 4 / 5 = 80%.

This would give me an average total success rate for the week of 73.33%, which is the “Total Success Rate” that would appear on this tab. So it takes into account all of your habits and how well you’re doing, and gives you a quick overall percentage to show you how you’ve performed.

Next, there is the total number of active habits and it shows if it has changed since the previous week.

Seeing your total success rate and your number of active habits allows you to see the relationship between the two. For example, if you recently increased your number of habits and you see your success rate go down… then it might make you evaluate if you were ready to add another habit in.

This tab is designed to be more of a high-level, overall picture sort of summary – without digging into the details. And because it is the first tab that appears when you open the spreadsheet, you’ll be reminded of how you’ve been doing recently each time you open it.

 Weekly Summary

This tab is where you can look at how things are going each week in a bit more detail.

The first section of data on the left-hand side is related to all of your habits, and shows you:

  • Number of active habits
  • Total habit sessions (How many habits you completed)
  • Your total success rate (Your average across all the habits)
  • Number of passes (How many habits you met the pass target for that week)
  • Total time spent on habits

Then to the right of that, you can see each individual habit’s data. There is:

  • Number completed (How many times you completed the habit for that week)
  • Success rate
  • Whether you passed or not
  • How much time you spent

If the habit isn’t active during the week, then it will show “Not active” in the data. So say if you begin a habit in the first few months of the year, and then make it inactive, and pick it back up towards the end of the year – then you won’t see loads of zeros in-between. This lets you see at a glance that the habit wasn’t active during that period, and also doesn’t throw any of the figures off (like the average total success rate).

If you have up to 10 habits, then this tab could get quite complicated and difficult to use. To help with this, I have built functionality to be able to choose which habits you want to see in detail. At the top of the sheet, you can check the boxes for the ones that you want to appear. You can see this below:

For example, if you wanted to compare your 1st and 5th habit and there wasn’t this functionality, then that would mean scrolling across the spreadsheet and probably getting lost and forgetting the figure you just had in your head. This way you could just uncheck all of the other habits and leave the 1st and 5th habits checked and it will be much easier to compare.

Unchecking these boxes has no effect on any of the totals or analysis, and it’s just a visual thing rather than actually removing the data – so there’s no need to worry.

Green

Next we’re going to have a look at the graphs that are available. All of the graphs are based on the same data that is available in the weekly summary, but displays it visually through graphs rather than having to look at numbers.

Totals Graphs

There is one column of graphs for the number of habits completed per week, and the other column shows how much time is spent each week.

As you go down the sheet, it goes through each of your habits. It starts with all habits at the top, and then shows each individual habit.

This tab allows you to see at a glance how you have been doing. Sometimes it can be easier to look at these graphs rather than looking through the Weekly Summary tab. Both have their advantages, so it depends on what you’re looking to do and what you prefer.

Analysis Graphs

There are three columns of graphs: Success rate, number of active habits per week, and number of habits passed per week. Like the Totals Graphs, as you go down the sheet is goes through the different habits.

Blue

Habit Snapshot

The habit snapshot looks at your active habits and gives you the current targets for them. You can change your targets for habits over time, but the habit snapshot will show the targets for the active habit line from the input tab – and no inactive habits will be shown here.

It also shows you whether you have completed the habit today, counts how many times you have completed it in the current week – and whether that means you have “passed” the habit for the week yet.

On the right-hand side it shows you current pass streaks for each habit, and also what your longest pass streaks are. This can help keep you motivated to continue passing that habit each week, and to try and set a new “high score” for that habit.

You can use this tab throughout the day or the week, to know where you currently are with completing each habit, and remind yourself what your targets are.

Tracker

Finally there is the tracker tab, which is where you will actually record your habit completion data.

This tab is the main difference between the checkbox version and the typing version.

Tracker – Checkbox Version

All of your habits for each week are displayed in a grid, with checkboxes for each day and habit. Each week has it’s own section, and the weeks go horizontally across the spreadsheet towards the right-hand side.

Whenever you open the spreadsheet, it will automatically open on the left-hand side of this sheet. It would be quite inconvenient to have to scroll all the way to the right and try and find the week that you are on, each time you open the spreadsheet again.

To help with this, I created functionality that lets you select a week, and then jump to that week. On the left-hand side of this sheet (so where it automatically opens to when you open the spreadsheet again) there is a drop-down cell and a button:

Once you have selected the week you want to jump to, simply hover over the “Go to Week” button and click the blue hyperlink that appears. If you experience an issue with the hyperlink not appearing, try: 1) Deleting the year in the Habit Input tab, and entering it again and 2) Refreshing the spreadsheet.

Once you are on the relevant week, all of your active habits will have a checkbox that you can tick. Habits will only appear on each week if they are active according to your input tab. For example, if you have 2 active habits in February and 4 active habits in March, all of the weeks for February on this tab will only have the 2 habits that were active during that time.

When you check a box to say that a habit has been completed, it will appear in the section below the checkboxes and you will see that it has populated the duration based off the time you entered in the input tab. If you know you spent more or less time than this default time that appears, just input the time manually over the formula. There is also a section for you to input some notes for the habit.

Because there are a lot of formulas on this tab, once you have checked a box it might take a second for it to appear in the section below. There is no time delay for you to check the habit boxes, so you can check all your boxes first and then change any times or write some notes in the section below. You don’t have to check a habit box one by one and wait for it to appear below.

Tracker – Typing Version

With the typing version, there is the same data as the checkbox version, but you just type it vertically instead of checking boxes.

The day of the week is a formula (you can remember by noticing the column heading is grey), so you don’t have to type this, but you fill in:

  • Date
  • Habit (This is a drop-down cell, so if you double click it, all of your currently active habits will appear)
  • Duration (This needs to be entered in hours, such as “0:48” or “1:23”)
  • Any necessary notes

Some people prefer this version, because you can see all of your previous habits in one table – rather than having it in separate weeks going horizontally like the checkbox version. It might take a little bit longer to fill out, because checking boxes is quicker than typing.

If you don’t know right now which version you might prefer, then maybe download both versions and have a play around with them before you begin using either one. Because the way they work are a bit different, they’re not compatible with each other. So you can’t begin using the checkbox version for a few months, and then transfer the data to the typing version. You need to use one or the other for that year.

How to use the spreadsheet

Let’s quickly recap the basics of how to use the spreadsheet:

1) Decide on a few “domino habits” to get started
2) Input the habits into habit input (remember: Start date needs to be a Monday, and when you make a change to a habit and create a new line, the end date for the old line needs to be a Sunday)
3) Record which habits you have completed throughout the day / week (and refer to the habit snapshot if needed)
4) When curious or looking for insights, check the weekly summary, or if you want to see the data represented in graphs – then go to the totals and analysis graphs tabs

So what are some ways you can practically use this spreadsheet to record your habits?

1) Keep the spreadsheet open (or bookmark it in your browser so it is accessible), and fill out which habit you have completed as soon as you’ve done it
2) Record throughout the day on a piece of paper, your phone, or a whiteboard, which habits you have completed and how long you spent doing it. Then at the end of the day, fill that information out in the spreadsheet
3) Same as number 2, but rather than doing it daily, doing it weekly instead (my personal method)

My way of using this spreadsheet is to use a whiteboard (or paper / phone when not at home) and to create a grid with the days of the week and each habit. Whenever I complete a habit, I would just write the time of how long I spent doing it. If there is no time next to the habit, then I know I didn’t do it.

So hopefully you have some ideas of how you can practically use this spreadsheet, but do whatever works for you.

And that’s it! I hope you enjoy using the spreadsheet and that it helps you build your habits.

Find this spreadsheet useful and feel like sharing?

Want me to create a spreadsheet for you? Learn more about working with me here

Don't want to miss out on future content?

Subscribe to my email list

Have a spreadsheet in mind?

Let me take care of it for you

Copyright © 2019

Don't want to miss out on future spreadsheets?