Habit apps work because of one mechanism: the visible streak. Miss a day, watch the number reset, and you show up tomorrow. The problem is that most habit apps charge a subscription for a grid and a counter.
A spreadsheet does the same job for free — and with one clever formula, it counts your streaks automatically. Here’s how to build it.
The layout: habits as rows, days as columns
Create a Weekly Grid sheet. Row 5 is the header: Habit | Mon | Tue | Wed |
Thu | Fri | Sat | Sun | Current streak | Week %.
Rows 6–15 hold up to 10 habits. You mark a day with any character — x, ✓,
even 1. The formulas below only check whether the cell is empty.
Two design rules:
- Habits as rows, days as columns — a week fits on one screen without scrolling.
- Cap at 10 habits, use 3–5. The grid allows ten because people will try ten; research and experience both say three to five is where tracking actually changes behavior.
The streak formula
Here’s the heart of the tracker. For habit in row 6, the current streak (column I) counts backward from Sunday:
=IF(H6<>"",1,IF(G6<>"",2,IF(F6<>"",3,IF(E6<>"",4,IF(D6<>"",5,IF(C6<>"",6,IF(B6<>"",7,0)))))))Read it as a cascade: if Sunday is marked, the streak is at least 1; if Saturday is also marked, it’s 2; if Friday too, 3… The first empty day from the right ends the count. Miss Sunday and the streak reads 0 — honest, immediate, exactly the feedback loop that makes streaks work.
Copy the same formula down every habit row (adjusting the row number).
Weekly completion percentage — same row, column J:
=COUNTIF(B6:H6,"<>")/7Format as percent. This is the number that tells you a habit is fading before the streak hits zero.
Make it visual
Two conditional formatting rules do the heavy lifting:
- The grid: select B6:H15 → Format → Conditional formatting → Custom
formula
=B6<>""→ green fill. Completed days become a green field you can read from across the room. - The streaks: select I6:I15 → Greater than or equal to
7→ green bold. A perfect week should look like a trophy.
Add a day-total row at the bottom (=COUNTIF(B6:B25,"<>")) to see which day of
the week you’re strongest — most people are surprised.
The monthly view
A weekly grid shows discipline; a monthly grid shows trajectory. On a Monthly View sheet, days run down the rows (1–31), habits across the columns. Bottom
row counts completions per habit:
=COUNTIF(B5:B35,"<>")And the row below turns that into a completion rate (=B36/31) with a data bar
so the best habit is obvious at a glance. Set the print area to landscape,
fit-to-one-page — a paper version on the fridge outperforms any app
notification.
Goals and reflection
Two optional tabs turn tracking into progress:
Goals — milestones with a progress bar:
=IFERROR(D2/C2,0) → current ÷ target, formatted as percentData-bar conditional formatting on that column gives you a filling progress bar per goal: “Run 500 km” with 312 done shows 62% filled. Concrete, motivating, free.
Reflection log — date, day rating (1–5 dropdown), energy, highlight of the
day, tomorrow’s one thing. Two honest minutes at night. The summary row
(=AVERAGE(B5:B42)) shows whether your habits are actually moving your mood —
the connection most people never measure.
Why this beats an app
- No subscription. The file is yours, forever, in both Google Sheets and Excel.
- No lock-in. Your data is a plain spreadsheet you can export, chart, or hand to a coach.
- Customizable. Ten habits, thirty, monthly, weekly, team challenges — the formulas don’t care.
The trade-off is real: no push notifications. If you need an app to remind you, use one — but track the streak here, where you can see the whole year at once.
Skip the build
The complete system — weekly grid with automatic streaks, monthly view with completion bars, goal milestones, reflection log with auto-summary, all pre-formatted and print-ready — ships in our Habit & Goal Tracker. Copy it, replace the sample habit names, and start marking days tonight.
Don’t track ten habits to prove you can. Track three to prove you will.
Related reading: