Google Sheets habit tracker: build one in 10 minutes
A habit tracker in Google Sheets from a blank sheet, with checkboxes, a current-streak formula, a completion percentage and colour. Every formula is here to copy. No template to download, no sign-up.
Layout
One sheet per month, or one long sheet with a date per column. The monthly version is easier to read; the long version is easier to keep. Start with the monthly one.
| A | B | C | … | AF | AG | AH | |
|---|---|---|---|---|---|---|---|
| 1 | Habit | 1 | 2 | … | 31 | Done | Streak |
| 2 | Meditate | ☐ | ☐ | … | ☐ | =… | =… |
| 3 | Read | ☐ | ☐ | … | ☐ | =… | =… |
Row 1 holds the day numbers, column A the habit names, the last two columns the stats. Keep it to twelve rows; past that the sheet stops being a glance and becomes a report.
Step by step
- Day numbers. In B1 type
1, in C1 type=B1+1, drag to AF1. For a month with 30 days, delete the last column or ignore it. - Checkboxes. Select B2:AF13, then Insert, Checkbox. Each cell becomes TRUE when ticked, FALSE when not. That is what the formulas count.
- Days done. In AG2:
=COUNTIF(B2:AF2, TRUE)- Completion rate for the month so far. Replace AG2 with this if you prefer a percentage that only counts days that have happened:
=COUNTIF(B2:AF2, TRUE) / DAY(TODAY())Format the column as a percentage.
- Current streak. In AH2, this counts consecutive ticked days ending at today’s column:
=IFERROR(
DAY(TODAY()) - MATCH(FALSE, INDEX(B2:AF2, 1, SEQUENCE(1, DAY(TODAY()), DAY(TODAY()), -1)), 0) + 1,
DAY(TODAY())
)It reads today’s column backwards, finds the first unticked day, and counts from there. If nothing is unticked, the streak is the day of the month. It resets on the 1st, which is the limitation of the monthly layout.
-
Colour. Select B2:AF13, Format, Conditional formatting, “Is equal to” TRUE, pick a fill. Ticked cells become coloured blocks and the sheet starts to look like the grid every habit app uses.
-
Shade weekends. Add a row of weekday letters in row 14 with
=TEXT(DATE(2026, 9, B1), "ddd")and a second conditional format on the columns where that row is “Sat” or “Sun”.
Making it last past month one
- Duplicate the sheet on the last day of the month, rename it, and untick everything. Do not build a new one.
- Put it on the phone home screen as a shortcut, or it stays a laptop thing and the evening ticks stop.
- Do not add a dashboard until month three. Charts are the point where a tracker becomes a project and the project replaces the habits.
The long-sheet version, for people who will keep it past one month
The monthly sheet resets the streak on the first of the month, which is wrong: a streak that crosses from the 31st to the 1st is still a streak. The long sheet fixes that by putting every day in one row, with dates going down instead of across.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Meditate | Read | Run |
| 2 | 2026-09-01 | ☐ | ☐ | ☐ |
| 3 | =A2+1 | ☐ | ☐ | ☐ |
Column A is a date; type the first one and put =A2+1 below it, then drag. Habits go across the top. The streak formula for a habit in column B becomes a helper column that counts consecutive TRUEs ending today:
=IF(B2, IF(ROW()=2, 1, E1+1), 0)in E2, dragged down; the current streak is then =LOOKUP(2, 1/(A:A<=TODAY()), E:E), which returns the value on today’s row. It is uglier than the monthly version but it never resets, and you can freeze the top row and scroll for years.
Conditional formatting that actually reads at a glance
One colour per habit is enough. Two rules that make the sheet readable:
- Ticked cells filled with the habit colour, unticked cells left blank. Not red for missed: a wall of red on a bad week teaches you to stop opening the sheet.
- Today’s row or column highlighted with a pale fill, using a custom formula like
=A2=TODAY()on the long sheet or=B$1=DAY(TODAY())on the monthly one, so your eye lands where the tick goes.
Skip the emoji, the progress bars and the motivational quote row. Every one of those adds friction to the four-tap tick on the phone, which is already the weak point.
Sharing it with someone
The genuine advantage of a sheet over any app: share the link with a partner, a coach or a friend doing the same challenge, and they see the ticks live. Two people on one sheet, each with their own columns, tick each other’s boxes when the other forgets, and that accountability is worth more than any streak animation. Give them edit access, not just view, or the second person will stop looking within a week.
The formulas, explained
For anyone who wants to change them rather than paste them:
COUNTIF(range, TRUE)counts ticked checkboxes, because a checkbox cell holds TRUE or FALSE.DAY(TODAY())is today’s day of the month, used to only count days that have happened.SEQUENCE(1, n, n, -1)builds the list n, n-1, … 1, which letsINDEXread the row backwards from today.MATCH(FALSE, ..., 0)finds the first unticked day scanning backwards; the streak is the number of days before it.IFERROR(..., DAY(TODAY()))handles the case where every day so far is ticked, which would otherwise error.
Change AF to the column of your last day if you cut the sheet to 30 columns, and change DAY(TODAY()) to a fixed number if you are filling in a past month.
Common mistakes, and the fix for each
| Symptom | Cause | Fix |
|---|---|---|
| Streak shows 0 although today is ticked | The formula reads columns B to AF but today’s column is outside that range (a 31-column month on a 30-column sheet, or the other way) | Match the range to the number of day columns |
| Streak shows the whole month on day 3 | IFERROR fired because MATCH found no FALSE, which is correct: every day so far is done | Nothing to fix; it is right |
| Percentages over 100% | Ticks in future days | Clear future columns, or restrict the range to DAY(TODAY()) |
| Checkboxes turn into TRUE/FALSE text | The cells were pasted from another sheet without formatting | Re-insert checkboxes via Insert, Checkbox |
| Sheet is slow on the phone | Conditional formatting applied to entire columns | Limit the rules to B2:AF13 |
| The tick is missed most evenings | Four taps is too many at 10 pm | Add the sheet to the phone home screen, or move the daily tick to an app and keep the sheet for the monthly review |
When a spreadsheet is the right tool
It is easy to read this page as “a sheet is worse than an app”. It is not; it is a different tool. A sheet wins when you want to combine habit data with other numbers (weight, sleep hours, money), when two people share one tracker, when you already run your life in Sheets, or when you want a chart of anything at all. An app wins on the daily tick and on reminders. Plenty of people run both: the app for the tap, a monthly export into the sheet for the numbers.
Where the sheet loses to an app
The four-step tick on the phone is the real cost, and it shows up around week three when the habit is still fragile and the tracker needs to be effortless. An app is one tap from the lock screen. If you notice you are ticking yesterday’s boxes in the morning, that is the signal. Virtue is a free one-screen tracker that does the daily tap with no account, and the sheet can stay as the monthly review it is good at.
Frequently asked questions
Is there a free Google Sheets habit tracker template?
You do not need one. The build below takes ten minutes and you will understand every cell, which matters when you want to change it in month two. If you would rather not build, the printable tracker is the same grid on paper.
Can I use it on my phone?
Yes, in the Google Sheets app, and ticking a checkbox works. It is slower than a habit app: open Sheets, find the file, find today's column, tap. Four steps versus one. That is the main reason people move to an app after a month.
How do I make the streak formula count the schedule, not the calendar?
You cannot easily. The formula below counts consecutive ticked days ending today. A habit you do three times a week needs a different formula or a rolling 7-day count, which is included as the completion rate.
Does it sync?
Yes, that is the one thing Sheets does better than an offline app. Same file on phone, laptop and tablet, and you can share it with a partner or a coach.
