I built a habit tracker in a weekend using nothing but an Excel formula

Modern life sure is hectic. It seems like there's always something going on. We can all build bad habits along the way. Watches, trackers, and all the different productivity apps are fine; but I found something that worked even better for me: a few formulas on an Excel spreadsheet.

I made an exception for Excel because of its portability

A free version of Excel is available as a mobile app and in the cloud

Normally, I choose open-source programs for my personal projects and experiments. But I have to use Excel at work often (the office isn't as keen on FOSS as I am), so I know my way around the program. I can wrangle spreadsheets with the best of them.

But I chose Excel for my habit tracker for a few other reasons:

1. I could use a standalone app version on my phone.

2. I could use an older, archived version of the program and achieve the same results.

3. XLS files are compatible with LibreOffice, so I could use my spreadsheet on Windows, Linux, and mobile. I created a new Microsoft account (I didn't have one previously) with a dedicated email address that I never use. Then I used the online version of Microsoft Excel . I doubled up on my phone with the app, so I'd have a portable version of my tracker available.

I spent some time setting up the visuals on my spreadsheet

I won't use a tracker if I don't like looking at it

I knew if I wanted to make this thing work, I'd have to customize it. I started by setting my font type toCourier Newand the size to 12. Then I usedCTRL + 1to open the cell formatting menu and immediately adjust the following:

• I set all text within a cell towrap

• I set cell borders toall borders

• I set the background color for the top row to light green

• All other cells were changed to a medium blue

• All upper row text was changed tobold

• Habit text was changed to white andbold

After completing my setup, I was ready to define the habits I wanted to track.

I had specific habits I wanted to track

Using a spreadsheet will give me insight into my progress

If it weren't for the fact that it's a Microsoft product, I'd use Excel more often. I spent a great deal of my early career making spreadsheets and I can usually do them quickly and with my eyes closed. It's like second nature to me.

Habit-wise, I figured tracking sleep and eating breakfast every day were two of the more vital ones. I have a tendency to get less than 7 hours of sleep per night and to occasionally skip breakfast. Other habits, like reading a chapter of a book or one issue of a comic, playing guitar for at least 30 minutes, and exercising for 20 minutes are personal goals.

I'm also teaching myself how to code, so I've added that as a habit to try as well. Once I loaded everything into my

Spreadsheet, I was ready to move on to the next step, which was making it work as intended.

There was one magic formula that made a world of difference

Making a visual tracker is remarkably simple

I wanted to build my spreadsheet with a progress bar feature and use specific formulas to make the tracker work.

Excel has checkboxes that make tracking efficient and easy. You can insert them by highlighting your cells and going to Insert > Checkboxesthen you format them the same way you would text. I put them in the appropriate boxes.

Now for my favorite part: the formulas. I went with theREPTandCOUNTIFfunctions because they'll let me make a progress bar visual.REPT repeats text a number of times , so I used a solid block instead of a word. Then I usedCOUNTIF, which counts the number of cells I specify. The formula looks like this (I got the ASCII rectangle online ):

=REPT("▮", COUNTIF(B3:H3, TRUE))

The formula is based on my tracker, so your columns may differ. Don't forget to adjust accordingly.

I changed the font color to bright yellow so the progress bar would pop. As a fun little bonus, I went to Home > Conditional Formatting > Format cells where a formula is trueand usedCOUNTIFto automatically change the color of a completed progress bar for the week.

I used the tracker for a week and learned some valuable information

I'm a lot less consistent than I thought

After building the spreadsheet, I used it for a solid week as a dry run. Things went pretty well, I think. I was honest with myself and didn't check a box if I didn't actually do the thing. So let's break down how I did.

I really suck at getting enough sleep. I only had one day, the entire week (Saturday) where I slept more than 7 hours. I'm averaging 4–6. That needs some work. Exercise fared better, but I'd like to hit the full 7 days instead of only 5. Breakfast was easy, and so was reading and playing guitar.

I totally failed the YouTube thing. Technically, I use NewPipe, but it's the same basic thing. According to my phone's tracking, I'm spending at least 30 minutes on the platform. Coding, organizing, and spending money were also pretty bad.

I did okay with the "no caffeine at night" and "limit your junk food" thing. Go figure.

---

I gleaned some valuable insight from this project

In the end, I built a useful spreadsheet. It's helped highlight some areas where I could stand to improve a bit. And since it's in XLS format, I can use it with Excel on my phone or import it into Calc if I please. Tracking habits is a good thing and a habit tracker offers a great way to do it.