How to Build a Habit Tracker in Excel

IMPORTANT
This template will only work with the Office 365 version of Microsoft Excel.
In this video, I teach you how to create a full-fledged habit tracker in Microsoft Excel! That way, you can track any number of habits over the course of the entire year.
And what's really amazing about this template is that it comes with a fully-automated dynamic dashboard that operates without the use of any macros or pivot tables.
Are you ready to push your Microsoft Excel skills to the test and build this incredible template all the way from scratch? If so, then get ready because this is going to be quite a ride!
LINK TO TEMPLATE
If you don't want to build it, you can purchase this template instead!
spreadsheetlife.com/templates...
RESOURCES
- Free Spreadsheet Templates: spreadsheetlife.com/free-temp...
- The Spreadsheet Life Blog: spreadsheetlife.com/blog/
LEARN EXCEL OR GOOGLE SHEETS
- Excel Total Course: spreadsheetlife.com/excel-tot...
- Google Sheets Masterclass: Coming soon...
MUSIC TRACK
Track: Ikson - Balance [Official]
Music provided by Ikson™
Listen: • #122 Balance (Official)
TIMESTAMPS
00:00 - Intro
01:43 - Setup the Worksheet Structure
02:56 - Create the Worksheet Copies
03:45 - The Habit List
05:45 - Build the Month Worksheets
14:55 - Complete the Habit List
19:03 - The Calculations Worksheet
28:19 - The Dashboard Worksheet
34:53 - Complete the Calculations
43:55 - Complete the Dashboard
52:20 - Conclusion
52:55 - Outro

[@spreadsheetlife]

Please note that this template is only compatible with the Office 365 version of Excel.

[@josecajar]

Great Habit Tracker. It's a shame the video is 365 specific only because HSTACK and VSTACK are formulas exclusive to 365. If you removed those and used a manual alternative (and the Total columns referencing Perecnt instead of #), this would be accessible to everyone. Thankfully I know my way around Desktop Excel so I could still recreate this.

[@spreadsheetlife]

You make a great point. I'm sorry that the HSTACK and VSTACK functions push this templates compatibility. But thank you so much for pointing out that there is a way around them to make this template compatible with older versions of Excel. Hopefully anyone looking for that answer will come across your comment.

[@colinmaroney2903]

Regardless, it's a great habit tracker. Hopefully, desktop friends will see this comment and fill in the alternatives.

[@josecajar]

@spreadsheetlife like Colin said, it's a great tracker. Thank you for making it. Hopefully, other Microsoft users can use your guide and my comment to make this.

[@tubhyamsoni9815]

​@@josecajar pl help I don't get it 😢

[@NdifelaniMurida]

I completed the entire tutorial. I feel accomplished especially because I am new to excel.

[@spreadsheetlife]

That's so amazing to hear! You should absolutely feel accomplished because this build is pretty complex. So awesome job!! 😊👍

[@meaganlong2055]

Man! That took some good work to create, but it was so fun! I love these because I feel so smart after building the spreadsheet! Thanks for the new habit tracker!!

[@spreadsheetlife]

You are so welcome! I'm so happy to hear that you were able to complete the spreadsheet, had fun, and learned along the way!

[@JA-ft9zw]

Wow, It took me a couple of hours to complete it but it is amazing, and I did learn a couple of new things that for sure will make me come back to this video , thank you very much!

[@spreadsheetlife]

So awesome to hear that you were able to complete it and learned some things along the way! Thank you for sharing!!

[@federicoleopoldoabrar7115]

What a pleasure to do this ! I enjoyed a lot creating it. It felt like the Legos Technic I used to build as a kid. Thank you a lot ! Plus, I am sure I will find this spreadsheet very useful.

[@spreadsheetlife]

Thank you so much for letting me know. It's comments like this that really make putting together these videos worth it. So thank you so very much for sharing!

[@brianinvirginia946]

