Habit Tracker Spreadsheet: Google Sheets and Excel
Build a habit tracker spreadsheet in Google Sheets or Excel: the layout, checkboxes, and working formulas for completion rate, this week, and streaks.
November 11, 2026 · 5 min read
To build a habit tracker spreadsheet, put dates in column A, one habit per column next to it, and a checkbox in each cell. Then add a few formulas: COUNTIF for days done, a division for your completion rate, and a small helper column for streaks. It takes about 15 minutes in Google Sheets or Excel, and every formula below is tested and works in both.
The layout uses one row per day, not one column per day. That way the sheet grows downward forever, and the formulas stay simple.
The short version
- 1Dates down column A, one habit per column, a checkbox in each cell.
- 2COUNTIF gives days done; divide by days so far for your completion rate.
- 3A helper column counts streaks: +1 when done, back to 0 when missed.
Step 1: the layout
Build it in 15 minutes
- 1Dates in column AOne row per day
- 2Habits across row 1One column per habit
- 3CheckboxesIn every habit cell
- 4Summary formulasDone, rate, this week
- 5Streaks and colorsHelper column, formatting
- In A1 type
Date. In B1, C1, D1 type your habits, for exampleRead,Walk,Journal. - In A2 type your start date. In A3 type
=A2+1and fill it down to row 366. You now have a full year. - Select B2:D366 and add checkboxes.
- Google Sheets: Insert > Checkbox.
- Excel: recent Microsoft 365 versions have Insert > Checkbox. If yours does not, type
xin a cell when you do the habit, and use the"x"versions of the formulas below.
- Freeze the first row (View > Freeze) so the habit names stay visible.
A checked box is the value TRUE, an unchecked or empty cell is FALSE. That is what the formulas count.
Step 2: the summary formulas
Put a small summary block to the right, starting in column F. These formulas are for the habit in column B. To copy them for column C, change B to C.
| What | Formula (checkboxes) |
|---|---|
| Days done | =COUNTIF(B$2:B$366,TRUE) |
| Days so far | =COUNTIF($A$2:$A$366,"<="&TODAY()) |
| Completion rate | =COUNTIF(B$2:B$366,TRUE)/COUNTIF($A$2:$A$366,"<="&TODAY()) |
| Done this week (from Monday) | =COUNTIFS($A$2:$A$366,">="&(TODAY()-WEEKDAY(TODAY(),2)+1),B$2:B$366,TRUE) |
| Done in the last 7 days | =COUNTIFS($A$2:$A$366,">"&(TODAY()-7),$A$2:$A$366,"<="&TODAY(),B$2:B$366,TRUE) |
Format the completion rate cell as a percentage. If you type x instead of using checkboxes, replace TRUE with "x" in each formula, for example =COUNTIF(B$2:B$366,"x").
WEEKDAY(TODAY(),2) returns 1 for Monday and 7 for Sunday, in both Google Sheets and Excel, so the "this week" formula counts from the most recent Monday. If your spreadsheet uses a language where formulas take semicolons, replace the commas with semicolons.
Step 3: streaks
Streak formulas get complicated in one cell, so use a helper column. Put it far to the right (for example column M) and hide it if you like.
| Cell | Formula | What it does |
|---|---|---|
| M2 | =IF(B2=TRUE,1,0) |
Starts the count |
| M3 | =IF(B3=TRUE,M2+1,0) |
Adds 1 if done, resets if not; fill down to M366 |
| Best streak | =MAX(M$2:M$366) |
The longest run so far |
| Current streak | =MAX(IFERROR(INDEX(M$2:M$366,MATCH(TODAY(),$A$2:$A$366,0)),0),IFERROR(INDEX(M$2:M$366,MATCH(TODAY()-1,$A$2:$A$366,0)),0)) |
The run up to today, or up to yesterday if today is not ticked yet |
The current streak formula looks at today and yesterday and keeps the larger number. That way your streak does not show 0 in the morning just because you have not done the habit yet.
Rest days that do not break the streak
Checkboxes have only two states. If you want planned rest days (illness, travel, a day off), use a dropdown instead: Insert > Dropdown in Google Sheets, or Data > Data Validation > List in Excel, with the options Done and Rest.
| What | Formula (dropdown) |
|---|---|
| Streak, first row | =IF(B2="Done",1,0) |
| Streak, next rows | =IF(B3="Done",M2+1,IF(B3="Rest",M2,0)) |
| Completion rate | =COUNTIF(B$2:B$366,"Done")/(COUNTIF($A$2:$A$366,"<="&TODAY())-COUNTIF(B$2:B$366,"Rest")) |
A rest day keeps the streak where it was, and it is removed from the days you count, so it does not lower your rate.
Step 4: colors and a chart
Color makes the sheet readable at a glance, like a heatmap.
- Google Sheets: select B2:D366, Format > Conditional formatting, "Custom formula is"
=B2=TRUE, pick a green fill. - Excel: select B2:D366, Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format",
=B2=TRUE, pick a fill.
To highlight today's row, select A2:D366 and add a second rule with =$A2=TODAY().
What the colored sheet shows
- Done
- Missed
- Rest day
For a chart, add a weekly summary: one row per week with the count of days done, then Insert > Chart. Seeing weekly totals is more encouraging than staring at single misses.
Weekly completion from the sheet
Week
Setup checklist
Your habit spreadsheet
0 / 7
Keep the habit list short. Three to five habits is plenty; habits to track helps you choose.
The limits of a spreadsheet
Spreadsheets are flexible and free, and building one teaches you exactly how your stats are calculated. But they have honest weaknesses.
Where a sheet struggles
LimitWorkaround
No reminders→A phone alarm each evening
Fiddly on a phone→Bookmark it, keep few columns
Weekly schedules are awkward→Use the this week formula
Easy to forget to open→Tick at the same time daily
The biggest one is friction. Opening a spreadsheet on your phone every evening is a small chore, and small chores are exactly what habits avoid. If you find yourself skipping the sheet, the habit is fine; the tool is in the way.
That friction is why we built Moto: a check off from a widget or the Mac menu bar, reminders per habit, flexible schedules, and the same stats (completion trends, a year heatmap) without formulas. If you prefer paper, see the bullet journal habit tracker, and for criteria to compare apps, our habit tracker app guide.
FAQ
How do I calculate a habit completion rate in Google Sheets?
Divide the number of days done by the number of days so far, =COUNTIF(B2:B366,TRUE)/COUNTIF(A2:A366,"<="&TODAY()), then format the cell as a percentage. The first part counts checked boxes, the second counts dates up to today. The same formula works in Excel.
Can I count a streak in Excel without a helper column?
It is possible with array formulas, but they are long and hard to fix when something breaks. A helper column that adds 1 for each done day and resets to 0 on a miss is easier to read and works the same way in Excel and Google Sheets. You can hide the column once it works.
Do Excel checkboxes work with these formulas?
Yes. Checkboxes in Excel for Microsoft 365 store TRUE or FALSE, just like in Google Sheets, so the formulas work unchanged. In versions without checkboxes, type an x and replace TRUE with "x" in the formulas.
Should I track habits in a spreadsheet or an app?
A spreadsheet is great if you like building tools and you sit at a computer every day. An app is usually easier on a phone, with reminders and one tap check offs. Try the sheet for a month and notice whether you open it without effort.
Keep Reading
Habit tracker widgets: put your next action in sight
Choose habit tracker widgets for your iPhone or Mac, decide what to show, and build a simple setup that makes your next habit easy to notice and check off.
November 11, 2026 · 6 min read
IdeasHabits of Successful People: An Honest Look
Habits of successful people that keep showing up (planning, reading, sleep, exercise, reflection), why copying routines misleads, and how to adapt them.
November 11, 2026 · 4 min read
GuideHabits to Build Confidence: Small Promises, Kept
Confidence grows from evidence. Daily habits that build confidence: keep tiny promises, do one hard thing, prepare, look after your body, track it.
November 11, 2026 · 4 min read