In this tutorial, I will explain how to create a habit tracker in Google Sheets and share the free template with you. One of the key features of this template is the use of array formulas.

You can use spreadsheets to create a monthly habit tracker very easily. I’ll show you how to format your sheet, the formulas required, and the rules you’ll need.

There are a few benefits to tracking your habits:

• It helps you stick to your goals.
• It can help you identify areas where you want to improve.
• It helps you stay motivated.

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

Creating a habit tracker in Google Sheets is quite easy, but it requires some basic formulas. This may not be enticing enough for you to use it regularly.

To make the habit tracker more addictive, you can add extra features such as:

• Smiley icon for goal completion: See a smiley icon when you reach your goals, which can boost motivation.
• Score highlighting: Highlight your score when you reach your goals to make it easy to see your progress.
• Drop-down month selector: Select the current month from a drop-down menu, which automatically adjusts the total days.
• Today’s column highlighting: Highlight today’s column for easy navigation.
• Tick boxes for completed habits: Quickly fill in tick boxes to mark completed habits.
• Highlight rules: Hide tick boxes in unused rows and columns with highlight rules.

Let’s create a habit tracker in Google Sheets that has all of the above-mentioned features. I have not included sparklines to show the percentage progress bar because they may give the tracker a too-formal feel.

Here are the step-by-step instructions.

Basic Steps: Naming the File, Creating a Drop-Down, and Entering Habits

Open a new Google Sheets spreadsheet at https://sheets.new/.

The default file name will be “Untitled spreadsheet”. Let’s change it by going to the File menu > Rename. You can alternatively use the Google Sheets keyboard shortcut Alt + F + R on Windows or Ctrl + Option + F + R on Mac. Enter “habit tracker” and press Enter.

Now let’s create a data validation drop-down in cell B1 to select months.

To create a drop-down with month names in cell B1, follow these steps:

1. Select cell B1.
2. Click on the Insert menu.
3. Select Drop-down.
4. In the sidebar dialog box that appears next, replace “Option 1” with “January” and “Option 2” with “February”.
5. Click Add another item and enter “March”.
6. Add all month names. You can select your choice of color for each drop-down.
7. Click Done.

Now, when you click on cell B1, you will see a drop-down menu with the month names. You can select the month that you want and it will be entered into the cell.

We can complete the basic settings to create the habit tracker in Google Sheets with one more step:

My habit tracker template is for entering and tracking a maximum of 15 habits per day for one month. The designated area for entering the habits is B3:B17.

Enter the title “Habits” in cell B2 and enter a few habits in cell range B3:B9. Temporarily, you can enter the following seven habits:

• 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

Google Sheets Habit Tracker: Add Checkboxes to Track Your Progress

We will use checkboxes to track the progress of the habits.

To add checkboxes, select the range C3:AG17 and then click Insert > Tick box.

This is an essential step in creating a habit tracker in Google Sheets.

We will later mask the unwanted habits in rows 10 to 17 and in columns AE, AF, and AG. The checkboxes in rows 10 to 17 will be visible if there are habits in B10:B17 in those rows. The checkboxes in columns AE, AF, and AG will be visible depending on the total number of days in the selected month in cell B1.

Formulas for Creating a Habit Tracker in Google Sheets

We have used 8 formulas in our habit tracker template in Google Sheets.

The main feature of this habit tracker in Google Sheets is that all of the formulas used are array formulas. That’s why we could limit them to 8.

Here are those 8 formulas:

1. Formula for Sequence Numbering

Enter the following SEQUENCE formula in cell A3 to get the sequence numbers corresponding to the habits in B3:B17. It will return 1 to 7 since we have 7 habits:

=SEQUENCE(COUNTA(B3:B17))

The COUNTA function returns the number of habits in the range B3:B17, which is 7. The SEQUENCE function returns 7 numbers vertically because we only specified the row argument.

Syntax:

SEQUENCE(rows, [columns], [start], [step])

2. Formula for Sequence Dates

Again, we will use the SEQUENCE function, this time to return the sequence horizontally from 1 to n, where n is determined by the chosen month in cell B1.

Generic Formula:

SEQUENCE(1, n)

How do we find n?

n is the number of days in the month selected in cell B1. To get that, we need to convert the text in cell B1 to the end-of-the-month date and return the day part from it.

Formula (for cell C2):

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

Explanation:

• DATE(YEAR(TODAY()),MONTH(B1&1),1) returns the month start date.
• EOMONTH() returns the end-of-the-month date.
• DAY() returns the day.

3. Formula for Returning Days of the Week (Optional)

When creating a habit tracker, you can optionally show the days of the week corresponding to the populated sequence dates.

Generic formula:

=ARRAYFORMULA(TEXT(SEQUENCE(1,n,month_start_date),"ddd"))

Formula (for C1):

=ARRAYFORMULA(TEXT(SEQUENCE(1,DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(B1&1),1),0)),DATE(YEAR(TODAY()),MONTH(B1&1),1)),"ddd"))

Explanation:

