An Excel habit tracker is a grid with your habits down the left side and the days of the month across the top. You mark each day you complete a habit, and formulas count how often you did it, how close you are to your goal and how long your current streak is.
This guide shows the layout that works best, the exact formulas (each one tested), and how to adapt the tracker for weekly use, printing or Google Sheets. You can follow it to build your own from a blank workbook or to customize a template you already have.
What a good habit tracker template includes
The best trackers keep one month on one tab, so you can see the whole month without scrolling far. Each month tab has four parts:
| Part | Cells | What it does |
|---|---|---|
| Date row | C2:AG3 | Shows each day of the month and its weekday, and adjusts for months with 28, 29, 30 or 31 days |
| Habit list | A4:B8 | One habit per row, with a goal for the month |
| Tracking grid | C4:AG8 | Where you mark each completed day |
| Summary columns | AH4:AL8 | Done count, goal %, pace, best streak and current streak |
A hidden helper block below the grid calculates streaks, and an optional Year tab collects each month’s results so you can compare them.
Skip the setup and download the Excel habit tracker template with the date row, streak counter and formatting already set up. Type in your habits and start marking today.
Here’s what the summary columns hold:
| Column | Heading | Example | Meaning |
|---|---|---|---|
| AH | Done | 22 | Days you marked this habit |
| AI | Goal % | 88% | Done divided by your goal |
| AJ | Pace | 92% | Done divided by the days that have passed so far |
| AK | Best streak | 9 | Longest run of consecutive days this month |
| AL | Current streak | 4 | Consecutive days up to today |
Monthly, weekly or daily: which layout to use
| Layout | Works best for | Trade-off |
|---|---|---|
| Monthly | Most people, and anyone who wants to see streaks and slumps | Wide grid that needs landscape printing |
| Weekly | Habits you only do a few times a week, such as the gym three times | You set up a new grid every week |
| Daily | Routines with many small steps, like a morning checklist | Hard to spot patterns across weeks |
Start with the monthly layout. It’s the only one that shows a full month of streaks, misses and weekend dips on one screen. You can still track weekly goals inside it with the formula in the improvements section below.
How to build a monthly habit tracker in Excel
The steps below use November 2026 as the example month and five habit rows (rows 4 to 8). Change the dates and rows to suit you.
Step 1: Set up the date row
In C3, enter the first day of the month: =DATE(2026,11,1). In D3, enter this formula and copy it right to AG3:
=IF(C3="","",IF(MONTH(C3+1)=MONTH($C$3),C3+1,""))Each cell adds one day to the cell on its left. Once the next day would fall in the following month, the formula returns a blank instead. November fills 30 columns and leaves AG3 empty; February 2027 fills 28.
Select C3:AG3, press Ctrl+1, choose Custom and type d as the format so only the day number shows. In C2, add the weekday and copy it right to AG2:
=IF(C3="","",TEXT(C3,"ddd"))November 1, 2026 is a Sunday, so C2 shows Sun.
Step 2: List your habits and goals
Type one habit per row in A4:A8 and the number of days you’re aiming for in B4:B8. Write each habit as an action you can answer yes or no to: “Walk 8,000 steps” works, while “Exercise more” doesn’t, because you can’t tell at the end of the day whether you did it.
Set goals below 100% for habits that don’t need to happen daily. A goal of 20 walking days in a 30-day month leaves room for rest days without turning the row red.
Step 3: Mark completed days
You have two options.
The first is typing an x. To stop stray entries, select C4:AG8, go to Data > Data Validation, choose List and type x as the source. Each cell then gets a small drop-down and rejects anything else.
The second is checkboxes. In Excel for Microsoft 365, select C4:AG8 and choose Insert > Checkbox. A ticked box stores TRUE and an empty one stores FALSE. Excel versions without this feature show the words TRUE and FALSE instead of boxes.
If you’ll ever open the file in Google Sheets or an older version of Excel, use the x method. It’s plain text, so it works everywhere.
Step 4: Count completions and goal progress
In AH4, count the marked days:
=COUNTIF(C4:AG4,"x")If you used checkboxes, count TRUE values instead: =COUNTIF(C4:AG4,TRUE).
COUNTIF isn’t case-sensitive, so x and X both count. A tick symbol, or an x with a space after it, won’t count, which is one more reason to use data validation.
In AI4, divide by the goal and format the cell as a percentage:
=IFERROR(AH4/B4,0)IFERROR returns 0 if a goal cell is empty instead of showing #DIV/0!.
Step 5: Add a pace column
Goal % only makes sense at the end of the month. On the 9th, 8 completions looks like 32% of a 25-day goal, even though you’re doing well. A pace column fixes that by comparing completions with the days that have actually passed.
In AJ2, calculate the days so far:
=MAX(0,MIN(TODAY(),EOMONTH($C$3,0))-$C$3+1)EOMONTH finds the last day of the month. During the month this returns today’s day number. For a past month it returns the full month length, and for a future month it returns 0.
Then in AJ4:
=IFERROR(AH4/$AJ$2,0)On November 9, a habit marked 8 times shows 89%.
Step 6: Count streaks with a helper block
Excel has no built-in streak function, so the tracker uses a running count in helper rows. Put them a few rows below the grid, in rows 14 to 18, so row 14 matches habit row 4.
In C14:
=IF(C4="x",1,0)In D14, then copy it right to AG14 and down to row 18:
=IF(D4="x",C14+1,0)Each cell adds 1 to the cell on its left when the day is marked and resets to 0 when it isn’t. With checkboxes, replace D4="x" with D4=TRUE.
Now fill in the two streak columns. Best streak in AK4:
=MAX(C14:AG14)Current streak in AL4:
=IFERROR(INDEX(C14:AG14,MATCH(TODAY(),$C$3:$AG$3,0)),0)MATCH finds today’s column and INDEX returns the running count there. The current streak shows 0 until you mark today, so if you update the tracker each evening, that’s expected. To show the streak through yesterday instead, change TODAY() to TODAY()-1.
When the formulas work, select rows 14 to 18 and choose Data > Group to collapse them out of sight.
Step 7: Make it easy to read with conditional formatting
Select C4:AG8, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and add these rules:
| Rule | Formula | Suggested format |
|---|---|---|
| Completed day | =C4="x" (or =C4=TRUE) | Green fill |
| Weekend | =WEEKDAY(C$3,2)>5 | Light grey fill |
| Today | =C$3=TODAY() | Bold border |
The dollar sign before 3 keeps every rule looking at the date row. Blank columns at the end of shorter months are skipped automatically, because the rules can’t read a date there.
For the Goal % column, Home > Conditional Formatting > Color Scales gives a quick red-to-green view of which habits need attention.
Weekly and daily habit tracker versions
A weekly habit tracker uses the same layout with seven day columns, C to I. Type the week’s Monday in C3, put =C3+1 in D3 and copy it to I3. To find this week’s Monday, =TODAY()-WEEKDAY(TODAY(),3) returns it, but copy the result and paste it as a value. Left as a formula, the dates would move every week and your marks would end up under the wrong days.
A daily habit tracker is simpler still: a checklist with the habit or routine step in column A and an x or checkbox in column B. One formula shows how much of today’s list is done:
=IFERROR(COUNTIF(B4:B12,"x")/COUNTA(A4:A12),0)COUNTA counts how many steps are listed, so the percentage stays right when you add or remove a step. A daily checklist doesn’t keep history, though, so pair it with the monthly grid if you want to see trends.
Example: one month with four habits
Here’s what a finished November looks like for someone tracking four habits:
| Habit | Goal (days) | Done | Goal % | Best streak |
|---|---|---|---|---|
| Walk 8,000 steps | 20 | 17 | 85% | 6 |
| Read 20 pages | 25 | 22 | 88% | 9 |
| Phone away by 11 pm | 30 | 19 | 63% | 4 |
| Drink 8 glasses of water | 30 | 27 | 90% | 14 |
The phone habit is the obvious weak spot. With weekend shading on, a run of empty cells on Fridays and Saturdays would point to the cause. One fix is a separate weekend goal; another is making the habit easier, like charging the phone outside the bedroom.
The goal % column also shows when a goal is too easy. If a habit sits at 100% for two months, raise the goal or replace the habit with a new one.
Using the habit tracker in Google Sheets
All the formulas in this guide (DATE, MONTH, TEXT, COUNTIF, IFERROR, EOMONTH, INDEX, MATCH, WEEKDAY and TODAY) also exist in Google Sheets. Upload the .xlsx file to Google Drive and open it with Google Sheets, or rebuild it there with the same steps.
A few things work differently:
- Checkboxes are under Insert > Checkbox. A ticked box stores TRUE, so use
=COUNTIF(C4:AG4,TRUE). - Conditional formatting is under Format > Conditional formatting. Choose “Custom formula is” and enter the same formulas.
- Grouping rows is under View > Group, or right-click the selected rows.
If you plan to switch between Excel and Google Sheets, the x method avoids any differences in how the two apps handle checkboxes.
Printing a habit tracker for a desk or bullet journal
A monthly tracker is wide, so set up the page before you print:
- Choose Page Layout > Orientation > Landscape.
- Select A1:AG8 and choose Page Layout > Print Area > Set Print Area. This leaves the summary columns off the page, which suits a tracker you’ll fill in by hand.
- In the Scale to Fit group, set Width to 1 page.
- Check File > Print to preview before printing.
For a bullet journal, print at a smaller scale and trim the grid to fit the page. Circle habit trackers are popular in bullet journals but are hard to build from Excel’s square grid. If you like the circular look, a doughnut chart of each habit’s done and remaining days (Insert > Insert Pie or Doughnut Chart > Doughnut) gives a similar view on screen.
Common mistakes to avoid
- Tracking too many habits at once. Start with three to five. A grid of twelve habits mostly fills with blanks and becomes discouraging.
- Mixing marks. An x, a tick and an x with a trailing space look alike but count differently. Data validation solves this.
- Typing 31 days into every month. Use the date formula from Step 1 so short months end on the right day.
- Writing habits you can’t answer yes or no to, such as “eat healthier”.
- Typing over formulas by accident. Select the grid and habit cells, press Ctrl+1, open the Protection tab and clear Locked. Then choose Review > Protect Sheet, and only those cells stay editable.
- Building a new file every month. Right-click the month tab, choose Move or Copy, tick Create a copy, then change the date in C3 and clear the grid.
Ways to improve the tracker
Year summary: Add a Year tab with one row per habit and one column per month. Each cell links to that month’s Goal % cell, for example ='Nov 2026'!AI4. Sheet names with spaces need the single quotes.
Weekly goals inside a monthly grid: For habits like “gym 3 times a week”, put the week’s start date in a cell such as AN2 and count that week only:
=COUNTIFS($C$3:$AG$3,">="&$AN$2,$C$3:$AG$3,"<="&$AN$2+6,C4:AG4,"x")COUNTIFS checks three conditions at once: the date is on or after the start, it’s within the next six days, and the cell is marked.
Sparklines: Select a cell next to each habit, choose Insert > Sparklines > Column and point it at that habit’s helper row. You get a small bar chart that grows with each streak.
Notes: Add a notes column after AL for anything that explains a bad week, like travel or illness. A month later, those notes are often more useful than the numbers.
Frequently asked questions
How do I make a habit tracker in Excel?
Put your habits in column A, use a formula to fill the dates across row 3, and mark each completed day with an x or a checkbox. Then add COUNTIF to count completions and divide by your goal to get a percentage. The step-by-step section above has every formula.
Can I add checkboxes to an Excel habit tracker?
Yes, in Excel for Microsoft 365. Select the grid and choose Insert > Checkbox. Ticked boxes store TRUE, so count them with =COUNTIF(range,TRUE). In other versions, use an x with a data validation drop-down instead.
How do I count a habit streak in Excel?
Use a helper row where each cell adds 1 to the previous cell when the day is marked and resets to 0 when it isn’t. The highest number in that row is your best streak, and the number in today’s column is your current streak.
Is there a habit tracker template for Google Sheets?
An Excel habit tracker built with the steps above opens in Google Sheets with its formulas working. Checkboxes and conditional formatting sit in different menus, which the Google Sheets section covers.
Should I use a daily, weekly or monthly habit tracker?
Use a monthly tracker for most habits, because a full month shows patterns a single week can’t. A weekly tracker suits habits with a weekly target, and a daily checklist suits routines with many small steps.
How many habits should I track?
Three to five is a good start. Add more once the first few stay above your goal for a full month.
Getting started
Set up next month’s tab now with three habits and realistic goals, then mark it at the same time each day. After the first week, check the pace column: anything under your goal % is the habit to make easier, not the one to drop.
The template has twelve monthly tabs and a year summary, so you only set it up once.