How to create a habit tracker in Excel

Step 1: Habits down, dates across

You want to walk, read, keep your phone out of bed, drink water and stretch. Five habits, one row each, in A2:A6. Across row 1, put real dates: type Nov 1 in B1, =B1+1 in C1, and fill right to the end of the month. Format them with the custom format d so each column shows just the day number, and make the columns narrow.

This sample shows the first two weeks (B1:O1) with the as-of date in B8. In your own tracker, put =TODAY() in B8 and fill the dates to the end of the month in column AE.

Real dates matter. They let the formulas below count only the days so far, total by week and shade weekends. A header of typed numbers 1 to 30 can't do any of that.

Step 2: Count the days done, and the % so far

COUNTIF counts the x marks in a row. Walk 20 min has 11. COUNTIF ignores capitals, so an X counts the same as an x. A tick symbol, a y or a 1 does not, so pick one mark and stick with it.

The % needs the right bottom number. Dividing by 30 on the 14th makes every habit look like a failure: 11 of 30 is 37%. Divide by the days so far instead. COUNTIF(B$1:O$1,"<="&$B$8) counts the date columns up to the as-of date, 14 here, so Walk is at 78.6%.

For the whole grid, divide all the marks by habits x days: =COUNTIF(B2:O6,"x")/(ROWS(A2:A6)*COUNTIF(B1:O1,"<="&B8)) gives 53 of 70, or 75.7%.

Step 3: A current streak that waits for today

The usual streak formula finds the last day you missed and counts the days since. The catch: in the morning you haven't ticked today yet, so every streak shows 0 until you do. Walk 20 min has an x on each of the last four days but a blank today, Nov 14.

This version counts through yesterday, then adds 1 only if today already has an x. FILTER finds the missed days before today, MAX picks the latest, and DAYS counts from there to yesterday. If you've never missed, FILTER falls back to the day before the month started, so Drink 2L water shows 14.

Stretch has ticks on Nov 13 and 14, so it shows 2. For your best run of the month, see the longest-streak guide. FILTER needs Excel 365 or 2021.

Step 4: Weekly totals and the best day

COUNTIFS with a date window totals one week. For the week of Mon Nov 2 to Sun Nov 8, Walk 20 min was done 6 times and Stretch 3 times. Put each week's Monday in a header and the formula fills across.

Totals down a column show your strongest days: =COUNTIF(B2:B6,"x") gives 3 habits on Nov 1, and Nov 7 had 4. A day with low totals every week is worth a look: maybe that's the evening you're always out.

Step 5: Checkboxes and color

In Excel for Microsoft 365, you can use real checkboxes instead of x. Select the grid and go to Insert > Checkbox. Each cell then holds TRUE when ticked and FALSE when not, so the count becomes =COUNTIF(B2:AE2,TRUE). Press the spacebar to tick the selected cell.

Color makes the grid satisfying to fill in. Select B2:AE6 and add a rule with Home > Conditional Formatting > New Rule > Use a formula: =B2="x" with a green fill (or =B2=TRUE for checkboxes). Add =WEEKDAY(B$1,2)>5 with a light gray fill to mark weekends.

1. Select B2:AE6.
2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =B2="x", click Format, choose a green fill and click OK.
4. For a quick visual of the month, add a column with =REPT("■",COUNTIF(B2:AE2,"x")) to draw a bar of squares.