Create a Habit Tracker in Google Sheets: Step-by-Step Guide

In this tutorial, I’ll show you exactly how to create a habit tracker in Google Sheets and share a free template you can copy and start using today. One of the key features of this template is that it uses array formulas for automation and simplicity.

With Google Sheets, you can easily build a monthly habit tracker that is both functional and motivating. In this guide, you’ll learn how to:

Format your sheet
Apply the necessary formulas
Set up conditional formatting rules that make the tracker visually appealing

Table of Contents

Why Track Your Habits?

There are many benefits to using a habit tracker:

It helps you stay committed to your goals.
It highlights areas where you can improve.
It keeps you motivated with visible progress.

Habit Tracker Template Layout in Google Sheets

How to Create a Habit Tracker That You’ll Actually Use in Google Sheets

Creating a habit tracker in Google Sheets is straightforward, but to make it engaging enough to use regularly, we’ll add a few fun and functional features:

Smiley icon for goal completion – See a smiley when you hit your targets.
Score highlighting – Instantly spot when you reach your goals.
Drop-down month selector – Select the current month, and the tracker automatically adjusts.
Today’s column highlighting – Quickly find the current day.
Tick boxes for habits – Mark tasks as complete in one click.
Highlight rules – Hide unused checkboxes for a clean look.

We won’t use sparklines for progress bars because they can make the tracker feel too formal.

Let’s get started.

Step 1: Basic Setup – Naming the File, Creating a Drop-Down, and Adding Habits

Open a new spreadsheet at https://sheets.new.
Rename it: Go to File > Rename (Windows shortcut: Alt + F + R, Mac shortcut: Ctrl + Option + F + R) and name it habit tracker.
Create a month selector drop-down in cell B1:
- Select cell B1 → Insert > Drop-down.
- Replace “Option 1” with “January” and “Option 2” with “February.”
- Add all month names. Optionally, set colors for each month.
- Click Done.

Habit entry area:

My template tracks up to 15 habits per day.
Title cell B2 as Habits.
Enter your habits in B3:B17. Example:

Wake up at 7 am
Treadmill for 45 minutes
Drink 15 ounces of water
Read 20 pages
Swim in the pool
Do yoga
Sleep at 10 pm

Step 2: Add Checkboxes to Track Your Progress

First, make sure your sheet has enough columns — you’ll need columns for 31 days plus 2 additional columns at the end for formulas and progress calculations.

Then, select the range C3:AG17 (covering all possible days) and go to Insert > Tick box.

These checkboxes will later be masked in unused rows and columns using conditional formatting rules, keeping the tracker clean and relevant to your selected month and available habits.

Step 3: Formulas for Your Habit Tracker

We’ll use 8 array formulas in total to make the habit tracker fully automated.

1. Sequence Numbers (A3) – Auto Number Your Habits

=SEQUENCE(COUNTA(B3:B17))

Generates numbers for each habit entered in column B.

2. Sequence Dates (C2) – Automatically Generate Month Dates

=SEQUENCE(1, DAY(EOMONTH(DATE(YEAR(TODAY()), MONTH(B1&1), 1), 0)))

This formula automatically adjusts to the number of days in the month selected in cell B1.

Explanation:

DATE(YEAR(TODAY()), MONTH(B1&1), 1) → Returns the first day of the selected month in the current year.
EOMONTH(..., 0) → Returns the last day of that month.
DAY(...) → Extracts the day number from the end date, which equals the total number of days in the month.

3. Days of the Week (Optional, C1) – Show Weekday Headers

=ArrayFormula(LET(
   sdt, DATE(YEAR(TODAY()), MONTH(B1&1), 1), 
   dts, SEQUENCE(1, DAY(EOMONTH(sdt, 0)), sdt), 
   TEXT(dts, "ddd")
))

This formula displays short day names (e.g., Mon, Tue, Wed) above the corresponding date numbers.

Sequence Numbers, Dates, and Weekday Headers (Formula Output)

Explanation:

DATE(YEAR(TODAY()), MONTH(B1&1), 1) → Returns the first day of the selected month in the current year.
LET(sdt, ...) → Assigns that first day to the variable sdt for reuse.
DAY(EOMONTH(sdt, 0)) → Finds the total number of days in the selected month.
SEQUENCE(1, total_days, sdt) → Generates a horizontal sequence of dates starting from sdt for the entire month.
TEXT(dts, "ddd") → Converts each date in the sequence into its 3-letter weekday abbreviation.
ARRAYFORMULA(...) → Applies the formula across the entire sequence automatically.

4. Monthly Progress % per Habit (AH3) – Track Habit Completion

=BYROW(FILTER(FILTER(C3:AG17, B3:B17<>""), C2:AG2>0), LAMBDA(row, COUNTIF(row, TRUE)/COUNTA(row)))