Great content, I appreciate the step by step process. I did find myself repeating some of the formulas not knowing what I was accomplishing. But I have a better respect for those that can figure all this content out. Hopefully I'll get better at understanding the formulas. Thanks again.

[@spreadsheetlife]

Thank you so much for your kind words. I'm super glad that you enjoyed the video, and don't worry. The more you practice, the better you will get at understanding the formulas. Also, I will make note of your feedback and try my best to improve my explanations of the formulas in the future! 😊👍

[@AT-kp4hc]

Thank you for making this. I love it

[@spreadsheetlife]

That's so wonderful to hear!! Enjoy using the template and may it serve you well!

[@soumyaparida882]

Thanks to your patience to make this masterpiece!!!

[@spreadsheetlife]

Thank you so much for your kind words. I hope you enjoyed building the template and love using it!

[@vasishthroy3258]

Bro really great work. I learned so much from this and it's all thanks to you.

[@spreadsheetlife]

Thanks so much! I'm ecstatic that you learned so much from this!! 😄🌟

[@bryanpeters9197]

Amazing! This was a great video. Thank you for doing this!

[@spreadsheetlife]

Thank you so much for watching and for your feedback! I'm so glad you enjoyed the video!

[@momfit2011]

This is something I’ll definitely use!!!

[@spreadsheetlife]

That's so awesome to hear!

[@janiceee___213]

Very useful, love it!!

[@spreadsheetlife]

😄👍

[@jelizazapata6018]

thanks this video is awsome, you explain so well much better than my math theacher 😊

[@spreadsheetlife]

I'm so glad that you enjoyed the video and find my explanations easy to follow! I'll do my best to keep it up. 😊👍

[@prabhudasdhawali2385]

Hi, You are so amazing, I loved your content.

[@spreadsheetlife]

Oh my goodness. Thank you for your kind words. I'm so glad you enjoy the content so much!

[@raulopezarch]

Thank you very much!

[@spreadsheetlife]

You are very welcome! 😄👍

[@tbterrorist]

Great video!

[@spreadsheetlife]

Thanks so much!

[@wiltter1582]

THANKS YOU BRO 10/10

[@spreadsheetlife]

You are so welcome!!

[@sultanalamsyah4981]

Big thanks you sir. Finally found a good excel knowledge for time management. can I represent it to the community and educate them on how to make it?

[@spreadsheetlife]

You are very welcome! I'm so glad you discovered this video. Yes, please take this knowledge and share it with others!

[@boringcook]

Your narrative is super! thank you for good work!

[@spreadsheetlife]

Thank you so much for your kind feedback!

[@user-nd3eg2ug3v]

MORE THAN GREAT JOB 🤤❤

[@spreadsheetlife]

Thank you so much for your kind words! I'm so glad you enjoyed the video. 😊

[@user-gu5qs4ip3d]

Great Video and instructions. It works great for my Excel 2019. However, I can not get the Yes in S4 or the No in R20 to work. Do you have any advice. Thanks again, great video.

[@spreadsheetlife]

The formulas in these two cells are pretty long and complex. I'd have to take a look at your workbook to diagnose the issue. You are welcome to send me an email with your workbook, and I can take a look at it for you. spreadsheetlife.com/contact/

[@christianarmanarile3179]

thank you so much for this!

[@spreadsheetlife]

Of course! Thank you so much for watching and commenting.

[@bmrkalj7653]

Hi, thank you for this habit tracker. I have a problem though when creating the calculations - when I enter =Habitlist in field B5 it copies the list from Habit no2. Like its not matched. In Habit list sheet, Habit 1 starts at B4, while in Calculations sheet it starts a B5.

[@spreadsheetlife]

Hmmm...how strange. It should still work correctly. If you'd like, you can email me and I can take a look at your file for you. spreadsheetlife.com/contact/

[@dindalestari4853]

​@@spreadsheetlifehi, thank you for sharing this habit tracker. Anyway, I have the same problem. I don't use office 365 but I use Excel 2010. Is that the problem? Can you help me, please? Thank you.

