
Habit Tracker Excel: Build One With Checkboxes
Most Excel habit tracker templates are built to look good on day one. They have a month name in a fancy font, 31 columns of tiny boxes and a few colors. Then the month ends, and you're copying the sheet, retyping dates and fixing formulas that pointed at the old tab.
You can skip all of that. A habit tracker in Excel that works for a whole year needs one sheet, one range of checkboxes and four formulas. This guide builds it step by step, then covers the point where a spreadsheet stops being the right tool.
The one design choice that matters: put dates in rows
Paper trackers put habits down the side and days across the top, because that fits a page. In Excel, flip it. Put one row per day and one column per habit.
This works better in a spreadsheet for three reasons:
It never runs out. A year is 365 rows. You don't need a new tab each month.
Formulas stay simple. Every habit is one column, so "how often did I do this" is one formula on one column.
Charts and filters just work. Excel handles long columns of data well. A 31-column grid is much harder to chart or filter.
If you like the monthly grid view, you can still print one from our free printable habit tracker templates. Use the spreadsheet for the data.
Step 1: Set up the columns
Open a blank workbook and add these headers in row 1:
A1: Date
B1 to F1: one habit per column (for example: Walk, Read, Water, No phone in bed, Stretch)
G1: Done today
Keep it to three to five habits. Each extra column makes the daily check-in a bit longer, and that's usually what kills a tracker. If you're not sure which habits deserve a column, our list of habits to track has ideas grouped by goal.
Write down what counts as done for each habit. Right-click the header and add a note, like "Walk: 20 minutes, any pace." If you don't set the bar in advance, you'll set it differently on good and bad days.
Step 2: Fill in the dates
Type your start date in A2, for example 1/1/2027. In A3, type =A2+1, then drag the fill handle down to row 366. That gives you a full year.
Format column A to show the weekday too (Format Cells > Custom > ddd d mmm). Seeing "Sat" next to a blank row explains a lot of gaps.
Step 3: Turn the habit cells into checkboxes
Select B2:F366, then choose Insert > Checkbox. Excel now has built-in cell checkboxes, and according to Microsoft's guide to checkboxes in Excel, a checked box has the value TRUE and an unchecked one is FALSE. You can click a box, or select it and press the Spacebar.
Microsoft lists the feature for Excel for Microsoft 365 (Windows and Mac) and Excel for the web. If your version doesn't have Insert > Checkbox, type a 1 in the cell when you do the habit and leave it blank when you don't. The formulas below have a version for that too.
Step 4: Add the four formulas
Daily score
In G2, count how many habits you completed that day:
=COUNTIF(B2:F2,TRUE)
Fill it down. One trap: =SUM(B2:F2) returns 0 here, because SUM ignores TRUE values in a range. With the 1s method, SUM works fine.
Completion rate per habit
Somewhere above or beside the log (say, row 368, or a separate Summary sheet), add this for each habit:
=COUNTIF(B2:B366,TRUE)/COUNTIF($A$2:$A$366,"<="&TODAY())
Format it as a percentage. The second half counts only the days that have happened so far, so the rate doesn't look terrible in February just because 300 future days are empty.
Streaks
Streaks need a helper column per habit. In H1 type "Walk streak", and in H2:
=IF(B2,N(H1)+1,0)
Fill it down. Each row adds one to the row above when the box is ticked and resets to zero when it isn't. N() turns the text header into 0, so the first row doesn't throw an error. Repeat for each habit in columns I to L, then hide the helper columns if they clutter the view.
Your longest streak is =MAX(H2:H366).
Current streak
To see the streak as of yesterday (today is often not ticked yet):
=XLOOKUP(TODAY()-1,A2:A366,H2:H366)
If your Excel doesn't have XLOOKUP, use =INDEX(H2:H366,MATCH(TODAY()-1,A2:A366,0)).
Step 5: Use conditional formatting for the patterns
Select A2:G366, open Home > Conditional Formatting > New Rule > Use a formula, and add two rules:
=$A2=TODAY()with a light fill, so today's row is easy to find.=WEEKDAY($A2,2)>5with a pale gray fill, to shade weekends.
The weekend shading is the most useful part of the whole sheet. After a few weeks, you'll see if your gaps cluster on Saturdays and Sundays. If they do, the fix is usually a lighter weekend version of the habit, not more willpower.
For a quick trend line, select columns A and G and choose Insert > Line Chart. The daily score over time tells you more than any single streak.
Make it a Table so it maintains itself
Click anywhere in the log and press Ctrl+T (Cmd+T on Mac) to turn it into an Excel Table. Tables add filter buttons to every header, so you can filter one month or every day you skipped a habit. And when you add rows past the end, the formulas in the Table fill down on their own.
Excel vs Google Sheets
Both work. Pick based on where you already live. Excel's desktop app is faster with a year of data and has better charting. Google Sheets is easier to open on any device and share with an accountability partner. If you'd rather use Sheets, follow our Google Sheets habit tracker guide. The layout ideas in this post carry over.
Where a spreadsheet habit tracker falls short
Tracking helps. A large review in Psychological Bulletin, summarized by the American Psychological Association, found that monitoring progress made people more likely to reach their goals, especially when they physically recorded it. But you only get that benefit on days you actually record.
That's the weak point of Excel. It never reminds you. It doesn't show on your home screen. Ticking a small cell on a phone is fiddly, so many people end up catching up three days later from memory, and the data gets less honest.
It also matters because habits take a while. In the University College London study behind the 66-day habit figure, the time to reach automaticity ranged from 18 to 254 days. A spreadsheet you stop opening in week three can't carry you that far.
A good split: use Excel if you love the analysis, and check in every evening at a set time, like right after you plug in your phone. If you keep forgetting, move the daily check-in to your phone and keep Excel for a monthly review.
Tracking on iPhone instead
If the spreadsheet keeps slipping, Kabit is a native iOS habit tracker that does the parts Excel can't do. It has smart reminders, home-screen widgets so the habit is in front of you, and streaks, calendar and history views that update as you go. Progress insights handle the analysis without formulas. It's free to download, so you can run it next to your spreadsheet for a month and see which one you actually keep up with.
Learn more
Keep reading: more guides on building habits that stick.




