Guide

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

  1. 1Dates down column A, one habit per column, a checkbox in each cell.
  2. 2COUNTIF gives days done; divide by days so far for your completion rate.
  3. 3A helper column counts streaks: +1 when done, back to 0 when missed.

Step 1: the layout

Build it in 15 minutes

  1. 1Dates in column AOne row per day
  2. 2Habits across row 1One column per habit
  3. 3CheckboxesIn every habit cell
  4. 4Summary formulasDone, rate, this week
  5. 5Streaks and colorsHelper column, formatting
The order to build the sheet. Each step is explained below with the exact formulas.
  1. In A1 type Date. In B1, C1, D1 type your habits, for example Read, Walk, Journal.
  2. In A2 type your start date. In A3 type =A2+1 and fill it down to row 366. You now have a full year.
  3. Select B2:D366 and add checkboxes.
    • Google Sheets: Insert > Checkbox.
    • Excel: recent Microsoft 365 versions have Insert > Checkbox. If yours does not, type x in a cell when you do the habit, and use the "x" versions of the formulas below.
  4. 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

9 days in a row, at bestDone: 25 · Missed: 3 · Rest day: 0
  • Done
  • Missed
  • Rest day
An example of four weeks of one habit after conditional formatting, one row per week. Gaps stand out immediately.

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

%
12345678

Week

An example: 4, 5, 5, 6, 5, 6, 7 and 6 days done out of 7, shown as rounded percentages. Your sheet produces the same kind of line.

Setup checklist

Your habit spreadsheet

0 / 7

Tick each part as you build it. Your ticks are saved in this browser.

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 remindersA phone alarm each evening

Fiddly on a phoneBookmark it, keep few columns

Weekly schedules are awkwardUse the this week formula

Easy to forget to openTick at the same time daily

Common pain points of spreadsheet habit tracking, and the usual workaround.

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