• SEQUENCE(1,n) generates a sequence of numbers from 1 to n, where n is the number of days in the month selected in cell B1.
• DATE(YEAR(TODAY()),MONTH(B1&1),1) returns the start date of the month selected in cell B1.
• TEXT(SEQUENCE(1,n,month_start_date),"ddd") converts the sequence of numbers to the corresponding days of the week in a short format (e.g., “Mon”, “Tue”, “Wed”).
• ARRAYFORMULA() applies the formula to all cells in the range, starting from cell C1.

The following two formulas calculate the percentage of progress of the tasks in the habit tracker, both daily and monthly.

4. Google Sheets Formula for Calculating the Monthly Percentage Progress of Each Habit

We can calculate the monthly percentage progress of each habit using the following formula in cell AH3:

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

This nested FILTER function works as follows:

• The inner FILTER formula, FILTER(C3:AG17,B3:B17<>""), filters out the unused rows at the bottom.
• The outer FILTER formula, FILTER(FILTER(C3:AG17,B3:B17<>""),C2:AG2>0), filters out the unused columns at the end.

The above (nested) FILTER formula is the range in COUNTIF and COUNTA.

• The COUNTIF function counts the TRUE values (ticked boxes) in the first row of the above range.
• The COUNTA function counts both the TRUE and FALSE values (ticked and unticked) in the first row of the above range.

The division operator returns COUNTIF/COUNTA. The BYROW function repeats this calculation in each row by grouping the range by row.

Formatting the results as percentages:

To format the results as percentages, select cells AH3:AH17 and then click on the Format menu > Number > Percent.

5. Google Sheets Formula for Calculating the Daily Percentage Progress of Habits

This is another important formula for creating a habit tracker in Google Sheets.

We can calculate the percentage progress of all habits on all days using the following formula in cell C18:

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

This is a modified version of the previous formula. It uses the BYCOL function instead of the BYROW function to group the range by columns.

The same nested FILTER formula is used as the range here. However, in this case, the COUNTIF function counts the values in columns, instead of rows.

Formatting the results as percentages:

To format the results as percentages, select cells C18:AH18 and then click on the Format menu > Number > Percent.

6. Monthly Habit Progress Percentage (Total %)

Please don’t confuse formulas 4 and 5 with this formula, which calculates the monthly percentage progress of all habits. This formula in cell AH18 returns your final monthly score.

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

This is pretty simple. The same nested formula is used as the range in COUNTIF and COUNT. Since the absence of the LAMBDA functions (BYROW and BYCOL), these functions take the range as a whole. No grouping by rows or columns.

7. How to Add Smileys to Your Google Sheets Habit Tracker Based on Percent Completion

I assume your target percentage is 80% and above. If so, you can use the following formula in cell AI3:

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

And this one in cell C18:

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

Those array formulas will insert smileys based on the percentage progress. Feel free to change the target percentage value in the formulas.

Creating and Highlighting a Habit Tracker in Google Sheets

Highlight rules are not essential for creating a habit tracker in Google Sheets, but they can be used to make the tracker more visually appealing.

In our example, we have only 7 habits to track, but the sheet is set for 15 habits. This means that we will need to mask or unmask checkboxes in rows based on the number of habits.

We can mask checkboxes in unwanted rows using the following rule for the range C3:AG17:

=$B3=""

To apply this rule to the range, follow these steps:

• Select the range C3:AG17.
• Click Format > Conditional formatting.
• In the Conditional format rules sidebar panel, select Custom formula is under Format rules.
• In the Formula field, enter the above formula.
• Under formatting style select White Text color and White Fill color.
• Click Done.

Additionally, the number of days in a month may not be the same. We have checkboxes in 31 columns, but some months have fewer than 31 days. Therefore, we may need to mask or unmask the checkboxes in the last three columns depending on the month that is selected in cell B1.

We can use the following rule for the range AE3:AG17 to mask checkboxes in the last three columns:

=AE$2=""

Please follow the above steps to apply it.

You can also use the following rules for the range C3:AG17:

To highlight the rows containing the smileys:

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

Fill Color: None
Text Color: Light Green

To highlight the columns containing the smileys:

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

Fill Color: None
Text Color: Light Red

To highlight the current day column:

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

Fill Color: Yellow
Text Color: None

How to Use the Habit Tracker Template in Google Sheets: Quick Tips

I assume you have created your habit tracker template using the formulas and custom rules above in Google Sheets. If not, you can preview and copy my template below.

Habit Tracker Template

To use my habit tracker:

1. Select the current month in cell B1 by clicking the drop-down there.
2. Remove the habits in the range B3:B17 and add your own tasks/habits. You can add up to 15 tasks.
3. Uncheck the checked tick boxes. For that select the range C3:AG17 and tap the spacebar twice.
4. Go to the current day column, which will be highlighted in yellow.
5. Click on the tick box or tap the space bar to check the completed habits.
6. Once the month is finished, right-click on the tab name, click “Duplicate”, and start recording your habits progress in that new tab.
7. Repeat steps 1-6 for each month.

Other Useful Free Google Sheets Templates

1. Reservation and Booking Status Calendar Template in Google Sheets.
2. Array Formula to Split Group Expenses in Google Sheets.
3. Calendar View in Google Sheets (Custom Template).