Build a Habit Tracker in Excel With Checkmarks and Streaks

A habit tracker in Excel is a simple grid: one row per day, one column per habit, and a checkmark each time you do it. A few formulas turn it into something more useful, with totals, a completion rate and your current streak. This guide builds one step by step, and the same COUNTIF and conditional formatting skills come up in real work trackers and Excel tests.

How the habit tracker is laid out#

Put each day on its own row and each habit in its own column. This shape keeps every formula short, because each habit is one column you can count down. Here is the first week:

For a real tracker, use dates instead of day names. Type the first date in A2, such as =DATE(2026,11,2), then in A3 type =A2+1 and fill down (Windows: Ctrl+D; Mac: ⌘D). Format the column with the custom format ddd d mmm to show "Mon 2 Nov". If you've built the attendance tracker, this is the same idea turned on its side.

Step 1: Add checkboxes or checkmarks#

In Excel for Microsoft 365 and Excel for the web, select B2:D8 and choose Insert > Checkbox. Each cell gets a checkbox that stores TRUE when ticked and FALSE when not. Press the Spacebar to tick the selected box, which is quick when you fill in a whole day from the keyboard.

In older versions, you can use Developer > Insert > Check Box (Form Control), but each box has to be linked to a cell by hand. It's simpler to type an x for done and leave the cell empty otherwise. The guide to checkboxes in Excel covers both methods. Every formula below works with either; just swap TRUE for "x".

Step 2: Count completed days with COUNTIF#

COUNTIF counts the cells that match a condition, so it counts the ticked boxes in a column:

=COUNTIF(B2:B8,TRUE)

For the Exercise column this returns 5. Type TRUE without quotes, because a checkbox holds the logical value TRUE, not the word. If you use an x, write =COUNTIF(B2:B8,"x").

To count how many habits you did each day, count across the row instead. In E2:

=COUNTIF(B2:D2,TRUE)

Fill it down to get 3, 2, 2, 2, 3, 2, 3. The COUNTIF function guide and the full COUNTIF article cover the criteria in more depth, and how to count yes and no answers shows the same trick for Yes/No columns.

Step 3: Show a completion rate#

A completion rate is the share of days you did a habit. Divide the days done by the number of days in the tracker:

=COUNTIF(B2:B8,TRUE)/COUNTA($A$2:$A$8)

For Exercise that is 5/7, or 71% once you format the cell as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). The $ signs keep the day count fixed when you copy the formula across to Read and Water, which both show 86% (6 of 7).

For a month-long tracker, count only the days so far, so future days don't drag the rate down: =COUNTIF(B2:B32,TRUE)/COUNTIF($A$2:$A$32,"<="&TODAY()).

Step 4: Track your streak with a helper column#

A streak is the number of days in a row you did a habit. The easy way is a helper column that keeps a running count. For Exercise, in F2:

=IF(B2,1,0)

Then in F3, and filled down to F8:

=IF(B3,F2+1,0)

Each row asks one question: was the box ticked today? If yes, add 1 to yesterday's count. If no, reset to 0. For the Exercise column, the helper shows 1, 0, 1, 2, 3, 0, 1. Then:

• Longest streak: =MAX(F2:F8) returns 3 (Wednesday to Friday).
• Current streak: the last value, 1.

Because B3 already holds TRUE or FALSE, IF can use it directly as the test. With x marks, write =IF(B3="x",F2+1,0). The TRUE, FALSE and comparisons lesson explains why a TRUE/FALSE cell works as an IF test on its own.

In Excel 2021 or Microsoft 365, you can get the current streak without a helper: =ROWS(B2:B8)-IFERROR(XMATCH(FALSE,B2:B8,0,-1),0). XMATCH with -1 searches from the bottom up for the last missed day, and everything after it is the streak. Use it on the days so far only, because an unticked future day counts as a miss.

Step 5: Color completed habits with conditional formatting#

Conditional formatting makes the tracker easy to read at a glance. To color every ticked cell green:

1. Select B2:D8.
2. Choose Home > Conditional Formatting > New Rule.
3. Pick Use a formula to determine which cells to format and type =B2=TRUE.
4. Click Format, choose a light green fill, and click OK twice.

To highlight the whole row on a perfect day, select A2:E8 and use =$E2=3. The $ before E keeps every cell in the row looking at the Done today column. The conditional formatting guide has more rule ideas, such as data bars on the completion rates.

Step 6: Count perfect days and extend to a month#

To count the days you did every habit, use COUNTIFS with one condition per column:

=COUNTIFS(B2:B8,TRUE,C2:C8,TRUE,D2:D8,TRUE)

This returns 3: Monday, Friday and Sunday. To extend the tracker to a month, fill the dates down to row 32 and change the ranges from row 8 to row 32. Better still, convert the grid to a table with Ctrl+T (Mac: ⌘T). Tables grow when you add a row, so formulas that point at the table's columns pick up new days by themselves.

Practise it#

Every part of this tracker uses skills employers test: counting with conditions, TRUE/FALSE logic and formula-based formatting. Practise COUNTIF in a live spreadsheet, then practise TRUE, FALSE and comparisons and practise conditional formatting with formula rules and the $ sign. Each lesson checks your work instantly. Or start right here with the exercise below.