[@spreadsheetlife]

Since you are using Excel 2010, then yes, that is probably the problem unfortunately. To fix this, simply reference the top cell of your habit list like so. ='HABIT LIST'!B4 Then copy this formula down with the fill handle. I hope this helps.

[@dindalestari4853]

@@spreadsheetlife thank you. I'll try later. And I cannot find HSTUCK function in my excel, the how to fix it?

[@spreadsheetlife]

Instead of using the HSTACK function, you will have to reference each cell manually (kind of like we are doing with the habit list) from the proper sheet. So it will take a little more effort and time compared to using the HSTACK function. Hopefully this makes sense. Let me know if you need more guidance.

[@nayakangelina6909]

MY online office 365 doesnt have chart move option or any chart tools tab. what shall i do? i have already come a long way?? at 46:00. please help me. i really want this tracker to be ready. i m loving it already

[@spreadsheetlife]

Email me at [e-mail], and I can help personally!

[@stoicOisee]

Hi sir hope u doing fine sir actually im getting some errors in my sheet.so i have emailed u please look into it and make nessesry adjustments please as i m unable to do that thanks

[@spreadsheetlife]

I'll take a look and email you back. 👍

[@pratikshadas262]

20:53 Here when I'm writing =habitList....it's showing from habit 2 to habit 10 then #value! Despite even copying it from the habit list sheet page, it's getting pasted the same as I mentioned before. Also while writing the formula, a small icon ( a small grid with 2 boxes selected) is showing. Clicking it isn't doing anything. Is it because the main habit tracker page has the list from B4 and here we are doing on B5 or is it just some setting problem?

[@spreadsheetlife]

Interesting. The easiest way for me to help would be if you sent me an email with your spreadsheet attached. This way, I can better diagnose the problem for you.

[@sayanghosh7981]

How to add new habits? Whenever I am trying to change the range of habitList greater than 10 the selection is going back to 10 habits (from h1 to h10)

[@spreadsheetlife]

So if you want more than ten habits, you have to build the template with the number of habits you want from the beginning. Does that make sense?

[@AlborzYaghubzade]

Hey mate this is a great video but the problem is that the hstack don't work i will email you and can you give me any tips or help to use something else for the hstack part in calculations

[@spreadsheetlife]

I got your email and sent you a response. 😊👍

[@user-jz3br1ku8k]

Hi there, Can you please tell me the alternate formula for the total for % (between 27:18 - 28:08) I am using google sheets and it does not seem to like it when I use the # symbol

[@spreadsheetlife]

Oh wow! It's quite impressive that you are attempting to build this in Google Sheets. Kudos to you! To answer your question, the # symbol does not work in Google Sheets like it does in Excel unfortunately. So as an alternative, you can just select the cells that contain the data you want to SUM. So instead of =IF(SUM(C5#, C21#... You would have: =IF(SUM(C5:N5, C21:N21... I hope this helps.

[@stmabruroh]

In calculations, when I enter =habitlist it works. But there is a problem with the sequence number, mine starts from habit2, even tho it should be habit1. Help me please.

[@spreadsheetlife]

Send me an email and I can help!

[@rpmuthu9547]

Can i use it excel 2016 for this ?

[@spreadsheetlife]

It is possible, however there are several formulas in the video that you will have to change to make it work properly in Excel 2016. So it will be a bit of a challenge. I'm sorry that is the case.

[@BayuPangreksaBuwana]

what sould i do if the formula can't be applied in my excel?

[@spreadsheetlife]

Which formula are you struggling with?

[@JorgeVen-zy4ke]

Thanks for the video. In my excel the formula =HSTACK does not appear, what can I do? Greetings

[@spreadsheetlife]

Unfortunately, without the HSTACK function, you will have to manually setup the references for each cell. For example, in cell C5 on the Calculations tab, the reference should be: =JAN!AI12 I hope this helps.

