Proper Spreadsheets

Templates › Personal

Habit Tracker

Made for people building routines.

List your habits and how often you want to do each, log each day, and see your current streak, days hit this month and best run for every habit. The summary carries a chart that redraws itself as you type.

Of the 17 columns, you fill in 8 and the other 9 work themselves out, and all 8 figures on the summary do too. There is room for 2,530 rows of your own.

£4.99one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

Already have a spreadsheet of your own? Check it for mistakes, free.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic. What the film shows, in order:
  1. A new row is typed into the Daily Log tab, one cell at a time, in the white cells a person fills in.
  2. The shaded cells in that row fill themselves in.
  3. The figures on the Summary tab change to include the new row.
  4. The chart, Days done and missed, recounts with it.
The chart, drawn from the totals in the table beside it. See it full size
Where you type, and the shaded columns that work themselves out. See it full size

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 2,530 rows. Open any tab to see its columns.

Habits

9 columns

One row per habit you are building, with your weekly target. Streaks and monthly counts fill in from the Daily Log. Ready for 30 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
HabitType any text.
CategoryPick one: Health, Fitness, Mind, Work, Home, Social or Other.
Target Days Per WeekHow many days a week you want to do this habit. 7 means every day.Type a number.
StartedType a date.
Current StreakConsecutive days done, ending today or yesterday.Fills itself in.

Worked out from this row's Habit, using Habit, Date, Done and Streak On This Day on Daily Log.

The formulaIF(ISBLANK([habit]), "", IF(COUNTIFS([Daily Log!habit], [habit], [Daily Log!date], TODAY(), [Daily Log!done], "No")>0, 0, MAX(SUMIFS([Daily Log!run_length], [Daily Log!habit], [habit], [Daily Log!date], TODAY()), SUMIFS([Daily Log!run_length], [Daily Log!habit], [habit], [Daily Log!date], TODAY()-1))))
Best RunLongest run of consecutive days you have ever logged as done.Fills itself in.

Worked out from this row's Habit, using Streak On This Day, Habit and Best Day Flag on Daily Log.

The formulaIF(ISBLANK([habit]), "", IFERROR(SUMIFS([Daily Log!run_length], [Daily Log!habit], [habit], [Daily Log!is_top], 1)/COUNTIFS([Daily Log!habit], [habit], [Daily Log!is_top], 1), 0))
Days Hit This MonthFills itself in.

Worked out from this row's Habit, using Habit, Done and Date on Daily Log.

The formulaIF(ISBLANK([habit]), "", COUNTIFS([Daily Log!habit], [habit], [Daily Log!done], "Yes", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY()))
Target So Far This MonthDays you would have hit by today if you kept to your weekly target.Fills itself in.

Worked out from this row's Habit and Target Days Per Week.

The formulaIF(OR(ISBLANK([habit]), ISBLANK([target_per_week])), "", ROUND([target_per_week]*DAY(TODAY())/7, 0))
On TrackFills itself in.

Worked out from this row's Habit and Target Days Per Week, using Habit, Done and Date on Daily Log.

The formulaIF(OR(ISBLANK([habit]), ISBLANK([target_per_week])), "", IF(COUNTIFS([Daily Log!habit], [habit], [Daily Log!done], "Yes", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY())>=ROUND([target_per_week]*DAY(TODAY())/7, 0), "Yes", "Behind"))

Daily Log

8 columns

One row per habit per day. Pick the habit and say whether you did it. The grey columns work out your runs; leave them alone. Ready for 2,500 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
DateType a date.
HabitPick one of the Habit entries on Habits.
DonePick one: Yes or No.
NotesType any text.
Run Start1 when this day begins a new run. Worked out for you.Fills itself in.

Worked out from this row's Date, Habit and Done, using Habit, Date and Done on Daily Log.

The formulaIF(OR(ISBLANK([date]), ISBLANK([habit])), "", IF(AND([done]="Yes", COUNTIFS([Daily Log!habit], [habit], [Daily Log!date], [date]-1, [Daily Log!done], "Yes")=0), 1, 0))
Run NoWhich run this day belongs to. Worked out for you.Fills itself in.

Worked out from this row's Date, Habit and Done, using Habit, Run Start and Date on Daily Log.

The formulaIF(OR(ISBLANK([date]), ISBLANK([habit])), "", IF([done]="Yes", COUNTIFS([Daily Log!habit], [habit], [Daily Log!run_start], 1, [Daily Log!date], "<="&[date]), ""))
Streak On This DayFills itself in.

Worked out from this row's Date, Habit, Done and Run No, using Habit, Run No and Date on Daily Log.

The formulaIF(OR(ISBLANK([date]), ISBLANK([habit])), "", IF([done]="Yes", COUNTIFS([Daily Log!habit], [habit], [Daily Log!run_no], [run_no], [Daily Log!date], "<="&[date]), 0))
Best Day Flag1 on the day your longest run peaked. Worked out for you.Fills itself in.

Worked out from this row's Date, Habit and Streak On This Day, using Habit and Streak On This Day on Daily Log.

The formulaIF(OR(ISBLANK([date]), ISBLANK([habit])), "", IF(COUNTIFS([Daily Log!habit], [habit], [Daily Log!run_length], ">"&[run_length])=0, 1, 0))

Summary

8 figures

How your habits are going overall.

FigureWorked out as
Habits tracked

The number of rows on Habits with Habit filled in.

The formulaCOUNTA([Habits!habit])
Days hit this month, all habits

Worked out from Done and Date on Daily Log.

The formulaCOUNTIFS([Daily Log!done], "Yes", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY())
Days missed this month, all habits

Worked out from Done and Date on Daily Log.

The formulaCOUNTIFS([Daily Log!done], "No", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY())
Hit rate this monthDays done as a share of days logged this month.

Worked out from Done and Date on Daily Log.

The formulaIFERROR(COUNTIFS([Daily Log!done], "Yes", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY())/COUNTIFS([Daily Log!done], "<>", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY()), 0)
Longest current streak

The largest Current Streak on Habits.

The formulaMAX([Habits!current_streak])
Best run on any habit

The largest Best Run on Habits.

The formulaMAX([Habits!best])
Habits on track this month

The number of rows on Habits where On Track is “Yes”.

The formulaCOUNTIF([Habits!on_track], "Yes")
Habits behind this month

The number of rows on Habits where On Track is “Behind”.

The formulaCOUNTIF([Habits!on_track], "Behind")