Google Sheets and Excel habit tracker

Tracking · 4 min read · Updated

To build a Google Sheets habit tracker, put dates down column A and one habit per column, tick checkboxes or type numbers, count completed days with COUNTIF and color the cells with conditional formatting. The same layout and formulas work as an Excel habit tracker. Spreadsheets suit custom analysis; an app is quicker for daily logging on a phone.

Step 1: set up the columns

Use one row per day and one column per habit. It's tempting to draw a month as a calendar, but a row per day makes formulas, sorting and charts far simpler, and a whole year is only 365 rows.

  • A1: Date. Type the first two dates in A2 and A3, select both and drag down to fill the rest.
  • B1: Floss. A yes or no habit.
  • C1: Read (pages). A counted habit; enter the number of pages.
  • D1: Water (glasses). Enter the day's total.

Step 2: add checkboxes or numbers

For yes or no columns, Google Sheets has built-in checkboxes: select the cells, then Insert, then Checkbox. A ticked box stores TRUE and an empty one FALSE. Recent versions of Excel for Microsoft 365 offer in-cell checkboxes too; if yours doesn't, type 1 for done and leave the cell blank otherwise.

Counted columns just take numbers. Enter 0 on days you did none, rather than leaving them blank, so averages stay honest.

Step 3: count completion with COUNTIF

With a month in rows 2 to 31, these formulas work in both Google Sheets and Excel:

  • Days flossed (checkboxes): =COUNTIF(B2:B31,TRUE)
  • Days flossed (1s): =COUNTIF(B2:B31,1)
  • Completion rate: divide either by 30 and format the cell as a percentage.
  • Days you reached 10 pages: =COUNTIF(C2:C31,">=10")
  • Total pages this month: =SUM(C2:C31)
  • Average glasses per day: =AVERAGE(D2:D31)

Read the numbers carefully

AVERAGE skips empty cells, so blank days quietly disappear from it. Ten logged days averaging 8 glasses looks great, even if the other 20 days were forgotten or skipped. That's why Step 2 says to enter 0 on days you did none.

For habits with rest days, divide by the number of scheduled days, not 30. A Monday, Wednesday, Friday habit has 12 or 13 scheduled days in most months, so 11 workouts is a strong month, not a 37 percent one.

Step 4: color it with conditional formatting

A color scale turns a column of numbers into a heatmap. In Google Sheets, select the range and choose Format, then Conditional formatting, then Color scale. In Excel it's Home, then Conditional Formatting, then Color Scales. Set the lightest color for 0 and the strongest at your target.

For yes or no columns, a single rule is enough: fill cells that equal TRUE, or 1, in your habit's color. Freeze the top row and the date column so headers stay in view as the year grows.

Optional: a streak column

Streaks need a helper column. For the floss habit in column B, put =IF(B2=1,1,0) in E2, then =IF(B3=1,E2+1,0) in E3 and fill down. Each row shows the current run of consecutive days, and =MAX(E2:E366) gives the longest. If you use checkboxes, compare with TRUE instead of 1.

Rest days break this, because a blank Sunday resets the run. If you use 1s rather than checkboxes, type r on off days and change the formula to =IF(B3=1,E2+1,IF(B3="r",E2,0)), so rest days carry the streak forward. This is where spreadsheets start to need care: every scheduled habit needs its own helper column and markers.

Logging from your phone

Phone entry is the weak spot. Finding the right row on a small screen gets old quickly. One workaround in Google's tools is a short Google Form with a date question and one question per habit, linked to your sheet so each submission adds a row. Save the form's link to your home screen.

Form responses arrive in submission order with a timestamp, so you'll want a separate summary tab rather than formatting the responses sheet directly.

When a spreadsheet is the better choice

Pick a spreadsheet if you want analysis no app offers, like =CORREL between hours slept and a mood score, or your own charts. It's also the right call if you enjoy building and adjusting the tool, already keep Sheets or Excel open all day, or want habits next to other records such as weight with notes or expenses.

When an app or chat is less work

An app wins once you have several habits on different schedules, counted habits logged in pieces during the day, or most of your logging happens on a phone. Schedules, partial days and streaks are handled for you, and no formula breaks when you insert a column.

With the habit tracker in ChatGPT, 'Had 2 more glasses' updates the day's total. Target and schedule changes apply from tomorrow, so old rows don't need rewriting. When you want spreadsheet analysis, export everything as CSV from the website and open it in Sheets or Excel.

Track it

Questions

Should I use checkboxes or numbers in a habit spreadsheet?

Numbers are more flexible: a 1 works in every formula in both Sheets and Excel, and counted habits need numbers anyway. Checkboxes look nicer and store TRUE or FALSE, so count them with COUNTIF(range,TRUE).

How do I track a streak in Excel or Google Sheets?

Use a helper column: =IF(B2=1,1,0) in the first row, then =IF(B3=1,E2+1,0) filled down. The MAX of that column is your longest streak. Mark rest days with a letter and carry the previous value forward so they don't reset it.

Can I import my spreadsheet history into the app?

There's no file import, but you can add past days in ChatGPT a month at a time, for example 'I flossed every day from October 1 to 20 except the 6th and 14th'. Days must fall on the habit's schedule.

Read next

All guides

Turn it into a habit you can see

Free for up to 2 habits, every feature included. Log by chatting with ChatGPT or on the website.

Start tracking for free