Honestly - you should. You'd be AMAZED at the number of people making nearly six figures who have no clue how to use excel. Knowing how to get stuff done in excel is a huge differentiator in my career.
for those who have the problem with the formula for total 36:43 , here is the solution - =SUM(INDIRECT(ADDRESS(income_min_row;COLUMN())&":"&ADDRESS(income_max_row;COLUMN()))) the problem is that after min_row/max_row we need ";" not "'," also in the beggining we need "=sum" if you get value in the cell. Amazing tutorial man, thanks!
I can't imagine the amount of time it must have taken to make this entire 6 hour video as soon as it was. Your explanations were so clear and you didn't skip any step. Some people will assume that some things are common knowledge in excel and will skip over it and that will ultimately lose some viewers. This was really an amazing dashboard and incredible video. I learnt so much, not just about excel's capabilities but also being detail-orientated so make sure everything is nice and standardised creates a beautiful end result. You're one of the people the world needs - people who share their knowledge generously. Thank you.
Really appreciate your kind words! That means a lot to me. Thank you 🙂 And yes, the amount of time and effort put into this has been tremendous, no doubt. But seeing people getting value from it and enjoying the learning journey makes it absolutely worth it!
@@theofficelab Could you please let me know how you solve this step 03:06:05. I am using the exact formula=If(is_cat, RANK(tracked, INDIRECT (tracked_range),0), "") i am having #REF! Error
My thoughts precisely. The man is a god. I tried following along and kept pausing. Unfortunately the screen goes dark when the video is paused. So I decided to opt for plan B. I copied the transcript and pasted it into Word. 48,913 words spread over 140 A4 pages...and that was only from "Budget Plan Setup". I didn't even bother with the introduction. And no, I'm not going to print it all off.🤣
So far above and beyond any other budget worksheet I have ever seen it doesn't even compare. Even my "I need paper and pencil" wife is interested in using it. Can't thank you enough for this. Well worth the time spent.
@@vijayparmar5213 I built the budget planner as well, everything works. There a slight differences between the 365 excel and my 2017 version which led to some confusion when he works with formulas that use arrays (you need to use ctrl+Shift+enter instead of just enter) but got that sorted out thanks to google :)
This is unbelievable. I was thinking of buying your template at first, but then I wanted to see how it works so I took the tutorial. Now that after +20 hours I'm finally done, I'm thinking of buying it just for gratitude. Thank you for this, I really appreciate the effort of putting this video together.
Just finished this absolutely incredible tutorial. I'm a university student with absolutely no Excel experience, and yet this guide was straightforward, easy to follow and unbelievably thorough. I'm amazed that this kind of tool has been made readily available on the internet for free. Thank you so much for the time and effort you have put into this
I followed this tutorial from A to Z. You, sir, are an absolute genius. The way you know your thing, the time you took to break down every single step, the tone of your voice... Everything was perfect. You could have just shown us your work and put a purchase link down in the description. You took +6hrs of your time to teach us noobs how to build something amazing. I am forever grateful. Thank you so much!
@@theofficelab =INDIRECT(ADDRESS(income_min_row,COLUMN())&":"&ADDRESS(income_max_row,COLUMN())) I'm having problem in this phase. after entering this formula, it shows #VALUE error
This is truly an ultimate personal budget tracker. Your generosity and patience to share such a detailed oriented 6 hours videos and valuable knowledge is highly appreciated!
TIPS bellow it took me so many days (around 10 days on my free time) but I managed to follow and create my own! I cannot believe what I've managed to do (with your guidance OBVIOUSLY!). I confess some of the explanations were a bit too advanced to my excel level but I am very happy with the result nevertheless. Thank you for being so thorough. If anyone is following, I have a couple of tips: make sure of all your spellings on the formula bar. I had to go back a few times because the result seemed to be initially correct (when the answer was blank) but it would give me errors when I expected to have something returning. Usually it was a typo on the formula itself. Also, create your tracking list within the expenses, for example, in the order of your importance such as "groceries" in the first line, "rent/mortgage" on the second, "hydro" on the third, and so on... I just entered randomly and it makes a difference when populating the doughnut chart. Same for the other ones too (savings and income), top to bottom importance. If I remember anything else I will come back to update this comment. Thank you!
I want to primarily thank you for this amazing work I call, "a piece of art." I'm an analytics consultant, and there were many functions you explained here that I never knew were possible with excel. I will be using a lot of these moving forward in the production of a lot of my dashboards. Very well made. I went ahead and followed your explanation step by step to create this document for myself and it works perfectly on my mac.
Awesome that you finished on a mac! I am almost complete as well, but I cannot find the "properties follow chart data point for current workbook"[4:14:08] setting in my Excel (v.16.68 macOS). Is there a work around that you found for this? Thanks!
@@BGphotographyBG I also spent some time looking for that checkbox until I realized after moving forward that it doesn't exist for Mac. Excel for Mac already follows chart data properties on a single workbook basis unless you're pulling from other workbooks (purposely linking cells/charts to/from other workbooks). Just keep going with his steps and skip this one 🙂
@@fadedgames6775 Thanks for the response! Interesting that you got it to work, I have rebuilt my charts a few times and haven't been able to solve it. Anytime I change my period my charts will not hold their color and they reset to the default.
I also highly recommend adding a Reoccuring column in your Budget Tracking sheet in between Details and Balance with a dropdown for Yes or No, that way you can see what subscriptions you pay for or Loan payments every month or year and decide if you still want those subscriptions and what not.
Appena finito questo incredibile tutorial, ho iniziato avendo delle conoscenze di base in Excel. Armato di buona volontà ho provato a comporre questo puzzle gigante, dopo giorni ci sono riuscito anche grazie all'aiuto degli utenti nei commenti dato che ho usato una versione passata di Excel. Se qualche altro italiano ha problemi o con la traduzione o di altro tipo non esitate a commentare e vi darò una mano se riesco. Grazie per il lavoro fatto dal canale è stato davvero stimolante.
Ok, this is seriously the BEST Excel video on personal budgets, period. 👆👍❤👆👍❤👆👍As a 35 year veteran Excel trainer and RUclips channel creator, I am absolutely floored by the incredible quality of virtually everything you've described here. I decided to create my own dashboard because the most popular money management applications have all gone into the cloud and I didn't want my information put at risk. Your gorgeous presentation with exquisitely thorough formulas and functions and elegant design elements makes this comprehensive tutorial a pleasure to watch but more importantly, a pleasure to actually use. Practical, yet so well designed that it really does provide an easy system that any one can use. I'm in awe of your skills. Coming from someone who teaches Excel up to the programming level. I learned so much even though I've used Excel since the very first version. Even your voice is perfectly modulated to make it easy to follow and understand though I did have to speed it up a bit - that's just because most of the techniques I already knew. 😊👏😊👏😊👏 EDIT: After walking through the whole video, I absolutely bought the template as a way to thank you for your work. 100% worth every penny!
What a great tutorial. I've been using spreadsheets since the '80s and after this, realised that I've just been scratching the surface. The power of tables is incredible. I have created, and am using this tool and have even expanded it to add 'Transfer' as well as expenses and income. Transfer moves money between accounts without being identified as income or expenses. I have also added a fiscal year option for the selected period, which was hard because the selected year and selected period names are referenced almost everywhere. For those wanting to add fiscal year, my advice is to dive in and do it yourself, you will learn so much. My last modification was to remove the savings function. I am retired so everything that comes in, goes out. Highly recommended.
To everyone who asks to share the excel document: If you check the description, you will find that ur request is forbidden. Please be careful. You could just make a video on that single category and post a link if u are keen on helping. Just read the description before you start
I have finally completed this beautiful masterpiece. Point to note is excel 2019 on PC generates an error if used as =SUM(FUNC.A * FUNC.B) But will work if only written as =SUMPRODUCT(FUNC.A * FUNC.B). All other formulas work splendidly. Thanks OP
I spend almost 2 hours just to figure out why there is the problem with function above, almost went crazy over this since I really want to finish this Budget Planning. Thank you for posting this since it really help me to sold the problem. You are great! Thank you so much! 🙏
@@CM7016 Thank you. Can you help me with the tracked columm at 2:39:40? I used SUMPRODUCT too, however, only Income & Saving amount work but the Expenses is all zero. I reviewed many times and still can't resolve it. Thank you very much! =SUMPRODUCT(Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year))
Pretty much the best tutorial I have ever done! Such an amazing end product! Really easy to follow, you have a very calm voice and I genuinely enjoyed following along. I watched this video in 30-45 minute parts and just took it easy. Made a million typo's in the code and had to translate all Excel functions to Dutch.. Now it's done and I can't wait to start budgetting and tracking! Thanks so much!
Three days, almost 26 hours invested, and so many headaches... I FINALLY FINISHED!!!!! mWHAahhaHa VICTORY SCREEEEEEECH!!!!!!!! Excellent tutorial. Thank you so much. Coming from somebody that has never messed with spreadsheets, I have to say you are a great instructor. I made several mistakes and spent countless hours back tracking to find the error but I think I finally have a usable budget calculator.. Thanks again.
Listen, I was about 2 hours into the video, and about 4 days into the worksheet.. found an error... didn't know how to fix... bought the sheet. And I'm impressed.
Wow, this gotta be the greatest Excel sheet I’ve ever seen. Can’t imagine how much time and effort you had to put into this and then you just share it with us for free 😮
Absolutely wonderful tutorial, learned a lot along the way and incredibly powerful end result tracker. Couple of future enhancements that I would love to see in an updated video: 1. Net Worth - Visualizations, Calculations and KPIs added on top of income, savings and expenses. Include assets and liabilities categories. 2. Amortization Schedule - for paying off credit cards line of sight, APR tracking, principal tracking, min payment tracking etc. 3. Portfolio Balance Sheet - track balances for checking, savings, investment and retirement accounts. Associated rates for totals, growth rates, avg growth rates 4. In period selection, enable “Year to Date” calculations
Waiting and hoping to see that video because I need these updatest too, at least the net worth and amortization schedule sections. @theofficelab please let us know if you'll be able to make an upgrade or have something similar to this that can be referenced to or purchased, would love any help!
This video was insane. Was able to build the entire model and include my own added credit card tracker which feeds into the budget dashboard. Couldn't have done it without the expert guidance from the narrator. Good job!
I just finished the tutorial, and I want to profoundly thank you for your hard work and generosity. I've been thinking about building a system for tracking my personal finances for a long time now, but as a complete newbie at Excel, I would never have achieved anything like this. You didn't just help me with the initial goal of setting up a finance tracker; you showed me a new way of thinking and sparked in me a love for numbers I never knew I could have. This is truly an art form. Thank you.
Damn, your words have seriously moved me 🥲 Thank you for such a kind and encouraging feedback. Especially loved the "sparked in me a love for numbers I never knew I could have" part. That's awesome!!
My god, this is a work of art. I am absolutely obsessed with your brain. Thank you so much for this video. I followed it step by step at 0.75x speed and now have the budget spreadsheet that I have been looking for for YEARS! My only question is; can I lock/protect/freeze the spreadsheet prior to adding in my data, so that the original spreadsheet is saved when I inevitably delete something I shouldn't? It would be amazing if there was a way where I could only edit the actual input value boxes for my different income/expenses/savings amounts and not accidentally delete formulae etc that will affect the use of the whole spreadsheet.
Never did I thought I'd be looking at an over 6h long video about Excel untill I saw this one. The logic in this, the way you think it through, the formulas you have used it's just absolutely mind blowing. I have absolutely loved it and it has taught me a lot. Thank you for a tutorial well put.
Thank you for your kind words! 🙂 and yes, developing the template alone took a huge amount of time and many many iterations. Let alone the production of the tutorial 😄 But hearing that it gives people value makes it 100% worth it!
I am still in the middle of the tutorial but I really want to share some feedback. In first, huge congratulations for the amazing project you created here. You tackle all the complexity through your clear explanations and that (even in 0.75 speed 😅) is truly amazing. As I read in other comments, I've never learned anything that complex in excel but I am continuously thrilled each day to keep going and following a few more minutes until the end. Thank you very much for this class, this is actually "a piece of art". The way you tackle the situation by work around with rows and names references, that is top-notch. Congrats again!!
This is by far, the most complicated tutorial and spreadsheet that I have ever followed. I don't normally get to the end of a tutorial without something going drastically wrong, but the way that you have displayed and described this tutorial has been astonishing. I have followed it through to the end and everything works. Thank you for such a brilliant tutorial.
Hello, I am stuck at 36:00 , I don’t know what I did wrong, even tried to do it all again. When I entry the =Address, excel gives me an error about the formula…
Wow, I can't thank you enough for creating this tutorial! After countless searches, I stumbled upon your video, and it's been a game-changer in helping me build my interactive personal budget. Your explanations were clear, step-by-step guidance was spot-on, and the result is a budget that not only makes sense but is also tailored to my needs. Your effort in putting this together is truly appreciated, and I'm now feeling more confident and in control of my finances. Kudos to you, and please keep sharing your wisdom! Cheers!
First of all I want to join the people praising your work. This tutorial as well as the downloadable version of the Worksheet are outstanding in quality and I can't wait to see more from you! I noticed just one small oversight in the Worksheet (in case you don't mention it later, I'm not yet done following the tutorial); when calculating the comb_rank and after that the comb_rank_unique, I ran into a problem when using more than 9 categories of expenses. since the comb_rank just writes the numbers in sequence it turns a 1 digit and a 2 digit number into a 3 digit number, which of course gets ranked way lower than it should in the normalized rank. For example I have a tracked_rank 1 and budget_rank 13 entry. it is the highest tracked amount and should be on the top of the list, but the comb_rank is 113 so it ranks lower than an entry with tracked rank 8, budget_rank 6 and comb_rank 86. Do you have a solution to this? I will also try to find one and update my comment if I do. EDIT: I do have a solution, but it's not 100% clean. It doesn't completely fix the issue but it lets you have 9999 categories instead of 9 before it breaks. formula: =IF(is_cat; NUMBERVALUE(tracked_rank & (budget_rank/1000)); "") in the column comb_rank I just divide the budget rank by 1000 before appending it to the tracked rank. this way in the example above the items would have a comb_rank of 10.013 and 80.006 and everything should be sorted correctly. Ths is not a perfect solution, because it also breaks if you have more than 9999 categories, but in practice you should ofc never have that many (and if you do just divide by 10000 for a total of 99999 categories). So hopefully this helps somebody, cheers.
@user-lt8uv1lk8j Basically just use my formula instead of his if you want more than 9 categories, no further math needed. :) That Was the tl:dr, however if you want to understand why this works, let me try to explain: So we have 2 numbers that we want to sort or rank. If those numbers are different that's easy to do, we rank them (in this case) from lowest to highest, easy enough. But what if we have the same number twice, which one should be higher? To solve this we introduce a secondary number. We call the first number tracked_rank and the second number budget_rank. So for example if you have a tracked rank of 3 and a budget_rank of 5 and I also have a tracked_rank of 3 but a budget_rank of 6, you should be higher rated, makes sense? We realize this with a function. the Funktion takes 2 numbers and combines them. Not by adding or Multiplikation, but simply by writing them next to each other. So a 3 and a 5 become 35. My 3 and 6 become 36 and now Excel can rank them because 35 is lower than 36. Makes sense so far? This all works well until you have 2 digit numbers (because you have for example 13 expences and 13 has 2 digits). Now this screws the whole System because imagine you now have the rank 1 but a budget_rank of 13 and I have rank 3 with a Budget rank of 4. You should be rated higher because your rank is lower, the budget_rank shouldn't matter at all here, do you agree? But the Formula is dumb and just does it's thing, neaning you get a 113 (for 1 and 13) and I get the 34 (for 3 and 4). So now I will be rated higher because 34 is lower than 113. This is the Problem we have. I hope you could follow so far, if not feel free to ask any questions! So how do we solve this issue? My solution Was to introduce floating point numbers (e.g. 0.04) since they work differently in this context. So the only thing I did is divide the budget_rank by some large number, in this case 1000. And that's all my function does differently. Why does that work though? Imagine the example from before, where you have the numbers 1 and 13 and I have 3 and 4. Remember, we expect Excel to rank you higher because 1 is smaller than 3. In my Formula the budget_rank is now first divided by 1000 so your numbers become 1 and 0.013 and my numbers become 3 and 0.004. Combine them like before and now your number is 10.013 and my number is 30.004. See how that works now? Your number is again smaller and will therefore be ranked higher as it should be and the budget_rank is just affecting your number after the decimal point. Note that this still works to solve the original Problem as well. Imagine for example that you have the numbers 3 and 4 and I have 3 and 5. The first number is identical as before so now you should be ranked higher because of your budget_rank again. With my Formula you now get the Combined number of 30.004 and I get 30.005, so you do get ranked higher as you should. Can you see why we got These numbers this time? And that's everything. I hope the explanation makes sense for you, even if it's a bit long. I also want to point out that you don't have to do math at All here, Excel does everything for you. Math is only needed to understand the Problem and the solution if you are interested in that. Feel free to ask if you have any questions and good luck with the Excel project :)
Not only the dashboard is top quality, but also your way of explaining things and how you presented this guide as a straight 6h developement in the cleanest way ever is astonishing. Thank you for your amazing work and time. I had an immense pleasure following this guide.
Wow, I can't thank you enough for creating this tutorial! 🌟 After countless searches, I stumbled upon your video, and it's been a game-changer in helping me build my interactive personal budget. Your explanations were clear, step-by-step guidance was spot-on, and the result is a budget that not only makes sense but is also tailored to my needs. Your effort in putting this together is truly appreciated, and I'm now feeling more confident and in control of my finances. Kudos to you, and please keep sharing your wisdom! Cheers!
Finally completed using Excel 2019 version. Took a few days, typo errors, and some formulas had to be corrected for this version. Totally worth doing yourself, to fully understand the how powerful this spreadsheet is and excel itself! Like to send shout outs to everyone in the comments that posted formulas or keyboard commands for excel versions 2019! Time to Execute this powerhouse Template......
@@theofficelab 2 of 3 adjustments made @30:15 When entering the formula =min(row(income)) it is not taking automatically but getting figure 10 And in next row when putting formula= max(row(income)) it is also showing figure 10. Solution to this error was given by @joshuabolen6617 "Type in the equations again separately under the table as seen at 30:59 and 'CTRL+Shift+ENTER' instead of just 'ENTER' "
Tutorial is amazing! Thank you so much for making this. If you are like me and have a lot of expense categories (10 or more) you may notice that the combined rank gets thrown off a bit by having some numbers with multiple place values and then throws off the sorting. A simple fix for this I found was to add a +10 to the end of the formula in the "tracked_rank" and "budget_rank" columns. This allows for up to 89 categories in each of the different types. If you were wanting to use this for a business with even more categories you could simply add a larger number (example 10000) for a significantly increased amount of categories to be supported. Hopefully this helps someone! Updated tracked_rank: =IF(is_cat,RANK(tracked,INDIRECT(tracked_range),0)+10,"") Updated budget_rank:=IF(is_cat,RANK(budget,INDIRECT(budget_range),0)+10,"")
Hello, this is definitely going to be useful for me. Problem is I don't know where to place these updated versions. Could you be so kind to indicate the time in the video? Thanks so much for your help.
I tried this method, but the result is still "#REF!". Actually I also changed SUM into SUMPRODUCT to fill the 'tracked' column. Is it because of that? please advise... thankyouu
Amazing! I'm an advanced Excel user, but this is the sort of the video that makes me uncomfortable. The whole video is fantastic, but it's even better to absorb your way of thinking, getting ideas of manipulations or features usage I didn't knew. Super high quality. I will watch it fully later.
@@theofficelab Can you please help with this issue: I have input the formula at 2.38.01 and it is pulling the expenses and savings from the tracker correctly. But for some reason no matter what date I put in for any type of income, it will not pull through. I have no clue how to fix. A response would be greatly appreciated.
Matheus, As you said you are an advanced Excel user. I am using Libre office. can this be made to work with that. I have seen alot of this sort of thing. But not like this. I have gotten to the 25 Min part . thanks
2:39:34, spent about an hour trying to figure out why it wasn't showing me the tracked values for the Income type. Was about to give up until I went back to the Budget Planning sheet and clicked on the Income header and noticed that Income had a trailing space... never thought I would debug something like that before but I'm learning a lot so far. All I'm gonna say for anyone who goes through this awesome tutorial is to make sure there aren't any leading or trailing spaces
having the same problem myself, can't seem to solve it. Under the tracked values all is showing for me is " - ". Any suggestion or help would be appreciated.
@@jackdoherty02 Yeah troubleshooting can be long but try these out: 1) Go back to the Budget Planning sheet and see if there is anything weird going on with the spellings or trailing spaces for the headers("Income, "Expenses" and "Savings"). 2) In the Budget Tracking sheet see if all the entries and their respective year matches with the year and period selected in the Budget Dashboard sheet. 3) In an empty cell, try breaking the formula down into parts and try to see if they work as intended. Hopefully it works out
Hi sorry just saw this. Hmm there seems to be something not formatted properly in the Effective Date column in your budget tracking sheet. Try going back to the video when he was creating formatting that column and see if there was a step that you might have missed. The part of the function you showed basically extracts the year from thar column and compares it with the selected year. I still a novice at excel but hopefully that helps a bit.
I thought I was pretty good at Excel before this. I'm learning so much from this tutorial and I'm not even half way through! Thank you so much for putting this out!
it took me almost 10 hours 41 minutes to complete the whole ultimate personal budget in excel and now im able to record my personal finance more efficiently than pervious. Thank you for make this wonderful tutorial i appreciate the work that you put to make this tutorial.
@@theofficelabHi, could you please help. I'm having issues with this formula "INDIRECT(ADDRESS(income_min_row,COLUMN())&":"& ADDRESS(income_max_row,COLUMN())). NOT WORKING PROPERLY. #VALUE error accour
Hi, could you please help. I'm having issues with this formula "INDIRECT(ADDRESS(income_min_row,COLUMN())&":"& ADDRESS(income_max_row,COLUMN())). NOT WORKING PROPERLY. #VALUE error accour
After a few days of work on this I finally made it. Awesome tutorial, still working perfectly fine. For those who encounter any error following this tutorial my best advice is go back a few secs and do EXACTLY what this tutorial says, it is completely doable without any prior excel knowledge and any kind of research at all, just follow step by step and make sure you are doing exactly what the video says, you don't need any external help, all information you need is in the video, if you are having an error message just make sure you did it right, after 3 hrs is easy to miss a comma or a ) to close a statement. Thanks for this tutorial and template.
I'm done! Took way longer than it should have but I am done! Thank you so much for taking the time to make this. I have learned so much. For anyone still working on this, DO NOT GIVE UP! YOU CAN DEFINITELY MAKE THIS.
Hello, could you check whether entering multiple data (e.g. three expense items) on the same date/day gives correct results in the balance on the Budget Tracking sheet. It does not work in my BTracking sheet.Thanks
Wow! I can't say enough WOWs! I have built dashboards for the last two years, and admittedly, this design tops everything I have done or seen! I have been following your templates for a few years and can see remarkable advances, not only in the design but in the overall presentation which is clear and concise. The price to get a template and a demo template is insanely affordable. I saved hours in time of effort and in thousands of dollars. KUDOS!
i've had a personal finance spreadsheet for a few years now while i was a student, but since i'm finally earning a proper income i wanted to "up my finance game" so to speak and this video was perfect! you explained things so clearly that i was able to incorporate exactly what i needed into my own dashboard, and i learned a bunch more excel tips along the way to make my existing spreadsheet more functional/efficient. thank you, thank you, thank you; and well done, you did an amazing job!
I started this to learn how to use Excel, I have spent a couple hours every day for about two weeks, and then I stopped. I'll get back to it today because it's amazing, I've learned so much, and the comments have been also helpful! Thank you for this amazing tutorial.
My knowledge of Excel is already pretty advanced and I am self-taught, and this is exactly the next level of things that I have been looking at doing and learning. A FANTASTIC video that is very well explained. Followed it from start to finish and now I have an amazing budget planner! Definitely looking to do another one of your clips, might look at your Gantt chart one next. Fantastic!
I'd say I'm intermediate in using Excel but after watching this full tutorial I learned so many tips to help me save time in any Excel work that I do. Thank you for not only building and sharing this, but also teaching us how to make a visually appealing workbook. It took me 3 days. I wasted time choosing custom colors which I wish could be integrated in the theme colors, but Excel doesn't have this functionality on Mac. The only 2 things I did differently was order savings before expenses rather than after, as it's important for me to allocate income into savings before expenses (I wanted this to reflect visually), and I also divided 'expenses' category into 2 groups: 'Expenses' or necessary expenses (e.g. housing, utilities, food) and 'Discretionary' (e.g. shopping, streaming) so I had 4 categories in total and I made sure to reflect that in the formulas taught. Now time to get to work!
I was also searching up how to enter the checkmark for the longest time and a lot just gave me √ which is not a checkmark. It's the square root symbol.
I finished it last June but I still paid for the excel budget file to thank him for his awesome work. It took me +- 10 days to finish it (because of work and life). I can't imagine how long it took him to engineer it and film it. Cheers, man!
Could you help me one forumla? At 3:04:27, the tracked_range column, it results in a #REF error. And also says 'too few arguments for this function'. I'm stuck here, and unable to move forward, could you please help?
@@raudatuljannah6916 I am also having this issue - I'm betting there was some kind of update that changed how to do this formula. I sure can't figure it out!
@@deidrah3074 the "expenses_min_row" etc. formulas? Those worked for me exactly like the tutorial. I'm using Microsoft 365 (Version 2306 Build 16.0.16529.20100)
You need Microsoft 365 for the function to work. This is the formula i used to return "12", or the value of the bottom table. Note, this will return the row number of the last non-blank cell. So if the category is not in the table, leave the cell empty. Sorry if my explanation is confusing . Hope this helps! = MAX(ROW('Budget Planning'!$C$10:$C$19)*('Budget Planning'!$C$10:$C$19"")) you can change the data range when you get to the expenses and savings max row formula.
The best video for personal budgeting I have seen so far. Even learning many new things in Excel. Thank you so much!!! Even as a Finance major, I could have never created this clever worksheet.
This is the first video of yours I’ve watched since I’ve been looking to up my budget template game! I just want to say, I went and actually built what you did here and it works amazingly! So functional and so many interesting ideas to do things I’ve never thought of doing before- thank you so much for posting such a detailed and thorough tutorial, it was super easy to follow and you explained everything perfectly
Truly mind-blowing work you've done, surprising in every way. It's the first time I've been able to follow a tutorial and build an Excel spreadsheet from scratch at this level. Really, congratulations on the incredible work you've done.
Thank you for sharing this in depth tutorial that reflects the effort you have put into this project - well done! To your closing statement on what could be next: 1) Import transactions from your bank app, 2) format to suit the budget tracking tab, 3) assign the entire process to a button using a macro. This would make the monthly tracking an almost instant activity and I feel would compliment this spreadsheet nicely.
I submitted the same requests to them; however, include the import step-by-step reconciliation and reports from XERO and Quicken. This is the next logical test, "according to Excel." 😁
If I'm not mistaken excel no longer supports linking your bank. There is a third party app that microsoft reccs you use called tiller but I'm not sold on connecting my bank to an app I've never heard of.
I've followed the tutorial and bought the template, populated it and am using it now. I thought I was fairly proficient in Excel but some of the template workings are a real eye-opener - the design principles followed are very illuminating. Your explanations were clear and concise. Thanks very much!
Since you clearly have the newest version of Excel, can you explain to me why in the video at 33:27 I do not have the same edit formatting rule popup as this video? I am not getting the same option to select a rule type but rather just the bottom half of the pop up screen he shows of just edit rule description.
When 3 hours in you expanded the selection and was like "we will need space to get this task done" I audibly gasped. Here's to hoping I can get this done! Really am enjoying the verifiable onslaught of excel knowledge; as someone who isn't professionally trained, I'm enjoying learning about these new functions and what excel can do!
After a full week of picking away at this after work, I finally finished! Thank you so much for the in depth tutorial and great template, I learned a lot about excel I didn’t know along the way too! No that I’ve invested time in building it, I feel motivated to actually make a budget for the first time in my life. One thing I would like to add to this template is the ability to add my loans (car, mortgage, credit card etc). and when I allocate an “expense” to them as a defined category, I can watch the value of the load drop on the dashboard. Ideally it would calculate how long it takes for the loan to be paid off if I add extra payments too. I will investigate this myself but would love to see your take on it! Thanks again!
I had the exact same question, however I have not done any research on it yet (about to start the tutorial myself). Were you able to find a solution to this idea?
Thank you so much! Sadly the check mark symbol doesn't work for me - I keep getting "FALSE" error message with it. Have you tried adding CHAR(252) somehow? I read that it should work - again it doesn't work for me - shows as 'ü' or the text 'CHAR(252)'
Hello Sir, I just want to say, your sheet is AMAZING! I'm not even exaggerating. I'm truly thankful for the time, effort, patience and consideration(beginners) you put into this masterpiece. I have been looking to many channels and haven't found anything like this. I have learned a lot and will hopefully use this along the way. I hope to see more of your contents. Thank you!
This is honestly one of the best tutorials I have seen, very well thought-out, explained and simply beautiful output. 10/10 recommended for anyone who wants to practice their Excel skills and also create a tool to master their personal finances.
Hey! One question! How about a follow up to add a « Year to date » period, allowing for comparing actual income, expense & savings vs budget up to a certain given month. When choosing total year - it takes the budget of the full year into account. Comparing automatically the spent e.g. from Jan to Mar vs budget allocated from Jan to Mar would be exquisite 🙂
goated video. three days later, its finally done, and I have never been so excited about using excel/creating a calculator,etc. Excellent job done, very much so appreciated
This is by far the best step by step Excel tutorial that I have ever seen. I have learned so much about excel just working through this tutorial. Thank you for the time and effort going into this.
Thank you so much for this brilliant tutorial and all your efforts to create this masterpiece! If I may I would suggest two feature extensions: 1) Integration of 1 to n subcategories per main category and related evaluation options 2) Tracking of fixed vs. variable expenses Again, thank you so much, I am looking forward for upcoming tutorials 🙂
i completely agree, a fantastic tutorial & tool!! i added in another row of subcategories in the tracker using the same method, but taking each subcategory option from a separate hidden table in another sheet - has worked a charm for me!
@@benbarr4001 I am trying to implement another row of subcategories too! How did you setup the separate hidden table so that you can select them in the dropdown for the tracker? Edit: trying to create another column, not row, for the tracker
Hi everyone. This is indeed an amazing tutorial. Thank you so much to "The Office Lab"! Same question about the subcategories: I would also like to do this. For example, "Housing" would have subcategories like "electricity", "rent" etc... To do so, is it possible to just add more expenses table to the budget planning sheet? Therefore, there would be the Income table, then 5 or 6 Expenses tables, and then the savings table? And if I do this, will it be an issue for the rest of the spreadsheet creation, when creating the dashboard etc... In advance, thank you for your help, Cheers.
Awesome video!! In case someone else has the same problem: I had trouble with the conditional formatting of the tracked income amounts at 1:20:45 but solved it by replacing both ' with ". I'm guessing it's the same category of problems as , vs ; separating a formula's arguments.
It would be great to see a second part of this video that introduces a "Total Savings" section, where all savings and investments recorded over the tracked years are summed up in a single graph. This would give a clear overview of your entire financial growth in one place. Another useful addition could be an "Initial Budget" feature, which tracks the amount of money you had before starting the spreadsheet. Overall, fantastic work!
Wish I came across this sooner. I didn't know most of these functions and dynamic formatting existed. I work in financial accounting and will be updating my recons with this. Thank you so much for your beautiful work!
It's me again. Been a while. I finally finished This Amazing Personal Budget today. It was challenging but I managed to get it done. I want to thank you (The Office Lab) for this amazing tutorial. I learned a ton while I was doing my copy following the video. It was absolutelly one of the greatest experiences I had on Excel so far. Thank you!!!
I have not finished yet. Still 4h left but I've learned a lot of new things in excel (and people always said I was an Excel master!). Thank you for sharing this content!
I will not tell you how long it took me to do this...worth every minute, rewind, typo check, rewind, typo check and win! I, too, learned "unsagbar viel" (unsayable much...)
This is amazing, I spent the last two days creating it from this video and it's really genius. Particularly how you found a way to rank the items to obtain the new order from largest to smallest in the dashboard. My mind is blown with how good it was. There is one part that seems a little tricky at the moment after entering all of my values. If I allocate amounts each month to savings for a big purchase then how do I reflect that amount when I make the big purchase. For example each month saving to a holiday pool. Then a few months later that holiday expense may be more than a months income. When budgeted for In the budget planning tab it shows a negative amount left over for that month. That doesn't work well with the charts etc as the budget bars will start pulling down out of the defined area. The way I have made a work around is to define an income category called "transfer in from savings". Then when the savings are used to make the big purchase I have income (from liquidating the savings) and the expense opposite it to pay for the holiday. It works ok but I was wondering if there is a way this is supposed to be reflected that I'm missing. Thanks for putting the time into this, it's the one of the best things I've found on here.
I was wondering the same thing. Your solution seems like the most logical one for me. Why does it only work ok? Is there a problem with assigning negative budget to the holiday daving category?
This tutorial is all I was searching for. It's just amazing, thank you for putting so much effort and explaining it step by step. Everything works fine and smooth. I even added two more columns in the Budget Tracking tab, one for "Methods of payments" and "Fixed and Variable Expenses" and it all worked fine. The only problem is to make the charts for these columns change with the selected period. I'm still trying to fully understand those formulas, but once I get it, it will be the Personal Budget I was wanted.
Also wanted to separate fixed from variable expenses so I added it as a separate category - instead of the 3 he shows in the video I have 4. :) It's quite easy to do if you just follow along as everything is nicely named and explained.
This personal budget Excel is a game-changer! As someone who is a number cruncher, I really appreciate the level of detail and the intuitive layout. It's clear that a lot of thought went into making this tool both comprehensive and user-friendly. Thanks for sharing such a valuable resource!
For those getting erroneous returns such as "#VALUE" in some functions, and are like me using an older version of Excel, I have a solution. Later versions have a feature called "dynamic array" which this guide is using to save a bit of work in entering formulas which use arrays. But as older versions dont have this feature, we need to manually tell excel in the formula that it is dealing with an array. Fortunately this is quite simple, and there are 2 ways: 1) Surround the formula in { } 2) Holding ctrl + shift when pressing enter when initially entering the formula does this automatically You'll need to do this in any of the formulas where the uploader mentions that arrays are in use
OMG I love you, i was really stuck at one point and couldnt find a solution in the internet because i didnt really know what to search for... But your solution was perfect... Thank you so much
Finally completed the template. Wonderful experience and the minute details put into the template is so cool and awesome. Thanks a ton for creating and making a tutorial for this template.
This tutorial and the excel file itself are simply amazing! Lot of new functions and tips about excel that I never even imagined, I found it very interesting! Finally I found a solution for budget tracking! There is only a single point that would like to evaluate if is worth to implement: like some incomes are often "shifted" to next month since they are late, it would be useful to have similar behaviour for special type of expenses. For example when I make a payment with a credit card, it would be useful to track it on the same day of the purchase, but the effective refund will happen on a fixed date of the next month. Probably it requires the creation of a specific type of expenses or a label. However, thanks again for this magic tutorial!
Absolutely genius work, really thanks for sharing!. I am facing an issue with tracking of income from the budget tracking to the budget dashboard in 2:39:42. From all types (income, expenses and savings) in the budget tracking, the calculation of tracking in budget dashboard considers only for expenses and savings and gives no value tracking for the income categories. Does any knows how to solve this?, Thanks
This is the first video of yours I’ve watched as I have been looking for a more dynamic personal budget template. I have followed your entire video (obviously it took me close to a month to accomplish 😅) I just want to say, I followed the video closely and actually built it on a mac what you did here and it works amazingly! This tutorial video of yours is a masterpiece and a meditation in itself. I got to learn so many formula's and so many new areas of microsoft excel tool and I had never imagined before being it such an elaborative tool ! The way you have explained the concepts, the workflow and logic is seamless and awesome and It's something hard to think of for me being a beginner. Thank you so much for posting such a detailed and thorough tutorial, it was super easy to follow and you explained everything perfectly
Thank you 🙂I really appreciate your kind and comprehensive feedback. It's my absolute pleasure and makes me super happy to hear you enjoyed the tutorial.
@@theofficelab I'm also doing it on a mac (your tutorial is amazing!) but have hit a snag because I can't find the "properties follow chart data point for current workbook" checkbox in the MacOS excel options. Any suggestions on how to tackle this issue?
@@kellycollege Same issue. I have the most updated version of Excel on Mac, and there is no option to uncheck the box, "Properties follow Chart Data point for current workbook". I've seen several comments from Mac users on here that say the budget works perfectly, so maybe it isn't a critical step? No idea.
This took me two and a half days to complete, but my goodness is it worth it! Fantastic explanations throughout helped to improve my understanding of Excel's functionality too. Thank you so much!
I would like to thank you so much for everything! For dedicating all this time, dedication and patience, as well as creating such a detailed explanation video to teach us. You have no idea how much you helped me improve my organization, in such short amount of time I can see how much I have not been tracking of my own money and my shop's. I shared this with a bunch of friends as well. Thank you again!
I really like this budget. As another comment said, it really is like a work of "art". My only suggestion is that it is very difficult to make any adjustments to suit my own budget needs. There are some features I have been using for a long time now and want to add on. I am trying to go through the tutorial but it is extremely advanced. I'm not a noob, I am familiar with excel, but the formulas in this are so complex and the amount of background knowledge you need to understand it is way over my head. I will try to learn but it would be nice to offer a budget template choice for those who are intermediate excel users so they can make their own adjustments.
For everyone whose spreadsheet went all wonky in the new year, I found the problem: The Budget Planning sheet seems to have been build slightly incorrect. Each header has a month listing (E9 = JAN), (E22 = JAN), etc. Specifically E9 =DATE($E5,1,1) In the new year 2024, I notice all these dates still point to E5, when they should point to S5 (EG S9 =DATE($E5,1,1). To correct this you need to update S9 =DATE($S5,1,1) You will also need to correct T9, U9, V9, etc as well as S22, T22, U22, etc and S40, T40, U40, etc... Notice however, the Total column is already correct. You will also need to correct each future year.
Looking back on the construction of the budget planner, I missed the step where he changed =DATE($E5,1,1) to =DATE(E$5,1,1). This change allows you to copy the formula, and it will auto update the E to S to AG, etc.
I had some problems when adding new categories in the budget planning page. you need to hide the un-needed rows and then update the calculations in the dashboard and calculations settings. If they still don't show save the file and reopen it. I had it work after doing both of those things. @@diogoreis8385
6 hours later and wow. Honestly I've probably learned more about Excel from this RUclips video than my whole life combined! Can't thank you enough honestly. Here's to a better financial future!
@theofficelab thank you so much for this awesome tutorial. It was an amazing journey navigating through this tutorial and following your approach to building this tool. As a suggestion for the next version, please may you consider adding a section for tracking accounts (both debit and credit). As money is often moved from one or more accounts, it would be useful to add that info to the Budget Tracking sheet as well as balance reports on the dashboard. It would also be great if transfers can be made between accounts as well. Thanks again and happy New Year!
Thank you for this excellent job ! I'm used to working with pivot tables and slicers but I have to admit that I'm amazed at how you built this dynamic dashboard. Congrats !!
After working on this project for over 10 hours spanning across several days, I can finally rest easy and put it to use. Thanks for putting out such a wonderful masterpiece that's easy to understand. Your explanation right before you implement some concepts made this easy to follow. Glad I got to learn so much in the process. I appreciate the 6-hour journey and the effort it took to put this together. Looking forward to exploring more of your work. You gained a sub friend. Thank you.
What an amazing video ! Thank you so much for providing this amazing tool ! If I could add one additional feature, I would probably want a new "Savings Dashboard" tab, that would allow you to track cumulative savings over the years. Again thank you so much for this amazing video !
Hi and first of all a massive THANK YOU for posting this amazing content! I'm following along and I'm at 2:48:46. Once I pass the formula to the column it only calculates the amounts for "Total", leaving - to the categories. Here's the formula, and it looks the same: =IF(OR(is_header; is_empty); ""; IF(is_total; IF(selected_period="Total Year"; SUM(Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year)); SUM(Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year) * (MONTH(Tracking[Effective Date])=selected_period))); IF(selected_period="Total Year"; SUM(Tracking[Amount] * (Tracking[Type]=type) * (Tracking[Category]=item) * (YEAR(Tracking[Effective Date])=selected_year)); SUM(Tracking[Amount] * (Tracking[Type]=type) * (Tracking[Category]=item) * (YEAR(Tracking[Effective Date])=selected_year)* (MONTH(Tracking[Effective Date])=selected_period))) ))
Spent my weekend following along and building this out, and it was so much fun! Thank you for sharing your expertise! A couple of things: 1. I don't know if this was already factored in and I just missed it, but I added a formula to the To Be Allocated row that basically checks if there's unallocated money at the end of the previous month and adds it to the balance of the new month. 2. It would be so cool to be able to track what account you're spending from/saving to. Bonus points if you can do that across currencies, but I'm probably asking too much 😅
The point of a zero budget is to make you allocate it to something. If it’s not an expense, it’s savings or investment. I helps force people to make definitive decisions about their money. I don’t say this to say your formula for forward allocation isn’t great, it is; however, people need to know what the benefit of not doing that also is.
May be for the next version it will be a good to have an account list to show you what amount is being spent from what account. All and all, i really liked the video and the result of it. Well done!!!
I also was thinking about that. As now I think how to spend saved money. For example I was saving for big purchase and how to make thins money "spent" in the table now...
I've just wrapped up the spreadsheet after a few days of work. Finally got it done! My aim was to learn more about Excel through your videos; your explanations are super clear and easy to follow. Thanks a lot!
That piece of art is f*****g awesome. I followed every single step and realized how little I knew about excel. Now I am trespassing all the budget tracking I had in my homemade Excel sheet to your template, which looks much cleaner. However, I came across one "problem" and would like to know what would be your strategy, imagine the following situation, there is a month when you don't have any income, or you have more expenses than income and you use money of your "Savings"->"emergency fund". How would you track this information? I'd really appreciate your comments on this. Thank you!
Good question. I think you could create a new category under expenses named "used savings". And in the "budget tracking" table, under "details", you could specify the kind of savings was used (eg: emergency fund, sinking fund, other savings).
Putting excel on my resume as a skill at this point
Bro is the Tripple Kill Kid. That's crazy XD
lol im saying, after the first hour or so, i thought i was doing alot, but when I began the third hour, boi oh boi!!!!
@@anthonylegendre2194i just finished the first hour dont tell me its getting worse 😂😂😂😂
Same lol
Honestly - you should. You'd be AMAZED at the number of people making nearly six figures who have no clue how to use excel. Knowing how to get stuff done in excel is a huge differentiator in my career.
for those who have the problem with the formula for total 36:43 , here is the solution - =SUM(INDIRECT(ADDRESS(income_min_row;COLUMN())&":"&ADDRESS(income_max_row;COLUMN())))
the problem is that after min_row/max_row we need ";" not "'," also in the beggining we need "=sum" if you get value in the cell. Amazing tutorial man, thanks!
Thank you so much for this!😊
I edited it a little =SUM(INDIRECT(ADDRESS(income_min_row,COLUMN())&":"&ADDRESS(income_max_row,COLUMN()))) try this if that does not work.
Thanks a million
@@notsogaby a tonne of thanks to you
37:29 its not working for i tried ";" and "," as well, with comma it was working but as soon as i add indirect function it give "#VALUE"
I can't imagine the amount of time it must have taken to make this entire 6 hour video as soon as it was. Your explanations were so clear and you didn't skip any step. Some people will assume that some things are common knowledge in excel and will skip over it and that will ultimately lose some viewers. This was really an amazing dashboard and incredible video. I learnt so much, not just about excel's capabilities but also being detail-orientated so make sure everything is nice and standardised creates a beautiful end result. You're one of the people the world needs - people who share their knowledge generously. Thank you.
Really appreciate your kind words! That means a lot to me. Thank you 🙂
And yes, the amount of time and effort put into this has been tremendous, no doubt. But seeing people getting value from it and enjoying the learning journey makes it absolutely worth it!
@@theofficelab Could you please let me know how you solve this step 03:06:05. I am using the exact formula=If(is_cat, RANK(tracked, INDIRECT (tracked_range),0), "") i am having #REF! Error
How it should be. Unlike these for the tards only shorts videos
@@naturelove4730 He's not gonna show you how to do it, if he's selling it he would rather you buy it
My thoughts precisely. The man is a god. I tried following along and kept pausing. Unfortunately the screen goes dark when the video is paused. So I decided to opt for plan B. I copied the transcript and pasted it into Word. 48,913 words spread over 140 A4 pages...and that was only from "Budget Plan Setup". I didn't even bother with the introduction.
And no, I'm not going to print it all off.🤣
So far above and beyond any other budget worksheet I have ever seen it doesn't even compare. Even my "I need paper and pencil" wife is interested in using it. Can't thank you enough for this. Well worth the time spent.
Hi Michael ☺
Thank you so much for your kind feedback! And glad to hear it helped convert your wife to using a spreadsheet for your finances.
Spent 2 days, My eyes are now burning but I completed it, thanks for this awesome video
@@vijayparmar5213 I built the budget planner as well, everything works. There a slight differences between the 365 excel and my 2017 version which led to some confusion when he works with formulas that use arrays (you need to use ctrl+Shift+enter instead of just enter) but got that sorted out thanks to google :)
@@vijayparmar5213 on my device are not working
Hi, are you using office 2021 or not?
I am trying to make this up on min and max row formula isn't working could you help me with this
Hey, you share it with me? I'm struggling make this.
This is unbelievable.
I was thinking of buying your template at first, but then I wanted to see how it works so I took the tutorial. Now that after +20 hours I'm finally done, I'm thinking of buying it just for gratitude. Thank you for this, I really appreciate the effort of putting this video together.
Thank you so much 🙂 hearing that you completed the tutorial, enjoyed the process and hopefully learned a thing or two makes my day! 🤗
Did you figure out how to add that tickmark at 50:57 ?
@marvenscantave4023 googeled „ascii checkmark“ and copied&pasted the one that I liked into my formula. Hope that helps.
@@marvenscantave4023 search for tick mark symbol on google and copy + paste form there
Could u send the sheet that u did? XD
Just finished this absolutely incredible tutorial. I'm a university student with absolutely no Excel experience, and yet this guide was straightforward, easy to follow and unbelievably thorough. I'm amazed that this kind of tool has been made readily available on the internet for free. Thank you so much for the time and effort you have put into this
Happy to hear you enjoyed the tutorial, Veronica ☺ Really appreciate your kind feedback!
This must be the definitive excel tutorial hahaha now who can help me get an office key please
BNH Software helped me to see how it is but can I ask why you want it?
Thanks man, and to answer your question, I've never bought one and I want to see what they're like.
That's just curiosity, but it's okay. If you have the money, do it. I think it could be a good decision.
Of course, if I didn't have the money, I probably wouldn't do it, but this time things are different.
I followed this tutorial from A to Z. You, sir, are an absolute genius. The way you know your thing, the time you took to break down every single step, the tone of your voice... Everything was perfect. You could have just shown us your work and put a purchase link down in the description. You took +6hrs of your time to teach us noobs how to build something amazing. I am forever grateful. Thank you so much!
Thank you for your kind feedback 😊 Really happy to hear you enjoyed following the process.
Can you help me please?
Can please share me the editable file ???
@@lingeshwarang1144 u need to purchase it
@@theofficelab =INDIRECT(ADDRESS(income_min_row,COLUMN())&":"&ADDRESS(income_max_row,COLUMN()))
I'm having problem in this phase. after entering this formula, it shows #VALUE error
This is truly an ultimate personal budget tracker. Your generosity and patience to share such a detailed oriented 6 hours videos and valuable knowledge is highly appreciated!
TIPS bellow
it took me so many days (around 10 days on my free time) but I managed to follow and create my own! I cannot believe what I've managed to do (with your guidance OBVIOUSLY!). I confess some of the explanations were a bit too advanced to my excel level but I am very happy with the result nevertheless. Thank you for being so thorough.
If anyone is following, I have a couple of tips: make sure of all your spellings on the formula bar. I had to go back a few times because the result seemed to be initially correct (when the answer was blank) but it would give me errors when I expected to have something returning. Usually it was a typo on the formula itself. Also, create your tracking list within the expenses, for example, in the order of your importance such as "groceries" in the first line, "rent/mortgage" on the second, "hydro" on the third, and so on... I just entered randomly and it makes a difference when populating the doughnut chart. Same for the other ones too (savings and income), top to bottom importance. If I remember anything else I will come back to update this comment.
Thank you!
can i get a copy of your excel
@@kimferandmagistrado8655did you ever get a copy of ?
I want to primarily thank you for this amazing work I call, "a piece of art." I'm an analytics consultant, and there were many functions you explained here that I never knew were possible with excel. I will be using a lot of these moving forward in the production of a lot of my dashboards. Very well made. I went ahead and followed your explanation step by step to create this document for myself and it works perfectly on my mac.
Awesome that you finished on a mac! I am almost complete as well, but I cannot find the "properties follow chart data point for current workbook"[4:14:08] setting in my Excel (v.16.68 macOS). Is there a work around that you found for this? Thanks!
@@BGphotographyBG I also spent some time looking for that checkbox until I realized after moving forward that it doesn't exist for Mac. Excel for Mac already follows chart data properties on a single workbook basis unless you're pulling from other workbooks (purposely linking cells/charts to/from other workbooks). Just keep going with his steps and skip this one 🙂
Thank you for this amazing feedback 😊
@@fadedgames6775 Thanks for the response! Interesting that you got it to work, I have rebuilt my charts a few times and haven't been able to solve it. Anytime I change my period my charts will not hold their color and they reset to the default.
Can you send template me the for free
GUYS FINALLY I SOLVED 30:31 Min Row and Max row function
For Min row ->
=Row(Index(Income,1,1))
Max Row
=ROW(INDEX(Income,COUNTA(Income),1))
Thanks 🙏
can you please send the file my laptop is lagging so much i couldn't open youtube and excel at the same time ?
Many thanks!
Thank youuu
Thanks a lot)
I also highly recommend adding a Reoccuring column in your Budget Tracking sheet in between Details and Balance with a dropdown for Yes or No, that way you can see what subscriptions you pay for or Loan payments every month or year and decide if you still want those subscriptions and what not.
How would you do that?
Damn I'd love something like that
Appena finito questo incredibile tutorial, ho iniziato avendo delle conoscenze di base in Excel. Armato di buona volontà ho provato a comporre questo puzzle gigante, dopo giorni ci sono riuscito anche grazie all'aiuto degli utenti nei commenti dato che ho usato una versione passata di Excel. Se qualche altro italiano ha problemi o con la traduzione o di altro tipo non esitate a commentare e vi darò una mano se riesco. Grazie per il lavoro fatto dal canale è stato davvero stimolante.
Ok, this is seriously the BEST Excel video on personal budgets, period. 👆👍❤👆👍❤👆👍As a 35 year veteran Excel trainer and RUclips channel creator, I am absolutely floored by the incredible quality of virtually everything you've described here. I decided to create my own dashboard because the most popular money management applications have all gone into the cloud and I didn't want my information put at risk. Your gorgeous presentation with exquisitely thorough formulas and functions and elegant design elements makes this comprehensive tutorial a pleasure to watch but more importantly, a pleasure to actually use. Practical, yet so well designed that it really does provide an easy system that any one can use. I'm in awe of your skills. Coming from someone who teaches Excel up to the programming level. I learned so much even though I've used Excel since the very first version. Even your voice is perfectly modulated to make it easy to follow and understand though I did have to speed it up a bit - that's just because most of the techniques I already knew. 😊👏😊👏😊👏
EDIT: After walking through the whole video, I absolutely bought the template as a way to thank you for your work. 100% worth every penny!
Could you help me with this part 52:45 the formula isn't working and I don't know why
praise of the highest honor. OK ill watch it now.
@@okfajvfoanjk you have to manually delete one of the dollar signs pause the video and look at which one is missing
What a great tutorial. I've been using spreadsheets since the '80s and after this, realised that I've just been scratching the surface. The power of tables is incredible. I have created, and am using this tool and have even expanded it to add 'Transfer' as well as expenses and income. Transfer moves money between accounts without being identified as income or expenses. I have also added a fiscal year option for the selected period, which was hard because the selected year and selected period names are referenced almost everywhere. For those wanting to add fiscal year, my advice is to dive in and do it yourself, you will learn so much. My last modification was to remove the savings function. I am retired so everything that comes in, goes out.
Highly recommended.
send me a copy
To everyone who asks to share the excel document:
If you check the description, you will find that ur request is forbidden. Please be careful. You could just make a video on that single category and post a link if u are keen on helping. Just read the description before you start
I have finally completed this beautiful masterpiece. Point to note is excel 2019 on PC generates an error if used as =SUM(FUNC.A * FUNC.B) But will work if only written as =SUMPRODUCT(FUNC.A * FUNC.B). All other formulas work splendidly. Thanks OP
THANKS MAN
I spend almost 2 hours just to figure out why there is the problem with function above, almost went crazy over this since I really want to finish this Budget Planning. Thank you for posting this since it really help me to sold the problem. You are great! Thank you so much! 🙏
@@bodhigayagirl653 It bugged me a long time too all the best.
@@CM7016 Thank you. Can you help me with the tracked columm at 2:39:40? I used SUMPRODUCT too, however, only Income & Saving amount work but the Expenses is all zero. I reviewed many times and still can't resolve it. Thank you very much!
=SUMPRODUCT(Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year))
@@bodhigayagirl653, same problem for me to...
Pretty much the best tutorial I have ever done! Such an amazing end product! Really easy to follow, you have a very calm voice and I genuinely enjoyed following along. I watched this video in 30-45 minute parts and just took it easy. Made a million typo's in the code and had to translate all Excel functions to Dutch.. Now it's done and I can't wait to start budgetting and tracking! Thanks so much!
Dude ! You gave a 6 hour tutorial on how to build this epic budget tracker ! This is unbelievable. Thank you so much. RUclips Excel GOAT moves !!!
Haha thank you 😄 really appreciate it!!
Three days, almost 26 hours invested, and so many headaches... I FINALLY FINISHED!!!!! mWHAahhaHa VICTORY SCREEEEEEECH!!!!!!!! Excellent tutorial. Thank you so much. Coming from somebody that has never messed with spreadsheets, I have to say you are a great instructor. I made several mistakes and spent countless hours back tracking to find the error but I think I finally have a usable budget calculator.. Thanks again.
Listen, I was about 2 hours into the video, and about 4 days into the worksheet.. found an error... didn't know how to fix... bought the sheet. And I'm impressed.
Wow, this gotta be the greatest Excel sheet I’ve ever seen. Can’t imagine how much time and effort you had to put into this and then you just share it with us for free 😮
This was insane! Took me 3 days to process and complete. This is next level tutoring. Thanks for your all your effort!
i would buy it from you.
Does it work on google sheets?
@@Businessfinance1349 it's for sale by the owner. Can get the link in the description of video.
@@avinashjagdeo CAN YOU SEND YOURS SO I CAN GET FOR FREE I AM STUDENT SHORT OF FUND: (
How did you add the "tik mark" at 51:03 ? I'm on windows and i don't know how to do it :(
Absolutely wonderful tutorial, learned a lot along the way and incredibly powerful end result tracker.
Couple of future enhancements that I would love to see in an updated video:
1. Net Worth - Visualizations, Calculations and KPIs added on top of income, savings and expenses. Include assets and liabilities categories.
2. Amortization Schedule - for paying off credit cards line of sight, APR tracking, principal tracking, min payment tracking etc.
3. Portfolio Balance Sheet - track balances for checking, savings, investment and retirement accounts. Associated rates for totals, growth rates, avg growth rates
4. In period selection, enable “Year to Date” calculations
Waiting and hoping to see that video because I need these updatest too, at least the net worth and amortization schedule sections. @theofficelab please let us know if you'll be able to make an upgrade or have something similar to this that can be referenced to or purchased, would love any help!
@@sahanasriram7543 i have a great amortization excel file if you need, lmk.
I have an Amortization excel file that calculates all your requirments if you need. lmk
This video was insane. Was able to build the entire model and include my own added credit card tracker which feeds into the budget dashboard. Couldn't have done it without the expert guidance from the narrator. Good job!
how did you insert the card management? can you perhaps share a template version of your modification
I just finished the tutorial, and I want to profoundly thank you for your hard work and generosity. I've been thinking about building a system for tracking my personal finances for a long time now, but as a complete newbie at Excel, I would never have achieved anything like this.
You didn't just help me with the initial goal of setting up a finance tracker; you showed me a new way of thinking and sparked in me a love for numbers I never knew I could have.
This is truly an art form.
Thank you.
Damn, your words have seriously moved me 🥲 Thank you for such a kind and encouraging feedback. Especially loved the "sparked in me a love for numbers I never knew I could have" part. That's awesome!!
hey mate , i am encountering some issues while following this tutorial. Could you help me a bit as you have finished the template? :)
@@infinitezer3950 you can buy it completed.
Please send
My god, this is a work of art. I am absolutely obsessed with your brain. Thank you so much for this video. I followed it step by step at 0.75x speed and now have the budget spreadsheet that I have been looking for for YEARS!
My only question is; can I lock/protect/freeze the spreadsheet prior to adding in my data, so that the original spreadsheet is saved when I inevitably delete something I shouldn't? It would be amazing if there was a way where I could only edit the actual input value boxes for my different income/expenses/savings amounts and not accidentally delete formulae etc that will affect the use of the whole spreadsheet.
Never did I thought I'd be looking at an over 6h long video about Excel untill I saw this one. The logic in this, the way you think it through, the formulas you have used it's just absolutely mind blowing. I have absolutely loved it and it has taught me a lot. Thank you for a tutorial well put.
Sorry to ask but may you share a copy of it with me
@@narwhalboy1799 I haven't even finished it. But you can download the full version from the author. It's in the description.
Jesus Christ, all of that in a six hour video! You're a trooper! I can't imagine how long it took you, to plan such a huge project.
Thank you for your kind words! 🙂 and yes, developing the template alone took a huge amount of time and many many iterations. Let alone the production of the tutorial 😄 But hearing that it gives people value makes it 100% worth it!
Half way through this video and my mind is blown away. I would not have imagined excel is this much powerful. Thank you for this amazing lesson.
Haha thanks for your kind words 🙂
I am still in the middle of the tutorial but I really want to share some feedback. In first, huge congratulations for the amazing project you created here. You tackle all the complexity through your clear explanations and that (even in 0.75 speed 😅) is truly amazing. As I read in other comments, I've never learned anything that complex in excel but I am continuously thrilled each day to keep going and following a few more minutes until the end. Thank you very much for this class, this is actually "a piece of art". The way you tackle the situation by work around with rows and names references, that is top-notch. Congrats again!!
How did you add the "tik mark" at 51:03 ? I'm on windows and i don't know how to do it :(
@@jhavanna7564 Use this formula: =If(E7=0;"Jan " & UNICHAR(10003);"Jan")
Can you send it to me please?
@@21luke21 many thanks!
This is by far, the most complicated tutorial and spreadsheet that I have ever followed. I don't normally get to the end of a tutorial without something going drastically wrong, but the way that you have displayed and described this tutorial has been astonishing. I have followed it through to the end and everything works. Thank you for such a brilliant tutorial.
Hello, I am stuck at 36:00 , I don’t know what I did wrong, even tried to do it all again. When I entry the =Address, excel gives me an error about the formula…
@@Luis-ne7ii Same here :(
@@isaiahrs change , for ;
@@Luis-ne7ii edited it in excel online and got it to work
you're the best :)
Wow, I can't thank you enough for creating this tutorial! After countless searches, I stumbled upon your video, and it's been a game-changer in helping me build my interactive personal budget. Your explanations were clear, step-by-step guidance was spot-on, and the result is a budget that not only makes sense but is also tailored to my needs. Your effort in putting this together is truly appreciated, and I'm now feeling more confident and in control of my finances. Kudos to you, and please keep sharing your wisdom! Cheers!
First of all I want to join the people praising your work. This tutorial as well as the downloadable version of the Worksheet are outstanding in quality and I can't wait to see more from you!
I noticed just one small oversight in the Worksheet (in case you don't mention it later, I'm not yet done following the tutorial); when calculating the comb_rank and after that the comb_rank_unique, I ran into a problem when using more than 9 categories of expenses. since the comb_rank just writes the numbers in sequence it turns a 1 digit and a 2 digit number into a 3 digit number, which of course gets ranked way lower than it should in the normalized rank.
For example I have a tracked_rank 1 and budget_rank 13 entry. it is the highest tracked amount and should be on the top of the list, but the comb_rank is 113 so it ranks lower than an entry with tracked rank 8, budget_rank 6 and comb_rank 86.
Do you have a solution to this? I will also try to find one and update my comment if I do.
EDIT:
I do have a solution, but it's not 100% clean. It doesn't completely fix the issue but it lets you have 9999 categories instead of 9 before it breaks.
formula: =IF(is_cat; NUMBERVALUE(tracked_rank & (budget_rank/1000)); "")
in the column comb_rank I just divide the budget rank by 1000 before appending it to the tracked rank. this way in the example above the items would have a comb_rank of 10.013 and 80.006 and everything should be sorted correctly.
Ths is not a perfect solution, because it also breaks if you have more than 9999 categories, but in practice you should ofc never have that many (and if you do just divide by 10000 for a total of 99999 categories). So hopefully this helps somebody, cheers.
so i cant have more than 9 expenses without having to do some maths? can you explain this for like a grade 2 level? I'm so confused
@user-lt8uv1lk8j Basically just use my formula instead of his if you want more than 9 categories, no further math needed. :)
That Was the tl:dr, however if you want to understand why this works, let me try to explain:
So we have 2 numbers that we want to sort or rank. If those numbers are different that's easy to do, we rank them (in this case) from lowest to highest, easy enough. But what if we have the same number twice, which one should be higher? To solve this we introduce a secondary number. We call the first number tracked_rank and the second number budget_rank. So for example if you have a tracked rank of 3 and a budget_rank of 5 and I also have a tracked_rank of 3 but a budget_rank of 6, you should be higher rated, makes sense?
We realize this with a function. the Funktion takes 2 numbers and combines them. Not by adding or Multiplikation, but simply by writing them next to each other. So a 3 and a 5 become 35. My 3 and 6 become 36 and now Excel can rank them because 35 is lower than 36. Makes sense so far?
This all works well until you have 2 digit numbers (because you have for example 13 expences and 13 has 2 digits). Now this screws the whole System because imagine you now have the rank 1 but a budget_rank of 13 and I have rank 3 with a Budget rank of 4. You should be rated higher because your rank is lower, the budget_rank shouldn't matter at all here, do you agree? But the Formula is dumb and just does it's thing, neaning you get a 113 (for 1 and 13) and I get the 34 (for 3 and 4). So now I will be rated higher because 34 is lower than 113. This is the Problem we have. I hope you could follow so far, if not feel free to ask any questions!
So how do we solve this issue? My solution Was to introduce floating point numbers (e.g. 0.04) since they work differently in this context. So the only thing I did is divide the budget_rank by some large number, in this case 1000. And that's all my function does differently. Why does that work though? Imagine the example from before, where you have the numbers 1 and 13 and I have 3 and 4. Remember, we expect Excel to rank you higher because 1 is smaller than 3. In my Formula the budget_rank is now first divided by 1000 so your numbers become 1 and 0.013 and my numbers become 3 and 0.004. Combine them like before and now your number is 10.013 and my number is 30.004. See how that works now? Your number is again smaller and will therefore be ranked higher as it should be and the budget_rank is just affecting your number after the decimal point. Note that this still works to solve the original Problem as well. Imagine for example that you have the numbers 3 and 4 and I have 3 and 5. The first number is identical as before so now you should be ranked higher because of your budget_rank again. With my Formula you now get the Combined number of 30.004 and I get 30.005, so you do get ranked higher as you should. Can you see why we got These numbers this time?
And that's everything. I hope the explanation makes sense for you, even if it's a bit long. I also want to point out that you don't have to do math at All here, Excel does everything for you. Math is only needed to understand the Problem and the solution if you are interested in that. Feel free to ask if you have any questions and good luck with the Excel project :)
Not only the dashboard is top quality, but also your way of explaining things and how you presented this guide as a straight 6h developement in the cleanest way ever is astonishing.
Thank you for your amazing work and time. I had an immense pleasure following this guide.
Wow, I can't thank you enough for creating this tutorial! 🌟 After countless searches, I stumbled upon your video, and it's been a game-changer in helping me build my interactive personal budget. Your explanations were clear, step-by-step guidance was spot-on, and the result is a budget that not only makes sense but is also tailored to my needs. Your effort in putting this together is truly appreciated, and I'm now feeling more confident and in control of my finances. Kudos to you, and please keep sharing your wisdom! Cheers!
Finally completed using Excel 2019 version. Took a few days, typo errors, and some formulas had to be corrected for this version. Totally worth doing yourself, to fully understand the how powerful this spreadsheet is and excel itself! Like to send shout outs to everyone in the comments that posted formulas or keyboard commands for excel versions 2019! Time to Execute this powerhouse Template......
Glad to hear that 🙌 would you mind sharing a summary of the adjustments you felt were necessary for the Excel 2019 standalone version?
@@theofficelab 1 of 3 adjustments made.
=IF(OR(is_header, is_empty), "",
IF(is_total,
IF(selected_period="Total Year", SUMPRODUCT( Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year) ), SUMPRODUCT( Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year) * (MONTH(Tracking[Effective Date])=selected_period) )),
IF(selected_period="Total Year", SUMPRODUCT( Tracking[Amount] * (Tracking[Type]=type) * (Tracking[Category]=item) * (YEAR(Tracking[Effective Date])=selected_year) ), SUMPRODUCT( Tracking[Amount] * (Tracking[Type]=type) * (Tracking[Category]=item) * (YEAR(Tracking[Effective Date])=selected_year) * (MONTH(Tracking[Effective Date])=selected_period) ))))
@@theofficelab 2 of 3 adjustments made
@30:15
When entering the formula =min(row(income)) it is not taking automatically but getting figure 10
And in next row when putting formula= max(row(income)) it is also showing figure 10. Solution to this error was given by @joshuabolen6617
"Type in the equations again separately under the table as seen at 30:59 and 'CTRL+Shift+ENTER' instead of just 'ENTER' "
@@theofficelab 3 of 3 was a keyboard command for the arrows in Excel version 2019
Arrow Symbol Shortcut
↑ ALT + 24
↓ ALT + 25
→ ALT + 26
← ALT + 27
Tutorial is amazing! Thank you so much for making this. If you are like me and have a lot of expense categories (10 or more) you may notice that the combined rank gets thrown off a bit by having some numbers with multiple place values and then throws off the sorting. A simple fix for this I found was to add a +10 to the end of the formula in the "tracked_rank" and "budget_rank" columns. This allows for up to 89 categories in each of the different types. If you were wanting to use this for a business with even more categories you could simply add a larger number (example 10000) for a significantly increased amount of categories to be supported. Hopefully this helps someone!
Updated tracked_rank: =IF(is_cat,RANK(tracked,INDIRECT(tracked_range),0)+10,"")
Updated budget_rank:=IF(is_cat,RANK(budget,INDIRECT(budget_range),0)+10,"")
Really helpful thanks !
Hello, this is definitely going to be useful for me. Problem is I don't know where to place these updated versions. Could you be so kind to indicate the time in the video? Thanks so much for your help.
Found it. Thanks again
Thanks this was exactly what I was looking for! This comment should be pinned
I tried this method, but the result is still "#REF!". Actually I also changed SUM into SUMPRODUCT to fill the 'tracked' column. Is it because of that? please advise... thankyouu
Amazing!
I'm an advanced Excel user, but this is the sort of the video that makes me uncomfortable.
The whole video is fantastic, but it's even better to absorb your way of thinking, getting ideas of manipulations or features usage I didn't knew.
Super high quality. I will watch it fully later.
Thank you so much for your kind feedback , Matheus 😊 Really appreciate it!
@@theofficelab Can you please help with this issue: I have input the formula at 2.38.01 and it is pulling the expenses and savings from the tracker correctly. But for some reason no matter what date I put in for any type of income, it will not pull through. I have no clue how to fix. A response would be greatly appreciated.
Matheus, As you said you are an advanced Excel user. I am using Libre office. can this be made to work with that. I have seen alot of this sort of thing. But not like this. I have gotten to the 25 Min part . thanks
@@collmackay79 same problem with me but mine is pulling income and savings but not expenses did you found solution to this problem please let me know
@@mrlajawab8654 did you end up finding a solution ? :)
2:39:34, spent about an hour trying to figure out why it wasn't showing me the tracked values for the Income type. Was about to give up until I went back to the Budget Planning sheet and clicked on the Income header and noticed that Income had a trailing space... never thought I would debug something like that before but I'm learning a lot so far. All I'm gonna say for anyone who goes through this awesome tutorial is to make sure there aren't any leading or trailing spaces
having the same problem myself, can't seem to solve it. Under the tracked values all is showing for me is " - ". Any suggestion or help would be appreciated.
@@jackdoherty02 Yeah troubleshooting can be long but try these out:
1) Go back to the Budget Planning sheet and see if there is anything weird going on with the spellings or trailing spaces for the headers("Income, "Expenses" and "Savings").
2) In the Budget Tracking sheet see if all the entries and their respective year matches with the year and period selected in the Budget Dashboard sheet.
3) In an empty cell, try breaking the formula down into parts and try to see if they work as intended.
Hopefully it works out
@@ZZZ-lc1fu Selected year & period wasn't selected as "current". Appreciate your help.🙏
Hi, I stuck at this part too. The YEAR(Tracking[Effective Date]) returns #VALUE! and I don't understand why, please help/\
Hi sorry just saw this. Hmm there seems to be something not formatted properly in the Effective Date column in your budget tracking sheet. Try going back to the video when he was creating formatting that column and see if there was a step that you might have missed. The part of the function you showed basically extracts the year from thar column and compares it with the selected year. I still a novice at excel but hopefully that helps a bit.
I thought I was pretty good at Excel before this. I'm learning so much from this tutorial and I'm not even half way through! Thank you so much for putting this out!
My pleasure 🙂glad to hear you can learn a thing or two from the tutorial!
it took me almost 10 hours 41 minutes to complete the whole ultimate personal budget in excel and now im able to record my personal finance more efficiently than pervious. Thank you for make this wonderful tutorial i appreciate the work that you put to make this tutorial.
My pleasure 🙂 Thanks for your kind words, really appreciate it!
@@theofficelabHi, could you please help. I'm having issues with this formula "INDIRECT(ADDRESS(income_min_row,COLUMN())&":"& ADDRESS(income_max_row,COLUMN())).
NOT WORKING PROPERLY.
#VALUE error accour
Hi, could you please help. I'm having issues with this formula "INDIRECT(ADDRESS(income_min_row,COLUMN())&":"& ADDRESS(income_max_row,COLUMN())).
NOT WORKING PROPERLY.
#VALUE error accour
@@aetemplate5245 can you specify me in which area of time in the tutorial you faced this issue
for example 2:34:30
facing the same issue.@@aetemplate5245
After a few days of work on this I finally made it. Awesome tutorial, still working perfectly fine. For those who encounter any error following this tutorial my best advice is go back a few secs and do EXACTLY what this tutorial says, it is completely doable without any prior excel knowledge and any kind of research at all, just follow step by step and make sure you are doing exactly what the video says, you don't need any external help, all information you need is in the video, if you are having an error message just make sure you did it right, after 3 hrs is easy to miss a comma or a ) to close a statement. Thanks for this tutorial and template.
Thanks for your kind and supportive feedback! 🙂 Really appreciate it
bro give me link to download please
I'm done! Took way longer than it should have but I am done! Thank you so much for taking the time to make this. I have learned so much. For anyone still working on this, DO NOT GIVE UP! YOU CAN DEFINITELY MAKE THIS.
Hello, could you check whether entering multiple data (e.g. three expense items) on the same date/day gives correct results in the balance on the Budget Tracking sheet. It does not work in my BTracking sheet.Thanks
@@peterasschert2486I just checked and mine isn't right either
Can you send me the template please?
Wow! I can't say enough WOWs! I have built dashboards for the last two years, and admittedly, this design tops everything I have done or seen! I have been following your templates for a few years and can see remarkable advances, not only in the design but in the overall presentation which is clear and concise. The price to get a template and a demo template is insanely affordable. I saved hours in time of effort and in thousands of dollars. KUDOS!
i've had a personal finance spreadsheet for a few years now while i was a student, but since i'm finally earning a proper income i wanted to "up my finance game" so to speak and this video was perfect! you explained things so clearly that i was able to incorporate exactly what i needed into my own dashboard, and i learned a bunch more excel tips along the way to make my existing spreadsheet more functional/efficient. thank you, thank you, thank you; and well done, you did an amazing job!
I started this to learn how to use Excel, I have spent a couple hours every day for about two weeks, and then I stopped. I'll get back to it today because it's amazing, I've learned so much, and the comments have been also helpful! Thank you for this amazing tutorial.
My knowledge of Excel is already pretty advanced and I am self-taught, and this is exactly the next level of things that I have been looking at doing and learning. A FANTASTIC video that is very well explained. Followed it from start to finish and now I have an amazing budget planner! Definitely looking to do another one of your clips, might look at your Gantt chart one next. Fantastic!
Please send
I'd say I'm intermediate in using Excel but after watching this full tutorial I learned so many tips to help me save time in any Excel work that I do. Thank you for not only building and sharing this, but also teaching us how to make a visually appealing workbook. It took me 3 days. I wasted time choosing custom colors which I wish could be integrated in the theme colors, but Excel doesn't have this functionality on Mac. The only 2 things I did differently was order savings before expenses rather than after, as it's important for me to allocate income into savings before expenses (I wanted this to reflect visually), and I also divided 'expenses' category into 2 groups: 'Expenses' or necessary expenses (e.g. housing, utilities, food) and 'Discretionary' (e.g. shopping, streaming) so I had 4 categories in total and I made sure to reflect that in the formulas taught. Now time to get to work!
did you have to watch all 6 hours
@@jarenventures yes
@@cp1007s my adhd couldn't :/
@@jarenventures you could buy the template but it’s pricey imo. There are many simple budget templates out there that don’t cost a dime :)
@@cp1007s but are any of the free ones as good as this in your opinion?
This is awesome. One tip on 51:35 to avoid typing each month manually: =IF(E7=0,TEXT(E9,"mmm")&"✓",TEXT(E9,"mmm"))
I appreciate you helping me to insert a tick-mark in IF CONDITION.
Hi, I just copied and pasted it from one of the comments below: ✓@@micraajabdimohamud6332
I was also searching up how to enter the checkmark for the longest time and a lot just gave me √ which is not a checkmark. It's the square root symbol.
I finished it last June but I still paid for the excel budget file to thank him for his awesome work.
It took me +- 10 days to finish it (because of work and life).
I can't imagine how long it took him to engineer it and film it.
Cheers, man!
Such a wonderful template this is
Could you help me one forumla? At 3:04:27, the tracked_range column, it results in a #REF error. And also says 'too few arguments for this function'. I'm stuck here, and unable to move forward, could you please help?
why i don't got the same result with the tutorial? I enter the min or max i still got 10 for the result
@@raudatuljannah6916 I am also having this issue - I'm betting there was some kind of update that changed how to do this formula. I sure can't figure it out!
@@deidrah3074 the "expenses_min_row" etc. formulas? Those worked for me exactly like the tutorial. I'm using Microsoft 365 (Version 2306 Build 16.0.16529.20100)
You need Microsoft 365 for the function to work. This is the formula i used to return "12", or the value of the bottom table. Note, this will return the row number of the last non-blank cell. So if the category is not in the table, leave the cell empty. Sorry if my explanation is confusing . Hope this helps!
= MAX(ROW('Budget Planning'!$C$10:$C$19)*('Budget Planning'!$C$10:$C$19""))
you can change the data range when you get to the expenses and savings max row formula.
The best video for personal budgeting I have seen so far. Even learning many new things in Excel. Thank you so much!!! Even as a Finance major, I could have never created this clever worksheet.
This is the first video of yours I’ve watched since I’ve been looking to up my budget template game! I just want to say, I went and actually built what you did here and it works amazingly! So functional and so many interesting ideas to do things I’ve never thought of doing before- thank you so much for posting such a detailed and thorough tutorial, it was super easy to follow and you explained everything perfectly
Could you share it with me?
@@essentials9302 make yours. follow the video steps
Truly mind-blowing work you've done, surprising in every way. It's the first time I've been able to follow a tutorial and build an Excel spreadsheet from scratch at this level. Really, congratulations on the incredible work you've done.
Thank you for sharing this in depth tutorial that reflects the effort you have put into this project - well done!
To your closing statement on what could be next:
1) Import transactions from your bank app,
2) format to suit the budget tracking tab,
3) assign the entire process to a button using a macro.
This would make the monthly tracking an almost instant activity and I feel would compliment this spreadsheet nicely.
I submitted the same requests to them; however, include the import step-by-step reconciliation and reports from XERO and Quicken. This is the next logical test, "according to Excel." 😁
If I'm not mistaken excel no longer supports linking your bank. There is a third party app that microsoft reccs you use called tiller but I'm not sold on connecting my bank to an app I've never heard of.
Can you send me the template please?
I've followed the tutorial and bought the template, populated it and am using it now. I thought I was fairly proficient in Excel but some of the template workings are a real eye-opener - the design principles followed are very illuminating. Your explanations were clear and concise. Thanks very much!
Since you clearly have the newest version of Excel, can you explain to me why in the video at 33:27 I do not have the same edit formatting rule popup as this video? I am not getting the same option to select a rule type but rather just the bottom half of the pop up screen he shows of just edit rule description.
Not sure what version he's using but I've Excel 2016 and the conditional formatting popup is the same as in the video.
Welcome back! Congratulations for this insane project, please continue to produce real projects like these, they are amazing!
Happy to be back 😊 thank you so much for your kind feedback and support. I will definitely continue with these kind of projects, no doubt!
Agreed! Just finished building and it's great!
Hello, can you upload it to you Google Sheets on Google Drive. And test if it works? So i can buy it right away :D!
@@theofficelab Heeelp! For some reason the tracked income figure isn’t reflected when I get to 2:40:24
@@mackinyoungin same error :(
When 3 hours in you expanded the selection and was like "we will need space to get this task done" I audibly gasped. Here's to hoping I can get this done! Really am enjoying the verifiable onslaught of excel knowledge; as someone who isn't professionally trained, I'm enjoying learning about these new functions and what excel can do!
After a full week of picking away at this after work, I finally finished! Thank you so much for the in depth tutorial and great template, I learned a lot about excel I didn’t know along the way too! No that I’ve invested time in building it, I feel motivated to actually make a budget for the first time in my life.
One thing I would like to add to this template is the ability to add my loans (car, mortgage, credit card etc). and when I allocate an “expense” to them as a defined category, I can watch the value of the load drop on the dashboard. Ideally it would calculate how long it takes for the loan to be paid off if I add extra payments too.
I will investigate this myself but would love to see your take on it!
Thanks again!
I had the exact same question, however I have not done any research on it yet (about to start the tutorial myself). Were you able to find a solution to this idea?
Check mark symbol ✓
Arrow mark symbol ➜
Copy and paste if needed.
This should be pinned. Had to scroll quite a bit to find!
Omg, that worked!
Thanks man
Thank you so much! Sadly the check mark symbol doesn't work for me - I keep getting "FALSE" error message with it. Have you tried adding CHAR(252) somehow? I read that it should work - again it doesn't work for me - shows as 'ü' or the text 'CHAR(252)'
The check mark just looks ugly on my end, so I shall keep the +
Must be cause my Excel is older.
Made it in one session, had an absolute blast. Amazingly detailed explanation. Love you people.
Happy to hear that ☺ Thanks for your kind and appreciative words!
@@theofficelab How do I add the tick ✔ ?
How are the titles on every sheet done?
Hello Sir, I just want to say, your sheet is AMAZING! I'm not even exaggerating. I'm truly thankful for the time, effort, patience and consideration(beginners) you put into this masterpiece. I have been looking to many channels and haven't found anything like this. I have learned a lot and will hopefully use this along the way. I hope to see more of your contents. Thank you!
This is honestly one of the best tutorials I have seen, very well thought-out, explained and simply beautiful output. 10/10 recommended for anyone who wants to practice their Excel skills and also create a tool to master their personal finances.
Thanks for your positive feedback, Deloris 🙂 I really appreciate it!
Hey! One question! How about a follow up to add a « Year to date » period, allowing for comparing actual income, expense & savings vs budget up to a certain given month. When choosing total year - it takes the budget of the full year into account. Comparing automatically the spent e.g. from Jan to Mar vs budget allocated from Jan to Mar would be exquisite 🙂
goated video. three days later, its finally done, and I have never been so excited about using excel/creating a calculator,etc. Excellent job done, very much so appreciated
Happy to hear that, Eric! Thanks for your kind words ☺
This is by far the best step by step Excel tutorial that I have ever seen. I have learned so much about excel just working through this tutorial. Thank you for the time and effort going into this.
Thank you so much for this brilliant tutorial and all your efforts to create this masterpiece! If I may I would suggest two feature extensions:
1) Integration of 1 to n subcategories per main category and related evaluation options
2) Tracking of fixed vs. variable expenses
Again, thank you so much, I am looking forward for upcoming tutorials 🙂
i completely agree, a fantastic tutorial & tool!! i added in another row of subcategories in the tracker using the same method, but taking each subcategory option from a separate hidden table in another sheet - has worked a charm for me!
@@benbarr4001 I am trying to implement another row of subcategories too! How did you setup the separate hidden table so that you can select them in the dropdown for the tracker? Edit: trying to create another column, not row, for the tracker
@@benbarr4001 can u send me your version? please?
@@benbarr4001 also interested in adding subcategories. can u send it, please?
Hi everyone. This is indeed an amazing tutorial. Thank you so much to "The Office Lab"!
Same question about the subcategories:
I would also like to do this. For example, "Housing" would have subcategories like "electricity", "rent" etc...
To do so, is it possible to just add more expenses table to the budget planning sheet? Therefore, there would be the Income table, then 5 or 6 Expenses tables, and then the savings table?
And if I do this, will it be an issue for the rest of the spreadsheet creation, when creating the dashboard etc...
In advance, thank you for your help,
Cheers.
Awesome video!! In case someone else has the same problem: I had trouble with the conditional formatting of the tracked income amounts at 1:20:45 but solved it by replacing both ' with ". I'm guessing it's the same category of problems as , vs ; separating a formula's arguments.
It would be great to see a second part of this video that introduces a "Total Savings" section, where all savings and investments recorded over the tracked years are summed up in a single graph. This would give a clear overview of your entire financial growth in one place. Another useful addition could be an "Initial Budget" feature, which tracks the amount of money you had before starting the spreadsheet. Overall, fantastic work!
Wish I came across this sooner. I didn't know most of these functions and dynamic formatting existed. I work in financial accounting and will be updating my recons with this. Thank you so much for your beautiful work!
It's me again. Been a while. I finally finished This Amazing Personal Budget today. It was challenging but I managed to get it done. I want to thank you (The Office Lab) for this amazing tutorial. I learned a ton while I was doing my copy following the video.
It was absolutelly one of the greatest experiences I had on Excel so far. Thank you!!!
Thank you 🙂 really appreciate your kind feedback!
@@marvenscantave4023 At what time on the video so I can take a look.
50:57
@@theofficelab fuck you I didn't give you a feedback I asked a question.
@@micraajabdimohamud6332 Yes there
This is right now my top 1 video on RUclips. Thank you so much for a very detailed tutorial and comprehensive, effective Excel template :)
I have not finished yet. Still 4h left but I've learned a lot of new things in excel (and people always said I was an Excel master!). Thank you for sharing this content!
Thanks for your kind feedback 🙂
I will not tell you how long it took me to do this...worth every minute, rewind, typo check, rewind, typo check and win! I, too, learned "unsagbar viel" (unsayable much...)
@@theofficelab - I send impressed and heartfelt thanks from Berlin to Frankfurt!
@@Locomaid Das freut mich, danke dir 🙂 Grüße nach Berlin!
This is amazing, I spent the last two days creating it from this video and it's really genius. Particularly how you found a way to rank the items to obtain the new order from largest to smallest in the dashboard. My mind is blown with how good it was.
There is one part that seems a little tricky at the moment after entering all of my values. If I allocate amounts each month to savings for a big purchase then how do I reflect that amount when I make the big purchase. For example each month saving to a holiday pool. Then a few months later that holiday expense may be more than a months income. When budgeted for In the budget planning tab it shows a negative amount left over for that month. That doesn't work well with the charts etc as the budget bars will start pulling down out of the defined area. The way I have made a work around is to define an income category called "transfer in from savings". Then when the savings are used to make the big purchase I have income (from liquidating the savings) and the expense opposite it to pay for the holiday. It works ok but I was wondering if there is a way this is supposed to be reflected that I'm missing.
Thanks for putting the time into this, it's the one of the best things I've found on here.
I have that problem aswell.
I was wondering the same thing. Your solution seems like the most logical one for me. Why does it only work ok? Is there a problem with assigning negative budget to the holiday daving category?
@@ScrantonStrangler19 You could do that, I assume you could set the axis to be always have a min of 0 in the chart settings as well
This tutorial is all I was searching for. It's just amazing, thank you for putting so much effort and explaining it step by step. Everything works fine and smooth. I even added two more columns in the Budget Tracking tab, one for "Methods of payments" and "Fixed and Variable Expenses" and it all worked fine. The only problem is to make the charts for these columns change with the selected period. I'm still trying to fully understand those formulas, but once I get it, it will be the Personal Budget I was wanted.
Also wanted to separate fixed from variable expenses so I added it as a separate category - instead of the 3 he shows in the video I have 4. :)
It's quite easy to do if you just follow along as everything is nicely named and explained.
This personal budget Excel is a game-changer! As someone who is a number cruncher, I really appreciate the level of detail and the intuitive layout. It's clear that a lot of thought went into making this tool both comprehensive and user-friendly. Thanks for sharing such a valuable resource!
For those getting erroneous returns such as "#VALUE" in some functions, and are like me using an older version of Excel, I have a solution.
Later versions have a feature called "dynamic array" which this guide is using to save a bit of work in entering formulas which use arrays. But as older versions dont have this feature, we need to manually tell excel in the formula that it is dealing with an array.
Fortunately this is quite simple, and there are 2 ways:
1) Surround the formula in { }
2) Holding ctrl + shift when pressing enter when initially entering the formula does this automatically
You'll need to do this in any of the formulas where the uploader mentions that arrays are in use
Thank you so much
You are a life saver 👏 Thanks!
OMG I love you, i was really stuck at one point and couldnt find a solution in the internet because i didnt really know what to search for... But your solution was perfect... Thank you so much
Thanks buddy!
Hey, that not working for me can you please check im at stuck 2:36:26 where he is taking reference of Amount column in tracking table
Finally completed the template. Wonderful experience and the minute details put into the template is so cool and awesome. Thanks a ton for creating and making a tutorial for this template.
can you please send the file ? 🙏
my laptop is lagging so much i couldn't open youtube and excel at the same time !
i hope you see the comment 🙏
This tutorial and the excel file itself are simply amazing! Lot of new functions and tips about excel that I never even imagined, I found it very interesting! Finally I found a solution for budget tracking! There is only a single point that would like to evaluate if is worth to implement: like some incomes are often "shifted" to next month since they are late, it would be useful to have similar behaviour for special type of expenses. For example when I make a payment with a credit card, it would be useful to track it on the same day of the purchase, but the effective refund will happen on a fixed date of the next month. Probably it requires the creation of a specific type of expenses or a label. However, thanks again for this magic tutorial!
Your uses of binary and boolean logic is beautiful you have a deep and profound knowledge in your feild
Absolutely genius work, really thanks for sharing!. I am facing an issue with tracking of income from the budget tracking to the budget dashboard in 2:39:42. From all types (income, expenses and savings) in the budget tracking, the calculation of tracking in budget dashboard considers only for expenses and savings and gives no value tracking for the income categories. Does any knows how to solve this?, Thanks
This is the first video of yours I’ve watched as I have been looking for a more dynamic personal budget template. I have followed your entire video (obviously it took me close to a month to accomplish 😅) I just want to say, I followed the video closely and actually built it on a mac what you did here and it works amazingly! This tutorial video of yours is a masterpiece and a meditation in itself. I got to learn so many formula's and so many new areas of microsoft excel tool and I had never imagined before being it such an elaborative tool ! The way you have explained the concepts, the workflow and logic is seamless and awesome and It's something hard to think of for me being a beginner. Thank you so much for posting such a detailed and thorough tutorial, it was super easy to follow and you explained everything perfectly
Thank you 🙂I really appreciate your kind and comprehensive feedback. It's my absolute pleasure and makes me super happy to hear you enjoyed the tutorial.
@@theofficelab I'm also doing it on a mac (your tutorial is amazing!) but have hit a snag because I can't find the "properties follow chart data point for current workbook" checkbox in the MacOS excel options. Any suggestions on how to tackle this issue?
@@kellycollege Same issue. I have the most updated version of Excel on Mac, and there is no option to uncheck the box, "Properties follow Chart Data point for current workbook". I've seen several comments from Mac users on here that say the budget works perfectly, so maybe it isn't a critical step? No idea.
This took me two and a half days to complete, but my goodness is it worth it! Fantastic explanations throughout helped to improve my understanding of Excel's functionality too. Thank you so much!
Need support
can you share it with me aswell please
Hey I need help with 2:38:43 my income is showing - instead of the amount.
@@blazemamb9919 Have you found the answer?
This is absolutely genius. Because watching how much work goes into making this template makes PAYING for it on his website ABSOLUTELY WORTH IT!
I would like to thank you so much for everything!
For dedicating all this time, dedication and patience, as well as creating such a detailed explanation video to teach us. You have no idea how much you helped me improve my organization, in such short amount of time I can see how much I have not been tracking of my own money and my shop's. I shared this with a bunch of friends as well. Thank you again!
How did you add the "tik mark" at 51:03 ? I'm on windows and i don't know how to do it :(
@@jhavanna7564 you can add it with its unicode like this: If(E7 = 0, "Jan "&UNICHAR(10004), "Jan")
I really like this budget. As another comment said, it really is like a work of "art". My only suggestion is that it is very difficult to make any adjustments to suit my own budget needs. There are some features I have been using for a long time now and want to add on. I am trying to go through the tutorial but it is extremely advanced. I'm not a noob, I am familiar with excel, but the formulas in this are so complex and the amount of background knowledge you need to understand it is way over my head. I will try to learn but it would be nice to offer a budget template choice for those who are intermediate excel users so they can make their own adjustments.
You can do it. Just try ND nothing is hard
@@mikeseal3053 I do appreciate the words, but I have to ask. And I'm sorry for being that guy, but did you just abbreviate the word 'and'?
For everyone whose spreadsheet went all wonky in the new year, I found the problem:
The Budget Planning sheet seems to have been build slightly incorrect. Each header has a month listing (E9 = JAN), (E22 = JAN), etc. Specifically E9 =DATE($E5,1,1)
In the new year 2024, I notice all these dates still point to E5, when they should point to S5 (EG S9 =DATE($E5,1,1). To correct this you need to update S9 =DATE($S5,1,1)
You will also need to correct T9, U9, V9, etc as well as S22, T22, U22, etc and S40, T40, U40, etc...
Notice however, the Total column is already correct.
You will also need to correct each future year.
Looking back on the construction of the budget planner, I missed the step where he changed =DATE($E5,1,1) to =DATE(E$5,1,1). This change allows you to copy the formula, and it will auto update the E to S to AG, etc.
Thanks. Now it's ok. Btw I added another category of Savings but it doesn't appear on the dashboard, do you now why?
I had some problems when adding new categories in the budget planning page. you need to hide the un-needed rows and then update the calculations in the dashboard and calculations settings. If they still don't show save the file and reopen it. I had it work after doing both of those things. @@diogoreis8385
6 hours later and wow. Honestly I've probably learned more about Excel from this RUclips video than my whole life combined! Can't thank you enough honestly. Here's to a better financial future!
@theofficelab thank you so much for this awesome tutorial. It was an amazing journey navigating through this tutorial and following your approach to building this tool.
As a suggestion for the next version, please may you consider adding a section for tracking accounts (both debit and credit). As money is often moved from one or more accounts, it would be useful to add that info to the Budget Tracking sheet as well as balance reports on the dashboard. It would also be great if transfers can be made between accounts as well.
Thanks again and happy New Year!
This is honestly sensational and so easy to use and follow along, half way through and cant wait until I can use this constantly
Thank you for this excellent job ! I'm used to working with pivot tables and slicers but I have to admit that I'm amazed at how you built this dynamic dashboard. Congrats !!
After working on this project for over 10 hours spanning across several days, I can finally rest easy and put it to use. Thanks for putting out such a wonderful masterpiece that's easy to understand. Your explanation right before you implement some concepts made this easy to follow. Glad I got to learn so much in the process. I appreciate the 6-hour journey and the effort it took to put this together. Looking forward to exploring more of your work. You gained a sub friend. Thank you.
Send it to me please
What an amazing video ! Thank you so much for providing this amazing tool ! If I could add one additional feature, I would probably want a new "Savings Dashboard" tab, that would allow you to track cumulative savings over the years. Again thank you so much for this amazing video !
Hi and first of all a massive THANK YOU for posting this amazing content! I'm following along and I'm at 2:48:46. Once I pass the formula to the column it only calculates the amounts for "Total", leaving - to the categories. Here's the formula, and it looks the same:
=IF(OR(is_header; is_empty); "";
IF(is_total;
IF(selected_period="Total Year";
SUM(Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year));
SUM(Tracking[Amount] * (Tracking[Type]=type) * (YEAR(Tracking[Effective Date])=selected_year) * (MONTH(Tracking[Effective Date])=selected_period)));
IF(selected_period="Total Year";
SUM(Tracking[Amount] * (Tracking[Type]=type) * (Tracking[Category]=item) * (YEAR(Tracking[Effective Date])=selected_year));
SUM(Tracking[Amount] * (Tracking[Type]=type) * (Tracking[Category]=item) * (YEAR(Tracking[Effective Date])=selected_year)* (MONTH(Tracking[Effective Date])=selected_period)))
))
Spent my weekend following along and building this out, and it was so much fun! Thank you for sharing your expertise!
A couple of things:
1. I don't know if this was already factored in and I just missed it, but I added a formula to the To Be Allocated row that basically checks if there's unallocated money at the end of the previous month and adds it to the balance of the new month.
2. It would be so cool to be able to track what account you're spending from/saving to. Bonus points if you can do that across currencies, but I'm probably asking too much 😅
Can you please give me?
Yes please , can u share it with us noobs
The point of a zero budget is to make you allocate it to something. If it’s not an expense, it’s savings or investment. I helps force people to make definitive decisions about their money. I don’t say this to say your formula for forward allocation isn’t great, it is; however, people need to know what the benefit of not doing that also is.
Yes the 1st point is amazing could you share the formula& where you've add it?
May be for the next version it will be a good to have an account list to show you what amount is being spent from what account. All and all, i really liked the video and the result of it. Well done!!!
I also was thinking about that. As now I think how to spend saved money. For example I was saving for big purchase and how to make thins money "spent" in the table now...
I've just wrapped up the spreadsheet after a few days of work. Finally got it done! My aim was to learn more about Excel through your videos; your explanations are super clear and easy to follow. Thanks a lot!
Please send it to me
That piece of art is f*****g awesome. I followed every single step and realized how little I knew about excel. Now I am trespassing all the budget tracking I had in my homemade Excel sheet to your template, which looks much cleaner. However, I came across one "problem" and would like to know what would be your strategy, imagine the following situation, there is a month when you don't have any income, or you have more expenses than income and you use money of your "Savings"->"emergency fund". How would you track this information?
I'd really appreciate your comments on this.
Thank you!
Good question.
I think you could create a new category under expenses named "used savings".
And in the "budget tracking" table, under "details", you could specify the kind of savings was used (eg: emergency fund, sinking fund, other savings).