Habit Tracker Google Sheets Template That Counts Streaks

A habit tracker Google Sheets template that counts streaks needs two formulas the download pages won't show you, and both are printed below. One reads today's streak for each habit off a row of checkboxes. The other finds the best streak you've had this month. Add a completion rate, an in-cell progress bar, a daily column chart, and a date header that blanks itself after the 28th, 30th or 31st, and the whole thing is one tab, about fifteen minutes to type in, on the free Google account you already have.

Every function is checked against Google's own Docs Editors Help, loaded on 15 September 2026. I haven't run this sheet in front of a thousand people. I've checked each formula against the documented syntax, and I'll tell you the three places it behaves in a way that looks like a bug and isn't.

The ranking pages for this search are template directories, an email-gated download, a Reddit advert for a sheet with a "Built-in Streak System," and an Etsy market. Not one of them prints a formula. If you'd rather download something, the Reddit one is at position three. If you'd rather understand it, keep reading.

What The Templates Keep To Themselves

The blog post at position seven is about 650 words around a copy link, with a Progress tab that "automatically will count how many checks" against a frequency goal, so four times a week becomes "about 16 runs" a month. Sensible. No formula on the page, no streaks, no chart.

The directory page at position two describes habits down the left, checkboxes across four or five weeks, "automated progress tracking charts" and a completion percentage, with an Edit Template button and an Excel version. The one at position four describes ten sheets, 400-plus habits, 15 categories and reward amounts, and asks for your email before it gives you the file.

The pattern is the same as the weekly planner in Google Sheets piece found. The file is the product and the article is the advert, so the formulas stay in the file. My objection isn't to the download. It's that you can't see the rule, so you can't change it. That planner covered checkboxes, COUNTIF and a per-day task filter. This one is about the thing a planner doesn't do, which is count consecutive days.

The Tab, Cell By Cell

Rename the tab Habits. The layout is a month across and habits down, with the sums to the right of the last day.

Now the formulas.

B1      =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

B3      =$B$1
C3      =IF(B3="","",IF(B3+1>EOMONTH($B$1,0),"",B3+1))        (copy across to AF3)

AH4     =COUNTIF(B4:AF4,TRUE)
AI4     =MAX(0,MIN(TODAY(),EOMONTH($B$1,0))-$B$1+1)
AJ4     =IFERROR(AH4/AI4,0)                                    (format as percent)
AK4     =SPARKLINE(AJ4,{"charttype","bar";"max",1})
AL4     =IFERROR(AI4-MAX(FILTER(SEQUENCE(1,31),B4:AF4=FALSE,SEQUENCE(1,31)<=AI4)),AI4)
AM4     =IFERROR(MAX(ARRAYFORMULA(LEN(SPLIT(JOIN("",ARRAYFORMULA(IF(B4:AF4=TRUE,1,0))),"0")))),0)
        (fill AH4 to AM4 down to row 13)

B15     =COUNTIF(B4:B13,TRUE)                                  (copy across to AF15)
AH15    =SPARKLINE(B15:AF15,{"charttype","column"})

