Google Sheets Habit Tracker Template (Free, 2026)
A Google Sheets habit tracker is a grid with dates down one axis, habits across the other, and a checkbox in every cell. It takes about ten minutes to build: insert checkboxes across the grid, count the ticks with COUNTIF, and count streaks with SCAN and LAMBDA. The part almost every published template gets wrong is the completion percentage, because it divides the ticks by the whole month instead of by the days that have already happened, so a perfect first five days of a 31 day month display as 16 percent instead of 100 percent. The fix is to build the denominator from the date column with COUNTIFS and TODAY, not from the checkbox range. This page gives a template you can import into Google Sheets in one click, every formula in it, and the four failure modes that silently return zero.
Most free Google Sheets habit tracker templates hand you a completion percentage that is wrong. Tick every box for the first five days of the month and the sheet congratulates you with 16 percent, because it divided your five ticks by all 31 days including the 26 that have not happened yet. It is a one line fix, almost nobody publishes it, and it is the difference between a tracker you trust and one you quietly stop opening. Here is a template that gets it right, and every formula inside it.
What is the best free Google Sheets habit tracker template?
The best template is the smallest one whose formulas are actually correct: a date column, five habit columns, and a dashboard holding current streak, longest streak and month to date completion rate. Anything beyond that is decoration you will maintain instead of using. Ours is a CSV file, it costs nothing, and it asks for no email address.
Download the habit tracker template (CSV), then in Google Sheets open File > Import > Upload and drop the file in. Leave the option Convert text to numbers, dates and formulas ticked, otherwise every formula arrives as plain text and nothing computes.
| Where | What is in it |
|---|---|
| Column A, rows 2 to 32 | 31 date rows that fill themselves in for the current month on the day you import, and blank out past the month end |
| Column B | Weekday name, so weekend patterns are visible at a glance |
| Columns C to G | Five habit columns, pre-filled with FALSE and ready for checkboxes |
| Columns H and I | Habits done today, and the share of today's habits done |
| Dashboard block, rows 35 to 38 | Current streak, longest streak and month to date completion rate for each of the five habits |
Two steps stay manual after the import, and no file format can carry them: select C2:G32 and use Insert > Checkbox, then add the conditional formatting rules further down this page. That takes under a minute. Any template that claims otherwise is either a Google Sheet you have to copy from someone else's Drive, or it is not really a spreadsheet.
Habit tracker spreadsheet. A grid in which each row is a date, each column is a habit, and each cell records whether that habit was done that day as a yes or a no. Summary formulas turn the grid into two numbers: how many days in a row the habit held, and what share of elapsed days it covered.
How do you create a habit tracker in Google Sheets from scratch?
Five steps, about ten minutes, and the only one people get wrong is the first. Column A must hold real date values, not text that looks like dates. If you type them as strings, every formula that compares a row against TODAY fails without complaining, which is the single most common reason a home built tracker reports nonsense.
- In A2, enter
=DATE(YEAR(TODAY()),MONTH(TODAY()),1). In A3, enter=IF(MONTH(A2+1)=MONTH($A$2),A2+1,"")and fill down to A32. The sheet now tracks the current month and stops on its own at 28, 30 or 31 days. - Put your habit names in C1 to G1. Each one has to resolve to a yes or a no at a fixed time of day, so "read more" is not a row but "20 pages before lights out" is. We wrote up 150 habit tracker ideas rewritten as tickable conditions if the rows are the part you are stuck on.
- Select C2:G32 and use Insert > Checkbox. Every cell now holds the boolean TRUE or FALSE.
- Add the streak and percentage formulas from the two sections below.
- Freeze row 1 and column A through View > Freeze, so the habit names stay visible when you scroll to day 24.
What formula counts a habit streak in Google Sheets?
SCAN with LAMBDA counts a streak in a single cell, with no helper column: it walks down the column, adds one for every ticked day, and resets to zero the moment it meets a miss. Wrap it in MAX for the longest streak the habit ever reached this month.
=IFERROR(MAX(SCAN(0, FILTER(C2:C32, A2:A32<>""),
LAMBDA(acc, val, IF(val=TRUE, acc+1, 0)))), 0)The current streak needs one extra guard. Days later in the month are unchecked, and an unchecked box is a genuine FALSE, so a naive version reads those future misses and reports a streak of zero every single day. Filtering the range to dates at or before TODAY is what fixes it.
=IFERROR(LET(
days, FILTER(C2:C32, A2:A32<>"", A2:A32<=TODAY()),
run, SCAN(0, days, LAMBDA(acc, val, IF(val=TRUE, acc+1, 0))),
INDEX(run, ROWS(run))
), 0)SCAN, LAMBDA and LET are Google Sheets functions with no direct equivalent in most Excel tutorials, which is why nearly every habit tracker guide online still teaches the 2019 method: a hidden helper column with =IF(C2=TRUE, N(M1)+1, 0) filled down, then MAX over it. That still works, and it has one real advantage, which is that you can see the running count row by row while you debug. It costs you a column per habit.
Why does your habit tracker show the wrong completion percentage?
Because the denominator counts days that have not happened yet. The formula published almost everywhere is =COUNTIF(C2:C32, TRUE) / COUNTA(C2:C32), and COUNTA over a checkbox range returns 31, not the number of days elapsed. FALSE is a value, not a blank. So on 5 August, a perfect record reads 5 divided by 31.
Take the denominator from the date column instead. COUNTIFS counts the rows whose date has already arrived, which is the only number your percentage should ever be measured against.
=IFERROR(COUNTIF(C2:C32, TRUE) /
COUNTIFS($A$2:$A$32, "<="&TODAY(), $A$2:$A$32, ">="&$A$2), 0)Format the result with Format > Number > Percent. Do not multiply by 100 and glue on a percent sign, because that produces text and any chart built on the cell will refuse it.
This is not a rounding quibble. A tracker that shows 16 percent for a perfect week teaches you that the effort is not landing, in exactly the first days when a new habit is most fragile. Lally and colleagues at University College London found in 2010 that automaticity took a median of 66 days, with individuals spread between 18 and 254 days, so the early weeks are precisely the stretch you have to survive on encouragement rather than results. A number that under-reports you by six times during that stretch is not a neutral bug.
A tracker that shows 16 percent for a perfect week teaches you that the effort is not landing, in exactly the days when a new habit is most fragile.
There is a second reason to prefer a percentage over the streak counter that most templates put front and centre: a streak resets to zero for one missed day and takes the month's whole record with it, while a completion rate absorbs the miss and keeps counting. We argued that case in full in streaks versus consistency. Keep both cells in the sheet, but read the percentage.
How do you colour the grid with conditional formatting?
Use Format > Conditional formatting > Custom formula is, applied to the range C2:G32. Five rules cover everything worth seeing. Sheets reads the rules from top to bottom and the first one to set a given property wins, so the order in this table is the order to enter them.
| What you want to see | Custom formula | Format |
|---|---|---|
| Today's row, so you find it instantly | =$A2=TODAY() | Bold text |
| Days not yet due, so they read as neutral | =$A2>TODAY() | Grey text, no fill |
| Done | =C2=TRUE | Green fill |
| Missed, but only in the past | =AND($A2<TODAY(), C2=FALSE) | Red fill |
| Weekends | =WEEKDAY($A2, 2)>5 | Light grey fill |
What can a Google Sheet not do?
It can record, and that is genuinely worth something, but it cannot prompt you and it cannot involve anyone else. Recording matters: Harkin and colleagues, in a 2016 meta-analysis in Psychological Bulletin covering 138 studies and 19,951 participants, found that monitoring progress raised goal attainment with an effect size of d+ = 0.40. The same analysis found the effect was larger when progress was physically recorded, and larger again when it was reported to someone else. A spreadsheet delivers the first of those three and neither of the other two.
That is the honest shape of the trade. A sheet is free, private, infinitely customisable, and yours forever. It also sits behind three taps on a phone, never says anything on the evening you are tired, and notices nothing when you stop opening it. Nobody abandons a habit spreadsheet because the formulas were bad. They abandon it because nothing happens when they do.
Where StellarHabit fits, and where it does not
StellarHabit is a habit tracker built around the third of Harkin's conditions, the one a spreadsheet structurally cannot reach: your habits are visible to friends you choose, and progress is a consistency percentage rather than a streak, so a single missed day does not erase the month. It runs in any browser, so there is nothing to install and no app store in the way.
It is not the right tool for everything. If you want to track 40 things, invent your own scoring, or keep the data entirely to yourself, the spreadsheet on this page is the better answer and you should keep it. StellarHabit is built for a small number of habits that you have already failed to hold on your own, where the missing ingredient is somebody noticing. If that describes you, the practical next step is choosing the right person, and we covered how in how to find an accountability partner.
Frequently asked questions
How do I make a habit tracker in Google Sheets?
Put dates in column A and habit names across row 1, select the grid where they meet and use Insert > Checkbox, then add two formulas. COUNTIF(C2:C32, TRUE) counts the ticks for one habit, and SCAN with LAMBDA turns the same column into a streak. Budget about ten minutes. The one step people skip is making column A hold real date values rather than typed text, because every formula that compares a row against TODAY breaks silently when the dates are strings.
Is there a free Google Sheets habit tracker template?
Yes. The template on this page is a CSV file you import through File > Import > Upload in Google Sheets, with no email address and no paid upgrade. It arrives with 31 self-populating date rows, five habit columns, and a dashboard block holding current streak, longest streak and month to date completion rate for each habit. Two manual steps remain after import, because no file format can carry them: selecting the grid and using Insert > Checkbox, and adding the conditional formatting rules.
What formula counts a habit streak in Google Sheets?
SCAN(0, range, LAMBDA(acc, val, IF(val=TRUE, acc+1, 0))) returns a running count that adds one for every ticked day and resets to zero on a miss. Wrap it in MAX for the longest streak, or read its last element with INDEX(run, ROWS(run)) for the current streak. Filter the range to dates at or before TODAY first, otherwise the unchecked future days of the month reset your current streak to zero every time.
Why does my Google Sheets habit tracker show the wrong percentage?
Almost always because the denominator counts days that have not happened yet. COUNTA over a checkbox range counts every cell in the month, since an unchecked box holds FALSE and FALSE is a value rather than a blank. On day five of a 31 day month, a perfect record then reads 5/31, which is 16 percent. Build the denominator from the date column instead, with COUNTIFS($A$2:$A$32, "<="&TODAY(), $A$2:$A$32, ">="&$A$2).
Why does COUNTIF return 0 on my checkboxes?
Because the boxes were created through Data validation with custom cell values rather than through Insert > Checkbox. A custom checkbox stores your own text, for example Done and Not done, so the cell never contains the boolean TRUE and COUNTIF(range, TRUE) matches nothing. Nothing shows an error, the count just sits at zero. Either switch to plain Insert > Checkbox, or change every formula to match your custom text instead of TRUE.
Can Google Sheets track habits that are not daily?
Yes, but count them against a weekly quota instead of a streak. A three times a week habit in a daily grid records four misses every week even when you follow the plan perfectly, and a consecutive day streak formula on that column is meaningless. Use COUNTIFS to count the ticks inside each week and compare that number to your target. Keep daily rows and quota rows in separate blocks, because mixing them makes the completion rate at the bottom of the sheet uninterpretable.
Sources
- Harkin, B., Webb, T. L., Chang, B. P. I., Prestwich, A., Conner, M., Kellar, I., Benn, Y., & Sheeran, P. (2016). Does monitoring goal progress promote goal attainment? A meta-analysis of the experimental evidence. Psychological Bulletin, 142(2), 198–229. Reports 138 studies, N = 19,951, d+ = 0.40 for goal attainment, with larger effects when progress was physically recorded and when it was reported to someone else.
- Lally, P., van Jaarsveld, C. H. M., Potts, H. W. W., & Wardle, J. (2010). How are habits formed: Modelling habit formation in the real world. European Journal of Social Psychology, 40(6), 998–1009. Median of 66 days to reach automaticity, individual range 18–254 days.
- Google. Google Sheets function list. Reference documentation for COUNTIF, COUNTIFS, FILTER, LET, SCAN and LAMBDA, all used in the template on this page.
- Google. Use conditional formatting rules in Google Sheets. Documents the custom formula option and how relative references are resolved against the applied range.