[@Rick88993]

@@spreadsheetlife CAN YOU EXPLIAN IN DETAILS HOW SHOULD I DO ??

[@spreadsheetlife]

It would be difficult to write out all the details via the comments. If you'd like, you can email me at [e-mail], and I can be more help there. 😊👍

[@startiny5883]

​@@spreadsheetlifethankss. in my excel version also hstack isnt working so i ll fill the data manually at the end of the month. but the video was great❤❤❤

[@kamakshigurunathan404]

@ Time 40:13 IFS function is not working in my excel version. could you guide with alternative formula please

[@spreadsheetlife]

If you are unable to use the IFS function, you will have to use a formula containing nested IF functions instead. If you need assistance with crafting this formula, feel free to send me an email. 😊👍

[@erlinabudiningsih9285]

Can you help me to give explanation abaut the formula in calculation because me fail....thankyou

[@spreadsheetlife]

Sure, I can do my best to help. Which formula are you referring to. Can you give me a timestamp?

[@jelizazapata6018]

hello can you make a book tracker ?

[@spreadsheetlife]

Hi there! I can add it to my list of potential future videos for sure. And if you really need one in the meantime, you can get one here: spreadsheetlife.com/templates/reading-dashboard/

[@MRXST_Brigade]

I got a problem at the Calculations when I type =habitlist it types Habit 2 not Habit 1 help me

[@spreadsheetlife]

Which version of Excel are you using?

[@aileen732]

Can this be created with numbers instead of icon sets?

[@spreadsheetlife]

Yes, you can modify it that way if you'd like for sure. You'd just need to adjust your count formulas.

[@thisishowilive143]

I AM NOT ABLE TO DO THE FORMULA 16:38 ITS SAYING U CANT ADD NAMES I DONT KNOW WHERE I WENT WRONG CAN U PLZ HELP

[@spreadsheetlife]

For sure. Send me an email, and I can help. spreadsheetlife.com/contact/

[@shannonsmith8135]

Is their an alternative to pressing F4, I’m trying to do it on a Mac laptop on OneDrive and have tried to type the $ in but that isn’t working either

[@spreadsheetlife]

The F4 key may not be directly available on the main keyboard. It might share a key with another symbol. However, you can probably access it using the Function (Fn) key. Try to press the Fn + F4 keys at the same time. Let me know if you are still having trouble after trying this.

[@shannonsmith8135]

Hi thank you that worked, I think it is all working now apart from on the dashboard I can’t unselected a habit or month to make it for the whole year and all the habits?

[@spreadsheetlife]

To deselect a habit or month, select the cell where you to deselect it, and press the delete key on your keyboard. I hope this helps.

[@sander8895]

How to make a streak count?

[@spreadsheetlife]

Unfortunately, calculating a streak would be a little difficult for this spreadsheet build in particular. If you'd like, you can send me an email and we can discuss a potential solution, but it will probably be a little involved.

[@erlinabudiningsih9285]

Why i use the formula in w calculation thre is wrong.....

[@spreadsheetlife]

Which formula are you referring to?

[@zdrastvuisolntse2964]

Can somebody help me please ,i am stuck at @10:00, I cannot get this formula to work in my excel, i get an error message saying ''there's a problem with this formula''

[@zdrastvuisolntse2964]

Someone i know found the issue, in my version of excel you use a '' ; '' instead of '' , '' when writing conditions, thanks anyhow!

[@spreadsheetlife]

Glad that you were able to figure it out!

[@Godletrich]

@@zdrastvuisolntse2964 Thank you for your help mate! I was stuck at the same place and couldn't figure it out

[@spreadsheetlife]

Glad you were able to figure it out! I'll try to make a note in future videos that the argument separator could possibly be a semicolon depending on where you live.

[@Rick88993]

HSTACK and VSTACK alternative???????

[@spreadsheetlife]