Conditional formatting, all on the Habits tab, Format > Conditional formatting > Custom formula is:
        range B3:AF15    =B$3=""          (grey fill, days this month doesn't have)
        range B3:AF15    =B$3=TODAY()     (light fill, today's column)
        range B4:AF13    =B4=TRUE         (green fill, a ticked day)

That's the template. The rest of this is what each part does, because that's what lets you change the rule when the rule stops fitting your life.

Why The Header Blanks Itself

B1 is the first of the current month, built from TODAY with DATE, YEAR and MONTH, all of which Google documents as taking a date and returning the part you asked for. You can type a date into B1 instead, and if you want last month's tracker, type the first of last month. Type an actual date, though. Google's DAY page carries the warning that applies to every date function here, that a literal like 10/10/2000 inside a formula is read as ten divided by ten divided by two thousand. A date in a cell is fine.

The header row is the part that lets one sheet serve February and August. B3 is the first of the month. Every cell after it adds a day unless that day would land past EOMONTH, which Google defines as "the last calendar day of the calculated month," and then it goes blank. The outer IF is there because a blank plus one is an error, so once one header goes blank the rest follow it. The grey rule reads those blanks and shades the columns nobody can tick.

The headers stay real dates underneath the day-only format, and that matters for the today rule, which compares the header to TODAY. Type the numbers 1 to 31 as text instead and the highlight never fires. That's the first place retyping usually goes wrong.

Why The Streak Reads Zero In The Morning

AI4 is how many days of the month have happened so far. Take the earlier of today and the month's last day, subtract the first of the month, add one, and floor it at zero so a tracker for a month that hasn't started reads 0 rather than a negative number. Everything to the right divides by it or stops at it.

AL4 is today's streak, and it works backwards. SEQUENCE(1,31) is the numbers 1 to 31 across one row, which Google's page says is what you get when you "specify rows as 1 and specify columns." FILTER keeps only the day numbers where the checkbox is unticked and the day is on or before today. MAX of those is the most recent day you missed. Today's day number minus that is the streak. If you haven't missed a day yet, FILTER returns #N/A, which Google's page says it does when nothing matches, and IFERROR turns that into AI4, the whole month so far.

Now the behaviour that looks broken. Open the sheet at eight in the morning, before you've ticked today, and the streak reads 0. That's correct by the formula, because today is on or before today and today is unticked, so today is the most recent miss. It's also, I think, the right nag. The streak is the reason you tick the box.

If you'd rather see last night's streak until you tick, change the three references to AI4 in that formula to AI4-1, and the search stops at yesterday. It'll read minus one on the day before a month starts, which is harmless. Pick one version and don't mix them, because the two disagree by one every morning.

Why The Best Streak Is A Piece Of String

AM4 is the longest run you've managed this month, and it works by turning the row into text. It's the formula I'd expect questions about, so here's the walk-through.

The inner ARRAYFORMULA maps each checkbox to a 1 or a 0. JOIN with a blank delimiter, which Google's page says is allowed, glues them into a 31-character string, something like 1111011111100111. SPLIT on the character "0" then breaks that string wherever there's a miss, and because Google's SPLIT treats "consecutive delimiters as one" by default, a run of misses collapses to a single break, leaving only the runs of ones. LEN under ARRAYFORMULA measures each run and MAX takes the longest. For that example string it's 6.

A row with no ticks at all is a string of zeros with nothing between them, so there's nothing to split into. Google's page doesn't say what SPLIT returns in that case, which is why the whole thing sits inside IFERROR and reads 0. That's the second thing that looks like a bug and isn't, and it only shows on a habit you haven't started.

The best streak counts future ticks too. If you tick Thursday on Tuesday, the run includes Thursday. Don't tick ahead.

The Bar And The Chart Without A Chart Object

Two of the related searches for this query are "with progress bar" and "with graph," and neither needs Insert > Chart.

AK4 is SPARKLINE with a bar chart type and max set to 1, which is Google's own sample syntax with the number changed. AJ4 is a fraction between 0 and 1, so the bar fills in proportion, and a habit at 60 percent is a bar three fifths across the cell. Set "color1" in the same options if you want it a colour, per the SPARKLINE page.

AH15 is the month chart. Row 15 counts ticks per day down the ten habit rows, and SPARKLINE with the column type draws one column per day inside one cell. A good week is a plateau. A bad week is a gap. It's not a dashboard, and it's the fastest honest picture of the month I know how to make in a spreadsheet.

The rate in AJ4 has the third odd behaviour. It divides ticks by days so far, and if you've ticked a future day the rate goes over 100 percent. Same fix as above. Tick the day on the day.

What Each Piece Costs To Change

The Excel row deserves a sentence. The directory page at position two offers an Excel version of its template, which tells you nothing about whether this one survives the trip. I've done the check against Google's function list and against nothing of Microsoft's, so treat this as a Sheets template.

Where The Sheet Stops

It doesn't remind you. Nothing here will buzz at nine. If a habit needs a nudge, it's a recurring event with a length, and the Google Calendar for time management setup shows how to build that on a free account and where recurring tasks fall short.

It doesn't do anything after the tick. A tick is a tick. The task tracking app comparison is about what apps do once you've marked something done, and a spreadsheet does nothing, which is either the point or the problem.

It doesn't know about the phone. Google's checkbox help has Android and iPhone versions of the page, so ticking on a phone is supported, and editing a 31-column grid on a phone is the part I'd avoid. I'd tick on the phone at night and read the streaks on a laptop.

What it replaces is the paid habit apps, for one person, at zero. The apps for time management comparison prices the timers and trackers if you want to see what the alternative costs. A sheet has no tier. The cost is that you built it.

Questions From The Related Searches

Is there a habit tracker template for Google Sheets? This one, retyped. The download pages are the same grid with a logo and the formulas hidden inside the file.

Can Google Sheets do a habit tracker with a progress bar? Yes, in the cell, with SPARKLINE's bar type and max set to 1, which is the AK column above. No chart object, no drawing.

Can it do one with a graph? The AH15 cell is a column chart of ticks per day, again with SPARKLINE. For a line instead of columns, change "column" to "line" in that cell, which Google lists as the default chart type.

Is there a free daily habit tracker for Google Sheets? The grid is daily by construction. Free because it's a sheet on a free account and there's nothing to download.

Is there a Google habit tracker app? Not on the Google pages I loaded for this piece or the calendar one. Calendar gives you a recurring event with a reminder, and Sheets gives you this. Neither Google app I've looked at counts a streak on its own, which is what this sheet is for.

What about Excel and PDF versions? Print the tab if you want a paper copy. For Excel, see the table above and test it yourself.

What I'd Do

Type it in tonight, from the code block, and don't download anything. Put three habits in, not ten. Tick them for a week and watch AL. On the morning it reads 0, notice whether that bothers you. If it does, the sheet is working.

At the end of the month, duplicate the tab, paste values on B1, and start the next one clean. And when you first retype it, check that B3 shows a date when you clear the format, because a header typed as text is the mistake I'd expect to make myself.