sheetsmith

Templates › Personal goals

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.

Tabs
3
Columns
17
Fill themselves in
17

£7one 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.

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.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

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.

Habits

One row per habit you are building, with your weekly target. Streaks and monthly counts fill in from the Daily Log. Room for 30 rows.

ColumnTypeWorked out as
Habittext
Categorychoice (list)
Target Days Per Weeknumber
Starteddate
Current StreaknumberIF(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 RunnumberIF(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 MonthnumberIF(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 MonthnumberIF(OR(ISBLANK([habit]), ISBLANK([target_per_week])), "", ROUND([target_per_week]*DAY(TODAY())/7, 0))
On TracktextIF(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

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. Room for 2,500 rows.

ColumnTypeWorked out as
Datedate
Habitchoice (from Habits › habit)
Donechoice (list)
Notestext
Run StartnumberIF(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 NonumberIF(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 DaynumberIF(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 FlagnumberIF(OR(ISBLANK([date]), ISBLANK([habit])), "", IF(COUNTIFS([Daily Log!habit], [habit], [Daily Log!run_length], ">"&[run_length])=0, 1, 0))

Summary

How your habits are going overall.

MeasureWorked out as
Habits trackedCOUNTA([Habits!habit])
Days hit this month, all habitsCOUNTIFS([Daily Log!done], "Yes", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY())
Days missed this month, all habitsCOUNTIFS([Daily Log!done], "No", [Daily Log!date], ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), [Daily Log!date], "<="&TODAY())
Hit rate this monthIFERROR(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 streakMAX([Habits!current_streak])
Best run on any habitMAX([Habits!best])
Habits on track this monthCOUNTIF([Habits!on_track], "Yes")
Habits behind this monthCOUNTIF([Habits!on_track], "Behind")