How to make a habit tracker in Excel or Google Sheets, with a streak formula and a success rate

Last updated: 4 November 2026 · By Quicksloth

You don't need a template to track habits in a spreadsheet. One sheet, a column per habit, a drop-down with a ✓ and a handful of short formulas give you each habit's success rate against your own target and its current and longest streak. Here is how to build it, cell by cell, with the same formulas in Excel and Google Sheets, and a check against real numbers so you know yours is right.

Short version: put one row per day and one column per habit, and allow only ✓ in the grid. Count the ticks with COUNTIF(range,"✓"). Success is the ticks divided by days counted × days per week ÷ 7, rounded and capped at 100%. For streaks, add one helper column per habit that counts up by one on every ✓ and goes back to 0 on a blank: its largest value is the longest streak, and its value on your last day is the current one.

The layout: one sheet, three parts

Everything lives on one sheet, so every formula can see the grid. The example tracks three habits in columns B to D; with more habits, the pattern is the same (see the end of the next sections).

Where	What you put there
Rows 1 to 7, column A	Labels: Habit, Days per week, Done, Days counted, Success, Current streak, Longest streak
Row 1, columns B to D	Your habit names, written so "done" is obvious: Read 20 pages, Spanish lesson (15 min), No-spend day
Row 2, columns B to D	Each habit's target in days per week: 7, 5, 5
Rows 3 to 7, columns B to D	The formulas of each habit (below)
A9 and B9	"Stats through" and the last day you ticked anything
Row 10	Headers of the grid: Date, the habit names, an empty column E, Logged, and one Run column per habit (G, H, I)
Rows 11 to 375	One row per day of 2027: the date in A, your ✓ in B to D, the helper columns in F to I

The summary sits on top so you see it without scrolling; the empty column E keeps the helper columns apart from the habits you tick.

The dates

Type the first day in A11 and let each row add one day:

A11   =DATE(2027,1,1)
A12   =A11+1          then fill down to A375 (31 December)

2027 has 365 days, so the grid ends on row 375. A leap year like 2028 has 366: fill down to row 376 and use 376 instead of 375 in every formula on this page. Next year, change the year in A11 and clear the ticks.

A drop-down with ✓ as the only choice keeps the grid clean: an "x", a 1 or a stray space can't sneak in and be counted, or not counted, by mistake. Copy the ✓ from this page when you set it up. Select the grid first: B11 to D375.

Excel: on the Data tab of the ribbon, select Data Validation. On the Settings tab, in the Allow box, select List, and type ✓ in the Source box. Keep Ignore blank and In-cell dropdown checked, then select OK. (Microsoft Support: Create a drop-down list.)
Google Sheets: click Data → Data validation → Add rule. Under "Criteria", choose Dropdown, type ✓ as the only item, and click Done. Anything that isn't on the list is rejected, unless you change it under Advanced options. (Google Docs Editors Help: Create an in-cell dropdown list.)

Prefer checkboxes in Google Sheets? Insert → Checkbox works too, as long as the checked value is ✓: under Data → Data validation, choose Checkbox, click Use custom cell values and type ✓ next to "Checked" (Add & use checkboxes). Then every formula below works unchanged. A ✓ drop-down is the version that works the same way in both programs.

Two helper columns: last day and runs

Two kinds of helper column do the work that would otherwise need array formulas. Type them in row 11 and fill down to row 375:

F11   =IF(COUNTIF(B11:D11,"✓")>0,A11,"")      Logged: the date, if you ticked anything that day
G11   =IF(B11="✓",1,0)                       Run of the habit in column B (fill right to I11)
G12   =IF(B12="✓",G11+1,0)                   from row 12 on: one more than yesterday, or 0 (fill right to I12)
B9    =MAX(F11:F375)                          Stats through: the last day with a ✓

Logged (column F) holds the date of every day you ticked at least one habit. Its largest value, in B9, is Stats through: every number on top counts up to that day. If you don't open the file for a week, the days you haven't filled in yet don't drag your rates down. If B9 shows a number instead of a date, format it as a date.
Run (columns G, H and I, one per habit, in the same order as the habits) is the number of days in a row with a ✓, up to and including that day. A blank day puts it back to 0.

With more habits, keep the same order: the habits, one empty column, Logged, then one Run column per habit. Logged then counts the ✓ across all your habit columns, and each Run column sits as many columns to the right of its habit as G sits to the right of B.

Done and success rate

Type these in column B and fill right to D. The $ signs keep the rows and the Stats through cell fixed while the columns move.

B3   Done           =COUNTIF(B$11:B$375,"✓")
B4   Days counted   =IF($B$9=0,0,$B$9-$A$11+1)
B5   Success        =IF(B4=0,"",MIN(1,B3/MAX(1,ROUND(B4*B2/7,0))))

