How to Build a Habit Tracker in Google Sheets (Checkboxes, Colors and Progress)

A habit tracker works because it makes progress visible. Ticking a box every day is satisfying, and a row of green squares is surprisingly motivating. Google Sheets is a great place to build one: it’s free, it syncs across your phone and computer, and with a few formulas it can calculate your progress for you.

Short answer: to build a habit tracker in Google Sheets, list your habits down column A, put the days of the month across row 1, select the grid and choose Insert > Checkbox. Then add a column with =COUNTIF(B2:AF2,TRUE) to count completed days, divide by the number of days for a percentage, and use Format > Conditional formatting with a custom formula to color checked boxes green.

What you’ll build

• A monthly grid with one row per habit and one column per day
• Checkboxes you can tick on your phone or computer
• Automatic green highlighting for completed days
• A count and percentage for each habit
• An optional mini progress bar using SPARKLINE

Step 1: Set up the grid

1. Create a new spreadsheet at sheets.google.com and name it, for example “Habit tracker”.
2. In A1, type “Habit”.
3. In B1, type 1, in C1 type 2, and continue to 31 (AF1). A quick way: type 1 and 2, select both cells, and drag the fill handle to the right.
4. In A2 downwards, list your habits: “Drink 2 litres of water”, “Read 20 minutes”, “Walk 8,000 steps”, “No phone after 10pm” and so on.

Keep the list short. Five to seven habits is plenty; trackers with twenty rows tend to be abandoned.

If you’d prefer real dates across the top, type the first date of the month in B1, then in C1 use =B1+1 and fill to the right. Format the row with Format > Number > Custom date and time to show just the day number.

Step 2: Add checkboxes

1. Select the grid of cells under the day columns, for example B2:AF8.
2. Go to Insert > Checkbox.

Each cell now shows a checkbox. A ticked box has the value TRUE and an empty one FALSE, which is what makes the formulas below work.

Step 3: Count completed days

Add three columns to the right of the grid:

Column	Header	Formula in row 2
AG	Done	=COUNTIF(B2:AF2,TRUE)
AH	Goal	Type a number, e.g. 20
AI	Progress	=AG2/AH2

Format the Progress column as a percentage with Format > Number > Percent. Fill the formulas down for every habit.

If you’d rather measure against the number of days in the month, use =AG2/COLUMNS(B2:AF2) instead of a separate goal.

Step 4: Color completed days automatically

1. Select the checkbox grid (B2:AF8).
2. Go to Format > Conditional formatting.
3. Under “Format cells if”, choose Custom formula is.
4. Enter: =B2=TRUE
5. Pick a green fill (and a matching text color, which hides the tick if you’d like solid blocks).
6. Click Done.

Because the formula uses a relative reference (B2 without dollar signs), Sheets applies it to each cell in the range individually.

Highlight habits that are falling behind

Add a second rule on the Progress column:

1. Select AI2:AI8.
2. Format > Conditional formatting > Custom formula is: =AI2<0.5
3. Choose a soft red or orange fill.

Now any habit below 50% stands out. You can also use the Color scale tab for a gradient from red to green.

Step 5: Add a progress bar (optional)

In AJ2, add:

=SPARKLINE(AI2,{“charttype”,”bar”;”max”,1;”color1″,”#3E8E6E”})

This draws a small bar that fills up as your percentage rises. Fill it down for each habit. SPARKLINE is a Google Sheets function, so this step won’t carry over directly if you open the file in Excel.

Step 6: Make it easy to use on your phone

• Freeze the habit column: select column A, then View > Freeze > 1 column, so habit names stay visible as you scroll.
• Freeze the header row: View > Freeze > 1 row.
• Narrow the day columns: select B to AF, right-click and resize to about 30 pixels.
• Install the Google Sheets app and bookmark the file; ticking a box takes seconds.

Step 7: Start a new month

Right-click the sheet tab at the bottom and choose Duplicate. Rename it for the new month, then select the checkbox grid and press Delete to clear the ticks (the checkboxes stay). Keeping each month in its own tab lets you compare months later.

Ideas for a summary tab

Once you have a few months, add a “Summary” tab that pulls each month’s percentages. For example, if your March tab is named “Mar”, this pulls its first habit’s progress:

=’Mar’!AI2

A line chart of those percentages shows whether a habit is becoming automatic or slipping.

Make the tracker work for you

• Track behaviours, not outcomes: “Walk 20 minutes” is trackable daily; “lose weight” isn’t.
• Set realistic goals: five days a week often works better than seven.
• Review it weekly: a weekly review routine is the perfect moment to check your tracker and adjust.
• Pair it with bigger goals: habits are the weekly actions behind goals, as explained in our guide to 12-week quarterly planning.

Prefer to start from a finished layout? Browse our Excel and Google Sheets templates. If you prefer handwriting, many digital planners include habit tracker pages too.

FAQ

How do I add checkboxes in Google Sheets?

Select the cells, then go to Insert > Checkbox. Ticked boxes return TRUE and empty ones return FALSE, so you can count them with COUNTIF.

Can I use this habit tracker in Excel?

Most of it, yes. COUNTIF and conditional formatting work in Excel, but checkboxes and SPARKLINE behave differently. In Excel you can type an “x” or 1 in each cell instead and count with =COUNTIF(B2:AF2,”x”) or =SUM(B2:AF2).

How many habits should I track at once?

Start with three to five. It’s easier to build consistency with a few habits, and you can add more once the first ones feel automatic.