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 Credit: David J. Buck / How-To Geek

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:

I could use a standalone app version on my phone. I could use an older, archived version of the program and achieve the same results. 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 Credit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCloseCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To Geek

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

I set all text within a cell to wrap I set cell borders to all 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 to bold Habit text was changed to white and bold

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.

Related There was one magic formula that made a world of difference Credit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCloseCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To GeekCredit: David J. Buck / How-To Geek

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 > Checkboxes then 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 the REPT and COUNTIF functions 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 used COUNTIF, 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 true and used COUNTIF to 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 Credit: David J. Buck / How-To Geek

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.

Related 9 time-saving Excel shortcuts that most cheat sheets miss

Power users rely on these lesser-known Excel shortcuts to work faster across large datasets, formulas, and worksheets.

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.

View Original Article