Habit Tracker in Google Sheets: Build One Step by Step
Build a free habit tracker in Google Sheets or Excel: checkboxes, completion rates with COUNTIF, conditional formatting and a monthly review.
A spreadsheet is one of the simplest and most flexible habit trackers: free, private, and easy to customize. With a few formulas, it also shows you completion rates and patterns that a paper tracker can’t.
This guide shows how to build a habit tracker in Google Sheets (the same steps work in Excel), from a basic checkbox grid to completion percentages and a monthly review. It’s part of our habit tracking guides.
Step 1: Set up the grid
- Create a new spreadsheet.
- In A1, type “Habit.” In B1 onward, enter the dates of the month (type the first date, then drag across).
- In A2 downward, list your habits (three to six is plenty).
| B | C | D | … | |
|---|---|---|---|---|
| A1: Habit | 1 Nov | 2 Nov | 3 Nov | |
| A2: Walk 20 min | ||||
| A3: Read 10 pages | ||||
| A4: No phone in bed |
Step 2: Add checkboxes
Select the grid of cells for habits and dates (for example B2:AE4), then:
- Google Sheets: Insert → Checkbox.
- Excel: use a “Y” or “1” for each completed day, or insert checkboxes if your version supports them.
Each checked box is stored as TRUE.
Step 3: Calculate completion rates
Add a column after the last date, titled “Done.” For the first habit (row 2), use:
=COUNTIF(B2:AE2, TRUE)Add another column “Rate” with:
=COUNTIF(B2:AE2, TRUE) / COUNTA(B$1:AE$1)Format it as a percentage. (In Excel with “Y” values, use =COUNTIF(B2:AE2,"Y").)
If you only want to count days up to today, divide by the number of days so far instead:
=COUNTIF(B2:AE2, TRUE) / (TODAY() - B$1 + 1)Step 4: Add conditional formatting
Make progress visible:
- Select the checkbox grid.
- Format → Conditional formatting (Google Sheets) or Home → Conditional Formatting (Excel).
- Rule: “Is equal to” TRUE → green fill.
- On the Rate column, use a colour scale (red to green).
Step 5: Add a weekly view
Weekly totals are often more useful than daily marks. Add a small table:
=COUNTIF(B2:H2, TRUE)for days 1–7, then the next seven days, and so on. A habit done four or five times a week is a strong habit, even with gaps.
Step 6: Review monthly
At the end of each month, look at:
- Completion rate per habit. Above roughly 70%? It’s working. Below 30%? Make it smaller or change the time.
- Weekday patterns. Which days are weakest? Plan for them.
- One change for next month. Duplicate the sheet for the new month and adjust.
Focus on rates, not streaks
A spreadsheet makes it easy to see “22 out of 30 days” instead of a broken streak. That’s usually a healthier and more accurate view of progress. See the psychology of streaks and habit trackers without streaks.
Spreadsheet, paper or app?
- Spreadsheet: free, flexible, good for stats; no reminders.
- Paper: visible and satisfying; see bullet journal habit tracker layouts.
- Apps: reminders and convenience; see the ultimate guide to habit tracker apps.
A Notion version is covered in our Notion habit tracker template.
Disclosure: Cohorty publishes this journal. A tracker records the habit; a set time gets it done. In Cohorty you book a 30-minute session and do the habit alongside others, with no camera or chat, then tick it off. See how to use Cohorty.
Common questions
How do I make a habit tracker in Google Sheets?
List habits in rows and dates in columns, insert checkboxes, then use COUNTIF to count completed days and conditional formatting to colour them.
What formula counts checkboxes in Google Sheets?
=COUNTIF(range, TRUE) counts checked boxes in a range.
Can I make a habit tracker in Excel?
Yes. Use the same layout with “Y” or 1 for completed days, COUNTIF for totals and conditional formatting for colours.
Is a spreadsheet better than a habit tracking app?
It’s more flexible and private, and free. Apps offer reminders and convenience. Choose what you’ll actually use.