This formula calculates the monthly completion percentage for each habit.

Explanation:

FILTER(C3:AG17, B3:B17<>"") → Removes unused habit rows (where column B is blank).
Outer FILTER(..., C2:AG2>0) → Removes unused day columns (where the date row is empty or zero).
BYROW(..., LAMBDA(row, ...)) → Processes the filtered data one row (habit) at a time.
COUNTIF(row, TRUE) → Counts the number of days the habit was completed (checked boxes).
COUNTA(row) → Counts the total number of days tracked for that habit (checked and unchecked boxes).
COUNTIF / COUNTA → Calculates the completion percentage for that habit.

Note:

After entering this formula, format the result as a percentage:

Select AH3:AH17.
Go to Format > Number > Percent.

5. Daily Progress % for All Habits (C18) – See Daily Completion Rates

=BYCOL(FILTER(FILTER(C3:AG17, B3:B17<>""), C2:AG2>0), LAMBDA(col, COUNTIF(col, TRUE)/COUNTA(col)))

Calculates daily progress across all habits. Follow the same explanation as in Formula 4, except this one works by columns instead of rows.

Note:

Format the result as a percentage:

Select C18:AG18.
Go to Format > Number > Percent.

6. Total Monthly Progress % (AH18) – Your Overall Monthly Score

=LET(ftr, FILTER(FILTER(C3:AG17, B3:B17<>""), C2:AG2>0), COUNTIF(ftr, TRUE)/COUNTA(ftr))

Shows your final score for the month.

Explanation:

This formula uses the same nested FILTER logic as in previous formulas to remove unused rows and columns. The filtered range is stored in the variable ftr using LET.

COUNTIF(ftr, TRUE) counts all completed habits in the month.
COUNTA(ftr) counts all habits tracked in the month (completed or not).
Since it does not use BYROW or BYCOL, the calculation is done on the entire range as a whole rather than grouping by rows or columns.

Note:

Format the result as a percentage:

Select AH18.
Go to Format > Number > Percent.

7. Smiley Icons for Goals (AI3) – Celebrate Habit Achievements

=ARRAYFORMULA(IF(AH3:AH17>=80%, "🥰",))

Adds smileys for habits with ≥80% completion.

8. Smiley Icons for Daily Goals (C19) – Track Daily Wins with Icons

=ARRAYFORMULA(IF(C18:AG18>=80%, "🥰",))

Daily Progress (Vertical) for All Habits, Monthly Progress (Horizontal) for Each Habit, and Smiley Icons

Step 4: Conditional Formatting Rules

We’ll use these rules to hide unused checkboxes, highlight progress, and show today’s column.

Mask unused habit rows — Range: C3:AG17

=$B3=""

Set the text color to match the background color and fill color to none so unused checkboxes appear hidden.

Mask unused days in short months — Range: AE3:AG17

=AE$2=""

Set the text color to match the background color and fill color to none.

Highlight high-score rows — Range: C3:AG17

=AND(C$2<>"", $AH3>=80%)

Use a distinct fill color for this rule.

Highlight high-score columns — Range: C3:AG17

=AND($B3<>"", C$18>75%)

Use a different fill color from the one above.

Highlight today’s column — Range: C3:AG17

=AND(MONTH($B$1&1)=MONTH(TODAY()), C$2=DAY(TODAY()))

Use a third distinct fill color so it’s visually clear from the other two highlights.

Note: If a cell meets multiple conditions, the color from the rule that appears higher in the Conditional formatting rules list will take priority. You can drag rules up or down to adjust the priority.

How to apply any of these conditional formatting rules:

Select the target range.
Go to Format > Conditional formatting.
In the Format rules panel, choose Custom formula is.
Enter the formula shown above for that rule.
Set your preferred fill color or text color.
Click Done.

Step 5: Using the Habit Tracker Template

Once you’ve finished setting it up:

Select the current month in B1.
Enter your own habits in B3:B17.
Clear checkboxes for a fresh start.
Track daily by ticking boxes.
Duplicate the sheet each month to keep a history.

Habit Tracker Template

If you’re looking for more tools to track habits, focus, and consistency, check out:

Explore more Google Sheets templates in our complete collection.

2 COMMENTS

Max Pearson February 21, 2024 At 11:41 pm

Hi, this sheet is awesome! I understand its capabilities for tracking progress monthly, but I wanted to see if there was a way to track progress annually. This would involve compiling all the progress within each monthly tab and essentially creating a dashboard to showcase your annual progress. Is there a way to do this?

- Prashanth KV February 22, 2024 At 9:48 am
  
  You can right-click on the tab name and duplicate it. This way, you can create a habit tracker for each month.
  
  For the dashboard, we may need to use complex functions to combine data and aggregations, which could make the template more intricate to use.