While I have tried countless habit trackers and beautifully designed journal apps, they often end up gathering dust after a few weeks. Frustrated with the lack of a truly adaptable and personalized system, I took matters into my own hands. Turns out, the solution was right under my nose: good old Microsoft Excel. It began as a simple experiment and quickly turned into a powerful, effective habit tracker that finally helped me achieve consistent progress.

If you are tired of complicated apps and growing subscriptions, stick around because I am about to show you how to build a habit tracker with your familiar spreadsheet program.

Build a habit tracker from scratch

You might be thinking, "Why bother building a habit tracker from scratch when there are hundreds of free Excel templates available online?" And you would be right. But hear me out. While templates offer convenience, they often lack the personal touch that is required in a habit tracker.

By building a habit tracker from scratch, you can learn about essential Excel hacks and, more importantly, customize the features, workflow, and overall tracker precisely to your needs.

Go over the basics

First, let’s create the basic structure of our habit tracker in Excel.

1. Launch Excel and create a new blank spreadsheet.
2. Increase the lengths of the first two rows (we will insert a chart here later).
3. Select cells in the fourth row and head to Home -> Alignment -> Merge & Center.
4. Give your habit tracker a unique name, increase the font size, and align it in the center.
5. Create columns for habit numbers, habits, and insert dates.
6. You can write 1, 2, and drag and row to insert numbers up to 30. Place the cursor in the bottom corner of a cell, wait for the + icon to appear, and drag it horizontally.
7. You should also center text from the top menu.
8. Increase the width of the Habits column. We will add checkboxes under the columns. Select these columns (1 to 30) and resize them to fit the checkbox. Add numbers below the first column.
9. Select all cells in the monthly range. Head to Insert and add checkboxes.
10. Select all cells in your habit tracker, open the borders menu, and insert Thick outside border. Now, there is a clear differentiation between your habit tracker and other data on your sheet.

The basic structure of your habit tracker is ready.

Use a formula and add habits

It’s time to add other details to complete your habit tracker.

1. Use the cell below to add a formula that counts the number of habits (=COUNTA(C6:C15)).
2. Now, when you enter habits above, the number should show below.
3. Merge the three cells below (from Home -> Merge -> Merge Across) menu and label them as Completed, Incompleted, and Percent (%).
4. Let’s use the formula to count the number of completed tasks on day one. Use the formula below.

=COUNTIF(D6, D15, true)

To count the uncompleted habits, we will subtract the total number of habits from completed ones.

=$C16-D17

​​​​​​​

Copy the same formula to the rest of the cells in the row. Select the current cell and drag it to the end of the row. Now, to calculate the percentage, I will divide the completed habits by the total number of habits.

=IFERROR(D17/$C$16, 0)

​​​​​​​​​​​​​​

Here, I have used the IFERROR formula so that your habit tracker doesn’t show infinite (####) when it tries to divide 0 with the total number of habits. Select the % symbol at the top and you are good to go. When I complete 8 out of 10 habits for a day, it shows 80% in the percent row.

Create a progress bar

If you prefer to visually track your habits, add a progress bar to your sheet. Here’s how.

1. Add three columns to your existing sheet — Tick, Percent, and Progress.
2. Use the following formula under the Tick column.

=COUNTIF(D6:AG6, TRUE)

​​​​​​​

The D6 to AG6 range represents the column for a specific habit.

Now, divide the total number of ticks by the days of the month. It will be =AH6/30.

Under the progress bar, use the following formula.

=REPT("█", AI6*30)

​​​​​​​​​​​​​​

​​​​​​​

AI6 represents the percentage value, and when you divide it by 30, it shows live progress. Increase its width so that you can see the progress properly. Now, extend the same formula for the entire column.

Add a chart

It’s time to add a line chart at the top to track your habit progress. Follow the steps below.

1. Select the Percent row at the bottom.
2. Head to Insert -> Recommended chart and pick a relevant one from the suggestion.
3. You can give it a unique name, customize the chart with dozens of options, and place it at the top.

Excel offers tons of options to customize the chart's look and feel.

Decorate your spreadsheet

Your habit tracker is ready to use. You can now remove gridlines, use different colors, and explore other tricks to make it look stunning. You can head to View -> Show and disable the Gridlines menu. When you use different colors, don’t go overboard with it.

Optional: Use conditional formatting

I use conditional formatting to highlight my least disciplined days of the month. I have set a condition where Excel automatically highlights cells where the overall completion percentage is less than 50%. Here’s how you can set the rule yourself.

1. Select the Percent row at the bottom.
2. Head to Home -> Conditional Formatting -> Highlight Cells Rules -> Less Than.
3. Write down 50% in the cell value, select the formatting style, and click OK. ​​​​​​​

Stop failing at habits

Building a sustainable habit tracker doesn't require a fancy application or a complex system. As you can see, Excel’s straightforward functionality can be leveraged into a powerful tool for self-improvement. So whether you are aiming to read more, exercise regularly, or simply stay more organized, take the time to build your own personalized Excel habit tracker. You might just be surprised at how much you can achieve with a little bit of Excel magic. Aside from a habit tracker, here are the top unique ways to use Excel.