Done counts the ✓ and nothing else. Every ✓ is on or before Stats through, because Stats through is your last ✓.
Days counted runs from the first day of the grid to Stats through, both included.
Success compares Done with the days your target asks for: days counted × days per week ÷ 7, rounded to a whole day. MAX(1,…) avoids a division by zero on the first days, and MIN(1,…) caps it at 100%: doing a habit more often than you planned is the target met, not 102%. Format B5 to D5 as a percentage.

These formulas assume every habit starts on the first day of the grid. Why a target in days per week matters, and what to do with a habit you add in February, is on our New Year habit tracker page.

Current and longest streak

The longest streak is simply the largest number in the habit's Run column. The current streak is the Run value on the Stats through day, which is row number B4 of the Run column:

B7   Longest streak   =MAX(G$11:G$375)
B6   Current streak   =IF(B4=0,0,INDEX(G$11:G$375,B4))

That current streak has one flaw: if you tick one habit this morning, Stats through becomes today, and every habit you haven't ticked yet shows a streak of 0 until you do. Today isn't over, so for those habits the streak should still count up to yesterday. This version does that, and it's the one to use:

B6   Current streak   =IF(B4=0,0,IF(INDEX(G$11:G$375,B4)>0,INDEX(G$11:G$375,B4),IF(AND($B$9=TODAY(),B4>1),INDEX(G$11:G$375,B4-1),0)))

Fill both right to D, so column C reads the Run column H and column D reads I. Tomorrow, an empty yesterday breaks the streak, as it should.

Check your formulas against our numbers

Before you trust a tracker, feed it days you already know the answer to. These are three habits from the example file of our Habit & Goal Tracker, ticked by a made-up person, Jordan, from 1 January to 31 March 2027. We copied their ticks into the sheet described on this page and recalculated it in LibreOffice Calc; the results match the Dashboard of our file:

Habit	Days per week	Done	Days counted	Success	Current streak	Longest streak
Read 20 pages	7	83	90	92%	38	38
Spanish lesson (15 min)	5	65	90	100%	3	6
No-spend day	5	59	90	92%	4	8

Stats through is 31 March 2027, so Days counted is 90. For the Spanish lesson, the target asks for 90 × 5 ÷ 7 = 64.3, rounded to 64 days; 65 were done, so it shows 100%. For the no-spend day, 59 ÷ 64 = 92%. A quick test of your own: in column B (Read 20 pages, target 7), tick 1 to 5 January and 7 to 9 January only, and leave C and D empty. Done is 8, Days counted 9, Success 89% (8 ÷ 9), Current streak 3 and Longest streak 5. On a column with a target of 5 the same ticks show 100%: 8 done against the 6 days the target asks for.

What the numbers say about Jordan's habits (why the journal at 74% is the one to look at, and why a short streak means little for a 3-day-a-week habit) is on our New Year habit tracker page.

Mistakes that break a habit tracker

Counting with COUNTA. COUNTA counts every cell that isn't empty, so an "x" or a space counts as done. COUNTIF(range,"✓") counts only the ✓.
Counting up to today. TODAY() in Days counted makes every rate fall on the days you don't open the file. Count up to your last ✓ instead, and use TODAY() only for the "today isn't over" rule of the current streak.
Measuring every habit against every day. A habit you planned for 3 days a week looks like a failure at 43% against 7. Divide by your own target.
Streaks in one cell. The single-cell streak formulas you find in forums use array formulas, and some use functions that older versions of Excel don't have. They are hard to read and hard to fix. A Run column per habit uses only IF and works everywhere; you can even see each run grow.
Semicolons. Depending on your country settings, Excel and Google Sheets may show these formulas with semicolons instead of commas, like COUNTIF(B$11:B$375;"✓"). It's the same formula.

Every formula on this page uses only IF, AND, COUNTIF, INDEX, MAX, MIN, ROUND, DATE and TODAY, which work the same in Excel, Google Sheets and LibreOffice (Microsoft Support: Use the COUNTIF function in Microsoft Excel; Google Docs Editors Help: COUNTIF).

Or start from ours

The sheet on this page covers three habits for one year. Our Habit & Goal Tracker is built on the same formulas and adds what takes longer to build: up to 20 habits, each with a days-per-week target and an optional start date, one grid for the year with the ✓ drop-down and a Check column that flags anything that isn't a ✓, Weekly and Monthly views, a Dashboard with success rates, current and longest streaks and every habit month by month, and up to 10 yearly goals with 50 milestones. You type the year in one cell. It works in Excel and Google Sheets, in a light and a dark theme, and is coming soon to our Etsy shop.

Not sure what to track yet? See Habit tracker ideas by area, written so you can tick them.

The habits and every number on this page come from the example file of our Habit & Goal Tracker (a made-up person, Jordan) and from a test sheet with the formulas above, recalculated in LibreOffice Calc. This page is about keeping track of habits, not health or medical advice. We used AI tools to help write this article and the calculations; the names and numbers in the example are made up.