Without the HSTACK or VSTACK functions, you will have to manually setup the references for each cell. For example, in cell C5 on the Calculations tab, the reference should be: =JAN!AI12 I hope this helps.

[@diogobaptista367]

Hey the percentage formula does not work can you help me with it?

[@diogobaptista367]

I already understood it I need to use ; instead of ,

[@spreadsheetlife]

For sure! Send me an email with your template attached, and I can look at it for you.

[@user-vg9gu5jr3p]

Hstack formula not working in ms excel

[@spreadsheetlife]

Are you using an older version of Excel? The HSTACK function is only available in the Office 365 version of Excel unfortunately.

[@armandocaputo9470]

I GET A MESSAGE SAYING YOU HAVE ENTERED TOO MANY ELEMENTS FOR THIS FUNCTION, HOW DO I SOLVE IT?

[@spreadsheetlife]

Hi there. Without more context, my advice is to check your number of argument separators. This might be the issue.

[@user-wx6no1ny8h]

For some reason at 10:05 while I'm writing it correctly it doesn't count. What can I do?

[@spreadsheetlife]

Hmmm...where are you located? For some countries around the world, a semi-colon is used as an argument sepator for functions instead of a comma. This could potentially be the issue. But it may not be.

[@user-wx6no1ny8h]

@@spreadsheetlife I am from Greece.

[@spreadsheetlife]

Ok awesome. I suggest trying to use a semi-colon instead of a comma as the argument separator. This may solve the problem for you.

[@user-wx6no1ny8h]

​@@spreadsheetlifeAt 35:45 while I wrote it correctly for some reason it gave me #N/A and not 33%. Do you know what I can to do?

[@user-wx6no1ny8h]

​@spreadsheetlife please help me.

[@Lb.batman49]

I don't get hstack function in my laptop excel 2021

[@spreadsheetlife]

Hi there! Yes, I believe this is my mistake. I double checked the documentation online, and it looks like the HSTACK function is only available for Office 365 subscribers. I must have been released after Excel 2021. I apologize for the inconvenience.

[@najlaehm7463]

what we can do @@spreadsheetlife

[@spreadsheetlife]

You'd have to come up with a different formula. You're welcome to send me an email and I can give you more detail there.

[@hima8325]

Can I get the alternative formula please? I am stuck in the middle because of hstack not working on the excel. @@spreadsheetlife

[@spreadsheetlife]

Send me an email, and it will be easier for me to help there. spreadsheetlife.com/contact/

[@spreadsheetlife]

For those that are interested, I have recently released a new affordable Excel course geared towards beginners. You can find out more here. spreadsheetlife.com/excel-launchpad-course/

[@amari_shumari]

So at 22:30 I entered this Formula =HSTACK(JAN:DEC!AI4) but it's not working, I'hv an error #NAME? in My cell

[@irtazaasim5223]

In calculations , Yes and No (longest one) function is not working. It shows me "####" please help me to fix it. I'm very upset.

[@spreadsheetlife]

There are two reasons for a #### error. It could be that the cell is not wide enough to display the calculated value. The other reason could be that your formula is resulting in a negative date or time. You are welcome to email your spreadsheet to me, and I can take a look at it.

[@karanmehta8147]

hello, i'm stuck at hstack function which is not available in excel 2019 what can i do to replace it

[@spreadsheetlife]

Unfortunately, without the HSTACK function, you will have to manually setup the references for each cell. For example, in cell C5 on the Calculations tab, the reference should be: =JAN!AI12 I hope this helps.

[@kiarakolher2202]

@@spreadsheetlife Hi there, I tried to create this spreadsheet and I also got stuck with HSTACK.. then i individually referenced cells in YES and NO table.. but i am now stuck at TOTAL column,, where you have used =SUM(C5#) function.. can you please provide an alternate solution to this?

[@spreadsheetlife]

Absolutely! So instead of using =SUM(C5#), just select the range of values you want to sum on the calculation sheet instead. Does this make sense?