Get overview of income and expenses during 2024 because I retire mid year. This tutorial will help with that! (I've done Excel budget for many years but very manual and timeconsumming). I'll add a function and graph of withdrawing on my savings accounts/pension funds on a two-three decades overview. I am not sure how to organise or present the use of savings yet, but something with extending the budget to future years added with inflation. I would like to know what happens in economy when I either have to replace my car (every 8-10 years), or my spouse dies (loss of expenses and income). My pension funds get paid either lifelong or 10 years (when I say start) or a lump sum. I would be good to know if I can afford a new PC laptop to my new hobby in Amateur Astronomy Imaging (expensive hobby) or a PowerQuery cource with you eg. ;-) If you have any tips I would like to know?
This is not only the best budget spreadsheet I have seen, but the best step by step tutorials I have ever seen. You have gone out of your way to make the whole thing as simple as possible to understand. I have seen lots of these over the years and the vast majority of people make them too cumbersome to use and don't give proper explanations either
I absolutely love this budget worksheet. I have been customizing my spreadsheet for 10 to 15 years so I have to stay with that one, but I am also going to use this one for a big picture overview. I would suggest adding a savings rate area and a net worth area.
You already had the best personal finance dashboard with your previous iteration. This one is even better. And, you make it so user friendly with this fantastic, easy-to-follow tutorial that I've been able to add some customization. I replaced the income donut with a donut that shows mortgage information: equity, dynamic balance, and dynamic payoff date. Kudos to you, Mynda!
It took me five hours, but I finally completed the dashboard. I've learned so much and was able to tweak some things to relate to my transactions. And I changed a few things on my budget sheet and refreshed and everything updated so gracefully. You are a Godsend. I truly watch your videos like they are tv shows. Can't wait for the next episode
I just finished watching your personal finance video from 2 years ago and it was really well made. Having this updated video is even more amazing. Thank you so much for all the effort, honestly it helps a lot
Mynda, I have been following you and your work for about a year now and I just wanted to chime in and tell you what I am sure many people have told you already and that is that you are a true Excel gem!. I love your videos, I have downloaded many of your cheat sheets and handouts and templates and now I am checking out this wonderful interactive budget with dashboard that I have been looking for to finalize my killer budget. I still have a very large learning curve with Excel, but you make it all worth learning it. Thank you so much for what you do.
I am a retired manufacturing engineer three years now and really miss working with excel. I watched this video and it is mind blowing. Mynda, you are awesome! I am going to get right on this and create a new budget workbook. Thank you very much. I will be watching more of your videos and check out your online courses. Gotta keep my mind sharp right!!!
I have searched for many videos hoping to get a decent, and easy to use expense tracker template and tutorial, I'm glad I saw this video. Thank you so much for the great template and an informative tutorial.
Great guide. Exactly what my wife and I were looking for to take our simple Excel budget to the next level. Well explained, well shown steps, very clear. My new favourite Excel website.
I love how attractive it is - not as clunky and off-putting as so many spreadsheets. I will be sharing this around in various budgeting forums I've been visiting! One embarrassingly basic question - how do I rename the bank accounts? I know it's technically correct but "Checking" is not the name I'd choose with our bank setup.
Awesome to hear 🙏 when you enter your own bank transactions, the report will pick up the name of the bank accounts you enter in the transactions table 😉
Absolutely bl**dy amazing, thank you Mynda. I don't often comment, but my ghast is truly flabbered! ;--) I did use Excel many moons ago when I worked in a bank (it was fairly simple, just 2 or 3 of us referenced it for unusual payments / exceptions - but that was updating a paper-based system ;-) ). However I had never used pivot tables, power query, or even 'Tables' for that matter. This has taken me more hours than I'd care to admit, and there is no way I would have got through it without your video tutorial and template. I started a family budget a couple of years ago, and had developed that to a point just with formulas etc. where it was invaluable to me. We've recently moved home, and there was a lot to keep track of. Your former employers must still be trying to fill your boots! I'm very, very grateful for your work on this, and releasing it as a free template is a service that not many go out of their way to gift for the betterment of others. Many, many thanks from sunny old Blighty! ;-)
1) Integration of 1 to n subcategories per main category and related evaluation options & Give to add & remove category & subcategory 2) Tracking of fixed vs. variable expenses 3) Also Add payment type add like cash ,credit card,Different bank , Google pay etc also maintained balance Again, thank you so much, I am looking forward for upcoming tutorials 🙂
I've done something similar for my own finances this year. It's an excellent exercise in so many excel skills. My best suggestion would be to setup a table that maps descriptions to subcategories so that reoccurring transactions are automatically mapped. That way you only need to map the transactions with new descriptions.
@AntonioGonzalez-no2hk make a table which contains the transaction description in one column and the corresponding categories in another column. You only need to do each description once. Then load it to PowerQuery and merge it to the transactions table. This will essentially do a Xlookup between the transaction table and the new table using the descriptions as the common factor. If you don't know about PowerQuery this is a great simple thing to get you introduced to it.
A piece of art as always Mynda - and agree on comments before, the best budgetting spreadsheet and simple tool I've seen also and have been hunting last few months! Thanks as always
Thank you, thank you, thank you!!!! I wanted a way i could track my money that was as close to 100% screen reader friendly as possible. I started looking around. One i liked the look of had a six hour tutorial. Thank you for making it simple and straight forward. I also appreciate your recognition of how easy it is for formulas to "break". I know I will be able to accomplish setting this up for my needs.
This video was not even what I was searching for, but I watched it anyway and wow -- what a great channel, super helpful content. Very eye opening for what all is possible within Excel for someone who has only ever maintained a fundamental working knowledge of it. Thank you.
@@MyOnlineTrainingHub Serious question, but do you do work for custom Sheets/Excel documents? I'm currently in dire need of one and was about to find someone on Upwork/Fiverr. Please let me know if you do, what I need is similar to a budgeting calendar but a little more complex than that and I am currently using a very clunky workflow that takes up a lot of my time.
Thank you this was the best Excel/power query training I've seen online! I'm here because I want something I can work with at home for personal finances. However, this is also interesting as I can use something like this to work more efficiently for work. Thank you!
Hi...Thanks for this great tutorial... Need 1 advice: Say i transfer balance between my accounts.How do i reflect that. Example: I transferred 4k from my account to my wallet. And used cash for multiple expenses...How to cater that. Hope i am able to explain it
In the Transactions list the Description will be 'Transfer to xyz account' and the Sub-category will be 'Transfer'. For cash you will 'Transfer' money from say your 'Saving' account to your 'Cash' account. Then when you spend cash, the account will be 'Cash'. You'll also want to exclude the 'Transfer' Sub-category from your report.
Hi Mynda, Thank you so much for this fantastic tutorial! I found it incredibly informative, useful, and easy to follow. Your clear explanations and step-by-step approach really helped me understand the concepts. Looking forward to more of your content! Keep up the great work!
Hello just found your video today and curious as to how i go about adding more than one credit card and checking account to the report so that i am able to identify which actual card or checking account the transactions are taking place in?
You can tag the different cards in the first column of the transaction table. If you want to filter by account, you can do that in the transaction table using the filters or Slicers, or you can build another PivotTable to analyse your expenses by account/card.
This is simply beyond outstanding, I watched 22 minutes, and it is absolutely mind blowing. Definitely, I am going to purchase one of your courses, I can't still decide which one. I want to create a very nice dashboard like this one, I have several things going on my mind on how to make it work, since I use my credit card for daily expenses and I paid the with credit card monthly without causing interest, I also share the household with my wife, she transfers me money and I paid for everything. I know with your videos I can make it work, thanks Mynda!
Wonderful to hear!🙏 Be sure to watch to the end 😁 I'd probably enter the money from your wife as a reduction to expenses, rather than income and the payment to your credit card is a balance sheet transaction that clears a debt, so not part of the Profit & Loss. If it's reports you want to build, I recommend my Excel Dashboard course: www.myonlinetraininghub.com/excel-dashboard-course
@@MyOnlineTrainingHub That's the one I was thinking of yesterday, I am going to go with that one. Thanks for replying to me and the tips. You're awesome, genius and dedicated🤗
@@MyOnlineTrainingHub Hi Mynda, fantastic work! I have been trying to use YNAB, and frankly, I still feel blind. This is clean, and comprehensive, and I'm an excel guy, so I want to adopt this going forward. I am confused about how to handle credit card transactions. I see in Transactions where to add CC transactions as a Credit Account, but how do you add payments? Simply as a Debit from Checking? What do you Categorize it as? I am unclear what you mean above when you say "the payment to your credit card is a balance sheet transaction that clears a debt, so not part of the Profit & Loss." Regarding CC debt (or any loan debt), do you have an add-on sheet to track that as well? I'm going to get started as is, leaving these off for now, but getting some clarity on the above would be incredibly helpful. Thx!!
Thanks Mynda for the template. One day, I wish we could use CDR Open Banking APIs to directly pull transaction data from various banks into this Excel template (perhaps using built-in Python magic), so we could seamlessly refresh bank transactions each time the Excel file is opened or on button trigger, rather than csv importing once a month, which I think is too longer time gap to reconcile.
This is a wonderful budget until I got adding PivotTable from external source. I have a MacBookAir and it says " Microsoft Excel cannot complete this operation because the ODBC Driver Manager is not installed. To install the ODBC Driver Manager, run Setup and install the database driver for the type of database(s) you want to access." when you try to go to Microsoft and do that it's a paid version and I'm not sure if I'm supposed to download an extra app or what is it that I need to do. Yes, I am totally new to excel and it's taking me longer than I thought but I hope I can find a way.
Unfortunately, Power Query on the Mac is not fully developed yet. One limitation is that you can't currently get data from the current file, so you'd have to move all the data to an external file. It may be easier to try this personal finance dashboard instead: ruclips.net/video/MRtHNqafufg/видео.html
Such a helpful tool, thanks so much. Bit of an excel noob so hoping someone will be able to help me figure out a couple of things now I've been through the process of adding my categories, budget and transctions: 1) For each cell in my budget where I've added data, there's a green triangle in the top left hand corner. Everything seems to work fine otherwise. 2) In the Variance column for the income section of the P&L statment, I just have a row of ##### - any ideas? 3) Is there a way to change the currency throughout the spreadsheet that won't risk screwing up all of the infrastructure? Thanks in advance!
Great to hear! 1. ignore the green triangle. It just means that the formulas in the column are not the same, which is fine for the budget. 2. try making the column wider to display the contents of the cell 3. you can change the currency. It's just formatting.
Excellent job, Mynda, as usual, and extremely important for the beginning of this year. Every year I make a budget, but with this tutorial and your template I will have a very effective tool to be able to monitor all expenses and income on a monthly basis. Thank you very, very much and have a great new year.
What’s the best way to document the transaction of paying off your credit card bill? Is that a subcategory that should be added or what is the best approach?
That's a transfer from your bank to your credit card. It shouldn't be included in your report because you've already accounted for those amounts in the transactions. You can just tag it with a new category type called 'Transfer' and exclude this from your Profit & Loss.
Hi Mynda, This is a great tutorial/ I used the template file and it worked perfectly, now I'm building the file from scratch following all the steps in the video. At about 10 mins in, you append one file, but shortly after you merge the other one. I have no concept of why one would be an append but not the other. Could you explain please. I have done as instructed, but don't understand why I am doing that. Thanks
Great to hear you've found this useful! You can think of appending as copying and pasting data from one table under the other, whereas merging is more like performing a VLOOKUP or XLOOKUP from one table to another to bring in some/all of the data.
I am using Excel in a Mac. It is version 16.82 and the licenses is Microsoft 365 Subscription. I downloaded the template and it works fine. But when I tried to create my own, I discovered Power Query has less functionality: I can't choose a data source from within the spreadsheet; it has to be from an external source. When I choose Excel file as the source and I go to choose the file, the file I am working in is greyed out and is not available for choosing. I guess I have to modify the template being careful not to break it.
Just watched the video and will create my own from scratch later. A few months ago, I watched another video on this exact topic. It was about 6 hours long and took me about a week to complete. As with everything in life, there are advantages and disadvantages with everything. The advantage with your method is the formulas are relatively easy. The disadvantage (if you can call it that) is it needs to be refreshed every month. Question. How does it look in future years? The advantage with the other method is it refreshes automatically as you go along. The disadvantage is that some of the formulas needed to achieve this are insane. The slightest error in creating the formula can involve hours of snagging even expanding the formula bar and using Alt+Enter to "split" the formula into the 7 or 8 lines. So, off for a cup of coffee for now. Looking forward to starting from scratch and creating my own. As usual, thanks for your time and effort in creating this video.
Awesome to hear. Hope it goes well. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@MyOnlineTrainingHub Thanks, not anticipating any problems (just had to download 8 months of statements in slightly different formats because I recently switched banks). Nothing that a little hard work couldn't cure. If I do get stuck, there's always your template to fall back on if I need to. Certainly useful to compare formulas.
Hi Mynda, firstly, thank you for the wonderful content, I really do appreciate it. I have built your budget template and it works beautifully, however, I have a couple of transactions where I have created a column in Power- Query to dynamically alter the the output based on the description content, but for some reason this doesn't appear to work anymore. I have tried different variations and am now at a loss - if that makes any sense to you, do you have any suggestions please... 😕
Very well done, as usual. My problem is that I receive my pensions on the 3rd last working day of the month and consequently they represent the income for the following month. What is the best way to change the budget period so that it can be linked to the transaction data?
I wouldn't bother, honestly. It sounds like you think of your pension income as being received in advanced of the period it relates to, but presumably it's the same amount every month, so I would just leave it be recorded/reported in the month it is received.
@@MyOnlineTrainingHub I was able to solve my problem by creating a field in the transaction table that uses a formula to automatically insert the budget month for each transaction. Works great. Thanks again.
Hi Brenda, Thank you for explaining of the budget spreadsheet. I do have a question though, how would you do a visual of the top 5 sub-category's expenses in this budget spreadsheet?
Simply amazing!. Just one remark. Formula in sheet Analysis cell D3 should be c3>0 good news? I mean below budget (parenthesis is bad news as you said).
Thank you! Yes, if C3 is a negative value, then the money left at the end of the period is less than planned, so this is bad news from a budget vs actuals perspective. i.e. we came in below our budget. This is one of those variances that's a bit of a mind bender and I deliberated over how to label it. If you want to label it so it makes sense to you, then I think that would be fine 😊
Hey thank you so much for this video ! I am a student trying to figure out how to manage my finances and I needed a budget spreadsheet that I could customise to my likings ! Very grateful for this, thank you :)
Hi there! Thank you so much for this very helpful video. I just need a little advice as I am a little bit lost in the part of "loading to" the pivot chart. The problem is that the sum of budget does not show the subcategories and in the pivot chart it shows "blank" subcategory with sum number. In the column "Actual" its OK (each subcategory has some value). Do you know where could be problem please ? Thank you very much again for this superb video !!! :-)
I love wathching this video since i wanted to do something similar. After watching the video and doing everything step by step i have a question. I did not filled out the budget table until the end of the year since a lot of my expenses categories and income change is there a way that once i fill it out the profit loss pitvot table can update on the budget column? any help will be appreciated. Thanks
I would just enter zeros for your budget items until such time that you have the data. That way, the queries and PivotTable will work without any errors.
Hi! First, thank you so much for sharing this. I am in the process of building this for myself and this is awesome! My question to you is at 13:29 mark, how do you get the sum of the budget to populate in the table for each line item? My budgeted items are currently sitting in one line item and not allocated across all the categories. Thank you so much in advance!
You need to enter your budget amounts in the Budget table as per the example file. Power Query will then do the conversion of the budget table so it feeds the PivotTable correctly. If you're still stuck, you're welcome to post your question and Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you so much for the feedback! I will go back to the budget amounts in the budget table and review/correct. I will also post the question to the forum too.
Hi! This is a game changer, thank you! Do you have a tutorial for the savings tab? When I try to create it the pivot table isnt picking up the month and year from the "monthly savings amount" list.
Glad you like it! I didn't do a video for the savings tab but there are notes on that tab as to how to use it. It's very straight forward. Just enter your data in the table and press Refresh All on the Data tab of the ribbon. If you're stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello, Thank you for this amazing video and template. I learned a lot about Power Queries and Excel. I do have a question about the Credit Payments, or let's say money transfers from Checking to Credit, not accounted for in the Report or Analysis. I tried creating a specific category for the transfer, but I ended up with an excess on the income side. Although I can just take it into account while reading the Report and Analysis, I was just wondering if there is a better way you account for them.
Hi. You are THE RUclips channel I go to to learn new additional features in MS Excel and I've seen a lot of your, and other channels, videos on personal expenses. But I would say almost all, if not all, only consider entire years. If I'm in, for example, June-2022, I would like to see my last 12 months and compare them to their previous 12 months (a 24 month comparison). How does one set time slides and Pivottables when one wants to choose the report on a specific month, say 3 months ago? Thank you so much for your wonderful work. The way you explain everything is just so clear!!! And love you use dd/mm/yyyy date format: it makes it SO obvious!
Thanks for your kind words and support 🙏🥰 check out this post on rolling 12 month periods: www.myonlinetraininghub.com/excel-slicers-for-rolling-periods
This tutorial is great! I have a question about sinking funds. I budget for discretionary expenses each month into sinking funds (clothes, vacation, medical, etc). For example, I transfer $300 into a clothing sinking fund each month. If I spend $50 on clothes from my main checking account, I transfer $50 from my clothing account back into my main checking. The remaining $250 stays in the sinking fund. What would the reporting category look like for transactions such as this?
Would it be sub-category "clothes"; category-type "Expense" for the initial $300 and the same for the $50 purchase. Then what does the transfer of $50 look like once it is transferred back into the main checking account?
Assuming you only want to track actual spend in your personal Profit & Loss statement, I would track the $300 debit transfer as a savings sub-category e.g. 'Sinking Fund - Clothing' that is not included in the P&L i.e. Category Type: Balance Sheet. Then when you spend the $50, it is entered as a debit on sub-category 'Clothing' that is included in the P&L and the transfer back from the sinking fund to your checking account is entered as a credit against the Sinking Fund - Clothing sub-category.
@MyOnlineTrainingHub I just want to make sure I am setting this up correctly: 1. initial $300: sub-category "Sinking Fund - Clothing"; Category "Savings"; Category Type "Balance Sheet 2. spent $50: sub-category "Clothing"; Category "Discretionary"; Category "Expense" 3. transfer $50 from the sinking fund back to checking: sub-category "Sinking Fund - Clothing"; Category "Savings"; Category Type "Credit" I would have the following category types: income, expense, balance sheet, and credit?
Thank you, Mynda! This is a great template and tutorial. I am having trouble with Step 4 though. When I click on refresh data I get an error message [Dataformat.error] we couldn't convert to number. I went back to the transactions sheet and removed text that was in the currency columns and made sure all of the columns were formatted correctly, text or currency. But that did not fix the problem. Have I done something wrong?
All of these tutorials are awesome! We're all lucky they are available on RUclips! I have one question please: when I import my bank transaction data, the transaction description is split across 2 or 3 rows most of the time, but not all, and the'useful bit' is not always in the same position (sometimes the first row, sometimes the second etc). However, the date and amount are always in line with the first row. Do you have any advice for automating cleaning this up please? I know there are techniques for merging columns of text into a row but I've only seen manual methods. If you have any tips or tricks for speeding this up or automating it I would be very grateful!
Great to hear, Anthony! I'd use Power Query to clean your banking data. If you get stuck, you're welcome to post your question and sample Excel file containing dummy data on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
It would be helpful to add the Category in the Transactions table - how do I do this? Reason is for using a slicer on categories and expanding the analysis further.
Sure, you can add a lookup formula (XLOOKUP/VLOOKUP) to find the sub-category in the Categories table and return the category. e.g. =XLOOKUP([@[Sub-category]],TblCategories[Sub-category],TblCategories[Category])
I have already tried that, but that leaves me with the error "[Expression.Error] The field 'Category' already exists in the record". @@MyOnlineTrainingHub
Sounds like you're doing this in Power Query instead of the Table in the Excel worksheet. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Really awesome budget tracker! Question about the transaction table. I’m entering in my bank statements. I notice there’s no where to enter in the total amounts in each account or the beginning balances? How or where do you put in the beginning balances so that the spreadsheet knows actual amounts?
Could this be modified for couples? That would amazing to see how to keep track of finances for couples and/or families :) If you already have something similar (I didn't see one on your channel), please point me in the right direction! Thank you!!!
Hi, love your video and learning a lot of excel. could we run this financial tracker for tax year period and should I go about doing it. My tax year run from 1 of April to 31 of March
Sure. Add a column to classify the dates into your fiscal periods and then use that field in your reports. You'll have to modify the queries. It's too much to explain in the comments here, sorry.
This the best excel tutorial and most helpful excel video I have watched so far. You have literally gone out of your way to put in all the efforts, and we cannot really ask more. However, out my selfish nature and obliged to ask if we can have similar budgeting dashboard for family as considering both the husband and wife's income, expenditure, debt etc., Thank you so much once and may God bless!
Thanks for your kind words! You can add more accounts to the transaction table for your husband/wife's income. Hope that points you in the right direction. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Mynda thank you so much for your efforts. how to prepare excel with daily schedule,weekly plan,to-do list,smart calender-task tracker like a Google sheet ?
Hi, Thank you for this template. It is a great guide to create your own personal financial dashboard board. I have a question regarding the timeline slicer, I want the slicer to only show a particular date range. For example- just from April 2023- March 2024. how can I do that? My tables only have data from April 2023, but in the timeline slicer I am getting data from January 2023. How can I get this custom date range? Thanks.
Nice video. Couple of questions: 1. If I have multiple checking accounts, how would you suggest recording a transaction from one account to another? The money hasn’t been spent obviously , but just moved from one place to another. 2. Consider the following situation: suppose in January I go to see a movie with 3 other friends and suppose a ticket costs 10 usd and that I paid for the whole group, meaning I paid 40 usd. Suppose my nice friends pay me back 2 months later only(meaning in March, I receive back 30 usd), how would you record such an activity? If I record it as I paid 40 usd for the ‘entertainment’ category, this won’t be accurate since I’ve only spent an actual 10, so how do you tackle this problem? 3. A similar situation: suppose I’m renting an apartment with 2 roommates and that every 3 months someone else pays for the house rent which suppose is 3000 USD. Meaning in Jan I’ll pay 3000 usd, and in April again, and so on… , how would you suggest recording such an activity? 4. Follow-up question for the previous situation: suppose now, we decide to split the bills in the following way: roommate A pays the internet bill , and roommate B pays for the electricity bill, how would you suggest recording the correct spending activities for every individual? 5. Last question: suppose I want to break up my spending categories into 3 bigger categories namely: fundamentals,fun, and savings and show reports for them.(meaning classifying the sub-categories into these 3 bigger categories) How can you do that?
1. tag it as a transfer and omit 'transfers' from your reports. 2-4. as an accountant you would enter an accrual to represent the money your friends owe you in the period it relates to. The other side of the accrual goes to a debtor account on your balance sheet and when your friends pay you, you enter the payment received against the debtor account to clear their debt to you. You could set up another table to represent your balance sheet debtors accounts so you can monitor them, but balancing things could get messy without a proper accounting system ensuring your double entry bookkeeping is correct. 5. Add another column to the categories table for the upper categories.
AMAZING! This is an awesome system. Thank you. This is my first time looking into power query. I work with fiscal periods. Where would you add the column for this ie in the transactions table or the power query transactions settings? Any suggestions would be much appreciated. :-)
Hi, I have created a budget based on your previous video's and it been working Great!!. However I always like to upgrade and improve. I am struggling to display the transactions linked to the category for the period selected. Meaning, For February I Spend 100 on Takeout's but where, so a short descriptive Section on the side would help understand instead of jumping back to the transactions sheet and start filtering. And as you select your period and category then the data changes as per what is logged in your transactions table. I Know how to do it in Power BI as it would be simple. But How do I go about this in excel. Just taking a shot here to ask you.
Great to know you're making use of these techniques. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello, thank you very much for this amazing template. I dind't found the way to update the sub-category slicer in the Transactions sheet when they were changed in the Categories sheet.
Thank you for sharing this great video! Using it to track my expenses. Only challenge I am having is the doubling of the budget figures on the P&L report figures. Does anyone know how to go round this?
@@MyOnlineTrainingHub feedback is appreciated. I'm doing all the updates on the transactions worksheet.All the other worksheets update upon clicking the refresh icon.Noticed that even the worksheets you shared for this video is also doubling bydget figures.
In the file you can download from the link in the video description you'll see my budget isn't being doubled. If you look at the Personal Profit and Loss statement on the Report page you'll see it reconciles to the Budget table figures.
@@MyOnlineTrainingHub after comparing your template with my file, I finally discovered why my file was doubling the budget figures. The transaction sheet in Power Query had a budget column with null values in all cells. I simply deleted that column, and everything worked perfectly! Now I have a nearly perfect template 😊. Thank you!
Can someone explain why Appending the Transactions and Budget queries at 10m:20s is appropriate? Seems like merging queries would be more appropriate… Or would they both work? Thanks!
I spent quite a while trying to make a cumulative chart not reset on the new year as yours seemed to do on the savings sheet. Then I realized it doesn't, it just looks like it does because the Jan saved is larger than for Dec. 😋I want to do something slightly different so will work around it. Thanks for the great content, I always learn something.
Absolutely love this spreadsheet and your easy to follow instructional videos. I have used Mint for many years and I'm sure like most of us, I was scrambling to find an alternative when they decided to discontinue it. Is it possible to rollover budget surplus or deficit amounts to the next month's budget? Can you please provide some direction?
Glad it was helpful! You can keep a running total of the surplus/deficit, but it's not trivial to explain how and largely depends on where you want to see it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi and big thank you for great content again. One question though - when combining tables in PQ would the solution where you connect tables and load them to datamodel and have power pivot afterwards to create reports later also work and be less „cluttered“ ?
You're welcome. You can use Power Pivot, but it wouldn't be less cluttered as such. It would just load the data to the Power Pivot data model instead of the Pivot cache. Both are held in Excel's memory rather than the worksheet.
Hello Mynda, Love this as it's a very insightful goal planner🙂however I'm struggling with the pivot, because I have blanks seeing that the power query is not reading the budget categories when I load the pivot resulting data not showing under the budget column in the pivot instead blanks that have all the budget figures. Please advise🙌🤲
Blanks in your budget shouldn't be an issue. I guess it's not clear where they are. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you very much for your valuable information I want to know how can I make a perfect project using excell? How can I sell on Etsy if I make a project?
When I demoed using emojis in the categories, I accidently chose a different car to the one I had used for MV Loan that I later copied in, so I ended up with two Transport categories. 🤦♀️ In the file download there is only one though 😉
Hello, would it be possible to make this workbook without pivot tables? I find it cumbersome to refresh data. Thank you kindly for your outstanding efforts.
I'm a newbie at excel, haven't used it in at least 20 years. Would you suggest to try and build one from scratch or edit yours with my information? Also, if I add categories, and then more sub-categories to your existing chart on the categories sheet, will those changes be reflected across the rest of the sheets and 'talk' to each other? Thanks so much
Yes, you can add more categories and subcategories and my file will automatically include them, so it's up to you whether you start from scratch or modify my file, but it's probably easier to modify my file.
Very beautiful dashboard! In order to automate mapping subcategories to transactions based on counterparties, should i create a mapping table TblCounterparties?
Thank you! If the transaction relates to two categories, then enter the amounts on separate lines with the different categories, so that the two amounts add up to the one transaction in your bank account.
Hello, first of all, congratulations because your tutorial videos are awasome. I really like the way that all is explain and show. The second is a question regarding power query. I have done all your steps in the same way, and I also saw that in your video the budget amounts have a "null". My problem is that the budget values are not showing in the pivot table, or shows are grouped in a blank category. How do you fix this? I am not very familiar with Power Query but looking foward to learn more about it.
❓What are your financial goals for this year?
Marry into money 💰😜
@@rob4433 lololololol ☝️
🤣👌🏼@@rob4433
Not rely on the bank of Mum & Dad
Get overview of income and expenses during 2024 because I retire mid year. This tutorial will help with that! (I've done Excel budget for many years but very manual and timeconsumming). I'll add a function and graph of withdrawing on my savings accounts/pension funds on a two-three decades overview. I am not sure how to organise or present the use of savings yet, but something with extending the budget to future years added with inflation. I would like to know what happens in economy when I either have to replace my car (every 8-10 years), or my spouse dies (loss of expenses and income). My pension funds get paid either lifelong or 10 years (when I say start) or a lump sum. I would be good to know if I can afford a new PC laptop to my new hobby in Amateur Astronomy Imaging (expensive hobby) or a PowerQuery cource with you eg. ;-)
If you have any tips I would like to know?
This is not only the best budget spreadsheet I have seen, but the best step by step tutorials I have ever seen. You have gone out of your way to make the whole thing as simple as possible to understand. I have seen lots of these over the years and the vast majority of people make them too cumbersome to use and don't give proper explanations either
Wow, thank you! So pleased you liked it 😊🙏
I absolutely love this budget worksheet. I have been customizing my spreadsheet for 10 to 15 years so I have to stay with that one, but I am also going to use this one for a big picture overview. I would suggest adding a savings rate area and a net worth area.
I have seen a lot of videos on how to construct personal budgets to control your finances and this is by far the best one.
Awesome to hear, George! Thank you 😊
Bless you for making this! I don't know how many hours you just saved me--you are a godsend!
Glad I could help 😊
You already had the best personal finance dashboard with your previous iteration. This one is even better. And, you make it so user friendly with this fantastic, easy-to-follow tutorial that I've been able to add some customization. I replaced the income donut with a donut that shows mortgage information: equity, dynamic balance, and dynamic payoff date. Kudos to you, Mynda!
Wow, that sounds great 😊
This is absolutely fantastic, I was sat here pondering creating something similar for 2024 and beyond and here is it. Thank you.
Glad it was helpful! 😊
It took me five hours, but I finally completed the dashboard. I've learned so much and was able to tweak some things to relate to my transactions. And I changed a few things on my budget sheet and refreshed and everything updated so gracefully. You are a Godsend. I truly watch your videos like they are tv shows. Can't wait for the next episode
Awesome to hear!
Mynda you are an international treasure!
Thanks so much! Glad you enjoyed it 😊
I think people who get put off on hearing Power Query don't know what they are missing out on...this is fantastic work❤
Thanks so much!
I just finished watching your personal finance video from 2 years ago and it was really well made. Having this updated video is even more amazing. Thank you so much for all the effort, honestly it helps a lot
Wonderful to hear! 🙏
Mynda, I have been following you and your work for about a year now and I just wanted to chime in and tell you what I am sure many people have told you already and that is that you are a true Excel gem!. I love your videos, I have downloaded many of your cheat sheets and handouts and templates and now I am checking out this wonderful interactive budget with dashboard that I have been looking for to finalize my killer budget. I still have a very large learning curve with Excel, but you make it all worth learning it. Thank you so much for what you do.
Wow, thanks for your kind words, Mark! Sincerely appreciated 🥰
I am a retired manufacturing engineer three years now and really miss working with excel. I watched this video and it is mind blowing. Mynda, you are awesome! I am going to get right on this and create a new budget workbook. Thank you very much. I will be watching more of your videos and check out your online courses. Gotta keep my mind sharp right!!!
That's wonderful to hear! And thanks for your kind words 😊
You are the bestest Mynda,
Lots of Love from India.
People sell similar templates for $30. but you are giving it free.
God bless you
Thanks so much 😊
I have searched for many videos hoping to get a decent, and easy to use expense tracker template and tutorial, I'm glad I saw this video. Thank you so much for the great template and an informative tutorial.
Glad it was helpful!
Great guide. Exactly what my wife and I were looking for to take our simple Excel budget to the next level. Well explained, well shown steps, very clear. My new favourite Excel website.
Glad it was helpful!
I love how attractive it is - not as clunky and off-putting as so many spreadsheets. I will be sharing this around in various budgeting forums I've been visiting! One embarrassingly basic question - how do I rename the bank accounts? I know it's technically correct but "Checking" is not the name I'd choose with our bank setup.
Awesome to hear 🙏 when you enter your own bank transactions, the report will pick up the name of the bank accounts you enter in the transactions table 😉
Thank you so much for this video. I used it as a tutorial to develop my own version that fits my needs perfectly.
Love you content!
Great to hear!
Absolutely bl**dy amazing, thank you Mynda. I don't often comment, but my ghast is truly flabbered! ;--) I did use Excel many moons ago when I worked in a bank (it was fairly simple, just 2 or 3 of us referenced it for unusual payments / exceptions - but that was updating a paper-based system ;-) ). However I had never used pivot tables, power query, or even 'Tables' for that matter. This has taken me more hours than I'd care to admit, and there is no way I would have got through it without your video tutorial and template. I started a family budget a couple of years ago, and had developed that to a point just with formulas etc. where it was invaluable to me. We've recently moved home, and there was a lot to keep track of. Your former employers must still be trying to fill your boots! I'm very, very grateful for your work on this, and releasing it as a free template is a service that not many go out of their way to gift for the betterment of others. Many, many thanks from sunny old Blighty! ;-)
Wow, thanks so much, Darren! My ghast is truly flabbered by your lovely comment 😁 I'm delighted you found the template and tutorial helpful.
1) Integration of 1 to n subcategories per main category and related evaluation options & Give to add & remove category & subcategory
2) Tracking of fixed vs. variable expenses
3) Also Add payment type add like cash ,credit card,Different bank , Google pay etc also maintained balance
Again, thank you so much, I am looking forward for upcoming tutorials 🙂
Glad you found it helpful!
I've done something similar for my own finances this year. It's an excellent exercise in so many excel skills. My best suggestion would be to setup a table that maps descriptions to subcategories so that reoccurring transactions are automatically mapped. That way you only need to map the transactions with new descriptions.
Great tip! Thanks for sharing 🙏
how would i go about doing that?
@AntonioGonzalez-no2hk make a table which contains the transaction description in one column and the corresponding categories in another column. You only need to do each description once. Then load it to PowerQuery and merge it to the transactions table. This will essentially do a Xlookup between the transaction table and the new table using the descriptions as the common factor. If you don't know about PowerQuery this is a great simple thing to get you introduced to it.
A piece of art as always Mynda - and agree on comments before, the best budgetting spreadsheet and simple tool I've seen also and have been hunting last few months! Thanks as always
Awesome to hear 🙏😊
Professional, informative, easy to follow instructions, as always Mynda. Thank you and Happy New Year!
Thank you for watching and happy New Year 🥳
Wow, what an amazing tutorial. This is probably the best tutorial I've seen on any topic ever. Thank you for taking the time!
Wow, thanks so much, Nancy! You've made my week! 🥰
Thank you, thank you, thank you!!!! I wanted a way i could track my money that was as close to 100% screen reader friendly as possible. I started looking around. One i liked the look of had a six hour tutorial. Thank you for making it simple and straight forward. I also appreciate your recognition of how easy it is for formulas to "break". I know I will be able to accomplish setting this up for my needs.
So pleased this was helpful!
This video was not even what I was searching for, but I watched it anyway and wow -- what a great channel, super helpful content. Very eye opening for what all is possible within Excel for someone who has only ever maintained a fundamental working knowledge of it. Thank you.
Glad you enjoyed it! 🙏😊
@@MyOnlineTrainingHub Serious question, but do you do work for custom Sheets/Excel documents? I'm currently in dire need of one and was about to find someone on Upwork/Fiverr. Please let me know if you do, what I need is similar to a budgeting calendar but a little more complex than that and I am currently using a very clunky workflow that takes up a lot of my time.
No, sorry. I don't have time for consulting.
Thank you this was the best Excel/power query training I've seen online! I'm here because I want something I can work with at home for personal finances. However, this is also interesting as I can use something like this to work more efficiently for work. Thank you!
Awesome to hear!
hi! beautiful tutorial, can we have the tutorial for savings cartel plz? thanks
Bloody legend! Thanks so much for the beautiful, easy to use, and free excel sheet, as well as the best tutorial to along with it 🙏
So pleased you liked it 😊😁
Hi...Thanks for this great tutorial...
Need 1 advice: Say i transfer balance between my accounts.How do i reflect that.
Example: I transferred 4k from my account to my wallet. And used cash for multiple expenses...How to cater that.
Hope i am able to explain it
In the Transactions list the Description will be 'Transfer to xyz account' and the Sub-category will be 'Transfer'. For cash you will 'Transfer' money from say your 'Saving' account to your 'Cash' account. Then when you spend cash, the account will be 'Cash'. You'll also want to exclude the 'Transfer' Sub-category from your report.
Hi Mynda,
Thank you so much for this fantastic tutorial! I found it incredibly informative, useful, and easy to follow. Your clear explanations and step-by-step approach really helped me understand the concepts. Looking forward to more of your content!
Keep up the great work!
Glad it was helpful!
This is honestly the best thing I could have found! I was trying to look for some sort of budget template and this one was the best, thank you!
You're so welcome! 😊
Hello just found your video today and curious as to how i go about adding more than one credit card and checking account to the report so that i am able to identify which actual card or checking account the transactions are taking place in?
You can tag the different cards in the first column of the transaction table. If you want to filter by account, you can do that in the transaction table using the filters or Slicers, or you can build another PivotTable to analyse your expenses by account/card.
This is simply beyond outstanding, I watched 22 minutes, and it is absolutely mind blowing. Definitely, I am going to purchase one of your courses, I can't still decide which one.
I want to create a very nice dashboard like this one, I have several things going on my mind on how to make it work, since I use my credit card for daily expenses and I paid the with credit card monthly without causing interest, I also share the household with my wife, she transfers me money and I paid for everything. I know with your videos I can make it work, thanks Mynda!
Wonderful to hear!🙏 Be sure to watch to the end 😁
I'd probably enter the money from your wife as a reduction to expenses, rather than income and the payment to your credit card is a balance sheet transaction that clears a debt, so not part of the Profit & Loss.
If it's reports you want to build, I recommend my Excel Dashboard course: www.myonlinetraininghub.com/excel-dashboard-course
@@MyOnlineTrainingHub That's the one I was thinking of yesterday, I am going to go with that one. Thanks for replying to me and the tips. You're awesome, genius and dedicated🤗
@@MyOnlineTrainingHub Hi Mynda, fantastic work! I have been trying to use YNAB, and frankly, I still feel blind. This is clean, and comprehensive, and I'm an excel guy, so I want to adopt this going forward. I am confused about how to handle credit card transactions. I see in Transactions where to add CC transactions as a Credit Account, but how do you add payments? Simply as a Debit from Checking? What do you Categorize it as? I am unclear what you mean above when you say "the payment to your credit card is a balance sheet transaction that clears a debt, so not part of the Profit & Loss." Regarding CC debt (or any loan debt), do you have an add-on sheet to track that as well? I'm going to get started as is, leaving these off for now, but getting some clarity on the above would be incredibly helpful. Thx!!
Great timing and with no doubt great content. Thanks alot Minda and happy new year.
Thanks for your support! Happy new year to you too 😊
Thanks Mynda for the template.
One day, I wish we could use CDR Open Banking APIs to directly pull transaction data from various banks into this Excel template (perhaps using built-in Python magic), so we could seamlessly refresh bank transactions each time the Excel file is opened or on button trigger, rather than csv importing once a month, which I think is too longer time gap to reconcile.
Brilliant! What a nice way to start my year more organised.
So pleased you can make use of it 😊
This is a wonderful budget until I got adding PivotTable from external source. I have a MacBookAir and it says " Microsoft Excel cannot complete this operation because the ODBC Driver Manager is not installed. To install the ODBC Driver Manager, run Setup and install the database driver for the type of database(s) you want to access." when you try to go to Microsoft and do that it's a paid version and I'm not sure if I'm supposed to download an extra app or what is it that I need to do. Yes, I am totally new to excel and it's taking me longer than I thought but I hope I can find a way.
Unfortunately, Power Query on the Mac is not fully developed yet. One limitation is that you can't currently get data from the current file, so you'd have to move all the data to an external file. It may be easier to try this personal finance dashboard instead: ruclips.net/video/MRtHNqafufg/видео.html
Such a helpful tool, thanks so much.
Bit of an excel noob so hoping someone will be able to help me figure out a couple of things now I've been through the process of adding my categories, budget and transctions:
1) For each cell in my budget where I've added data, there's a green triangle in the top left hand corner. Everything seems to work fine otherwise.
2) In the Variance column for the income section of the P&L statment, I just have a row of ##### - any ideas?
3) Is there a way to change the currency throughout the spreadsheet that won't risk screwing up all of the infrastructure?
Thanks in advance!
Great to hear!
1. ignore the green triangle. It just means that the formulas in the column are not the same, which is fine for the budget.
2. try making the column wider to display the contents of the cell
3. you can change the currency. It's just formatting.
Nice! Can you please add a data entry form on the dashboard
to input day to day transaction
You can just add them direct to the Transactions table.
Excellent job, Mynda, as usual, and extremely important for the beginning of this year. Every year I make a budget, but with this tutorial and your template I will have a very effective tool to be able to monitor all expenses and income on a monthly basis. Thank you very, very much and have a great new year.
Thanks for your support, Henry! It's great to hear you budget every year. Awesome discipline!
What’s the best way to document the transaction of paying off your credit card bill? Is that a subcategory that should be added or what is the best approach?
That's a transfer from your bank to your credit card. It shouldn't be included in your report because you've already accounted for those amounts in the transactions. You can just tag it with a new category type called 'Transfer' and exclude this from your Profit & Loss.
Hi Mynda, This is a great tutorial/ I used the template file and it worked perfectly, now I'm building the file from scratch following all the steps in the video.
At about 10 mins in, you append one file, but shortly after you merge the other one. I have no concept of why one would be an append but not the other. Could you explain please. I have done as instructed, but don't understand why I am doing that.
Thanks
Great to hear you've found this useful! You can think of appending as copying and pasting data from one table under the other, whereas merging is more like performing a VLOOKUP or XLOOKUP from one table to another to bring in some/all of the data.
Hi Mynda, Could you kindly let me know how you inserted the icons with text into the Categories cells?
They're emojis. You can enter them by pressing the Windows key + ;
I am using Excel in a Mac. It is version 16.82 and the licenses is Microsoft 365 Subscription. I downloaded the template and it works fine. But when I tried to create my own, I discovered Power Query has less functionality: I can't choose a data source from within the spreadsheet; it has to be from an external source. When I choose Excel file as the source and I go to choose the file, the file I am working in is greyed out and is not available for choosing. I guess I have to modify the template being careful not to break it.
Yes, I’m afraid Excel for Mac doesn’t have full Power Query functionality yet.
this woman is a freaking machine WOW thank you
Glad you liked it 😁
Just watched the video and will create my own from scratch later.
A few months ago, I watched another video on this exact topic. It was about 6 hours long and took me about a week to complete.
As with everything in life, there are advantages and disadvantages with everything.
The advantage with your method is the formulas are relatively easy. The disadvantage (if you can call it that) is it needs to be refreshed every month. Question. How does it look in future years?
The advantage with the other method is it refreshes automatically as you go along. The disadvantage is that some of the formulas needed to achieve this are insane. The slightest error in creating the formula can involve hours of snagging even expanding the formula bar and using Alt+Enter to "split" the formula into the 7 or 8 lines.
So, off for a cup of coffee for now. Looking forward to starting from scratch and creating my own.
As usual, thanks for your time and effort in creating this video.
Awesome to hear. Hope it goes well. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@MyOnlineTrainingHub
Thanks, not anticipating any problems (just had to download 8 months of statements in slightly different formats because I recently switched banks). Nothing that a little hard work couldn't cure. If I do get stuck, there's always your template to fall back on if I need to. Certainly useful to compare formulas.
Hi Mynda, firstly, thank you for the wonderful content, I really do appreciate it.
I have built your budget template and it works beautifully, however, I have a couple of transactions where I have created a column in Power- Query to dynamically alter the the output based on the description content, but for some reason this doesn't appear to work anymore. I have tried different variations and am now at a loss - if that makes any sense to you, do you have any suggestions please... 😕
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Very well done, as usual. My problem is that I receive my pensions on the 3rd last working day of the month and consequently they represent the income for the following month. What is the best way to change the budget period so that it can be linked to the transaction data?
I wouldn't bother, honestly. It sounds like you think of your pension income as being received in advanced of the period it relates to, but presumably it's the same amount every month, so I would just leave it be recorded/reported in the month it is received.
@@MyOnlineTrainingHub Thank you.
@@MyOnlineTrainingHub I was able to solve my problem by creating a field in the transaction table that uses a formula to automatically insert the budget month for each transaction. Works great. Thanks again.
This is the first time I come across one of your videos. Where have you been all my life? I have a lot of catching up to do. You are amazing
Wow, thank you so much! Glad you enjoyed my video and hope you find more that are helpful to you 😊🙏
Hi Brenda, Thank you for explaining of the budget spreadsheet. I do have a question though, how would you do a visual of the top 5 sub-category's expenses in this budget spreadsheet?
I'd put them in a bar chart 😉or if you wanted to visualise them over time, then a line chart would probably be ok.
@@MyOnlineTrainingHub Oh thanks for the advice!
THANKS, thanks a lot, this is what I have been looking for, highly appreciated, you have made yourself a new devoted follower and fan
Awesome, thank you!
thankyou so much i feel over the moon when i see yours videos
Aw, thanks so much 🥰
Simply amazing!. Just one remark. Formula in sheet Analysis cell D3 should be c3>0 good news? I mean below budget (parenthesis is bad news as you said).
Thank you! Yes, if C3 is a negative value, then the money left at the end of the period is less than planned, so this is bad news from a budget vs actuals perspective. i.e. we came in below our budget. This is one of those variances that's a bit of a mind bender and I deliberated over how to label it. If you want to label it so it makes sense to you, then I think that would be fine 😊
Hey thank you so much for this video ! I am a student trying to figure out how to manage my finances and I needed a budget spreadsheet that I could customise to my likings ! Very grateful for this, thank you :)
Glad it was helpful!
Hi there! Thank you so much for this very helpful video. I just need a little advice as I am a little bit lost in the part of "loading to" the pivot chart. The problem is that the sum of budget does not show the subcategories and in the pivot chart it shows "blank" subcategory with sum number. In the column "Actual" its OK (each subcategory has some value). Do you know where could be problem please ? Thank you very much again for this superb video !!! :-)
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I love wathching this video since i wanted to do something similar. After watching the video and doing everything step by step i have a question. I did not filled out the budget table until the end of the year since a lot of my expenses categories and income change is there a way that once i fill it out the profit loss pitvot table can update on the budget column? any help will be appreciated. Thanks
I would just enter zeros for your budget items until such time that you have the data. That way, the queries and PivotTable will work without any errors.
Hi! First, thank you so much for sharing this. I am in the process of building this for myself and this is awesome! My question to you is at 13:29 mark, how do you get the sum of the budget to populate in the table for each line item? My budgeted items are currently sitting in one line item and not allocated across all the categories. Thank you so much in advance!
You need to enter your budget amounts in the Budget table as per the example file. Power Query will then do the conversion of the budget table so it feeds the PivotTable correctly. If you're still stuck, you're welcome to post your question and Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you so much for the feedback! I will go back to the budget amounts in the budget table and review/correct. I will also post the question to the forum too.
Hi! This is a game changer, thank you! Do you have a tutorial for the savings tab? When I try to create it the pivot table isnt picking up the month and year from the "monthly savings amount" list.
Glad you like it! I didn't do a video for the savings tab but there are notes on that tab as to how to use it. It's very straight forward. Just enter your data in the table and press Refresh All on the Data tab of the ribbon. If you're stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello,
Thank you for this amazing video and template. I learned a lot about Power Queries and Excel. I do have a question about the Credit Payments, or let's say money transfers from Checking to Credit, not accounted for in the Report or Analysis. I tried creating a specific category for the transfer, but I ended up with an excess on the income side. Although I can just take it into account while reading the Report and Analysis, I was just wondering if there is a better way you account for them.
I ended up creating another category type for the transfer. I can filter them out but there are currently hidden in the Report and Analysis.
Yes, that's what I would have said. Glad you figured it.
Hi. You are THE RUclips channel I go to to learn new additional features in MS Excel and I've seen a lot of your, and other channels, videos on personal expenses. But I would say almost all, if not all, only consider entire years. If I'm in, for example, June-2022, I would like to see my last 12 months and compare them to their previous 12 months (a 24 month comparison). How does one set time slides and Pivottables when one wants to choose the report on a specific month, say 3 months ago? Thank you so much for your wonderful work. The way you explain everything is just so clear!!! And love you use dd/mm/yyyy date format: it makes it SO obvious!
Thanks for your kind words and support 🙏🥰 check out this post on rolling 12 month periods: www.myonlinetraininghub.com/excel-slicers-for-rolling-periods
Thank you for this video, can I change the language to my own language, if so is there a formula I must change for this
Excel will automatically change the language for the functions and formulas etc. to that of your PC upon opening the file.
This tutorial is great! I have a question about sinking funds. I budget for discretionary expenses each month into sinking funds (clothes, vacation, medical, etc). For example, I transfer $300 into a clothing sinking fund each month. If I spend $50 on clothes from my main checking account, I transfer $50 from my clothing account back into my main checking. The remaining $250 stays in the sinking fund. What would the reporting category look like for transactions such as this?
Would it be sub-category "clothes"; category-type "Expense" for the initial $300 and the same for the $50 purchase. Then what does the transfer of $50 look like once it is transferred back into the main checking account?
Assuming you only want to track actual spend in your personal Profit & Loss statement, I would track the $300 debit transfer as a savings sub-category e.g. 'Sinking Fund - Clothing' that is not included in the P&L i.e. Category Type: Balance Sheet. Then when you spend the $50, it is entered as a debit on sub-category 'Clothing' that is included in the P&L and the transfer back from the sinking fund to your checking account is entered as a credit against the Sinking Fund - Clothing sub-category.
@MyOnlineTrainingHub Yes! Thank you! I will try doing it this way.
@MyOnlineTrainingHub I just want to make sure I am setting this up correctly:
1. initial $300: sub-category "Sinking Fund - Clothing"; Category "Savings"; Category Type "Balance Sheet
2. spent $50: sub-category "Clothing"; Category "Discretionary"; Category "Expense"
3. transfer $50 from the sinking fund back to checking: sub-category "Sinking Fund - Clothing"; Category "Savings"; Category Type "Credit"
I would have the following category types: income, expense, balance sheet, and credit?
Thank you, Mynda! This is a great template and tutorial. I am having trouble with Step 4 though. When I click on refresh data I get an error message [Dataformat.error] we couldn't convert to number. I went back to the transactions sheet and removed text that was in the currency columns and made sure all of the columns were formatted correctly, text or currency. But that did not fix the problem. Have I done something wrong?
Sounds like you still have text where it's expecting a number.
All of these tutorials are awesome! We're all lucky they are available on RUclips! I have one question please: when I import my bank transaction data, the transaction description is split across 2 or 3 rows most of the time, but not all, and the'useful bit' is not always in the same position (sometimes the first row, sometimes the second etc). However, the date and amount are always in line with the first row. Do you have any advice for automating cleaning this up please? I know there are techniques for merging columns of text into a row but I've only seen manual methods. If you have any tips or tricks for speeding this up or automating it I would be very grateful!
Great to hear, Anthony! I'd use Power Query to clean your banking data. If you get stuck, you're welcome to post your question and sample Excel file containing dummy data on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
It would be helpful to add the Category in the Transactions table - how do I do this? Reason is for using a slicer on categories and expanding the analysis further.
Sure, you can add a lookup formula (XLOOKUP/VLOOKUP) to find the sub-category in the Categories table and return the category. e.g. =XLOOKUP([@[Sub-category]],TblCategories[Sub-category],TblCategories[Category])
I have already tried that, but that leaves me with the error "[Expression.Error] The field 'Category' already exists in the record". @@MyOnlineTrainingHub
Sounds like you're doing this in Power Query instead of the Table in the Excel worksheet. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Really awesome budget tracker! Question about the transaction table. I’m entering in my bank statements. I notice there’s no where to enter in the total amounts in each account or the beginning balances? How or where do you put in the beginning balances so that the spreadsheet knows actual amounts?
You can enter the opening balance with an account category that is 'not reported' or similar and then exclude these values from your reporting.
Could this be modified for couples? That would amazing to see how to keep track of finances for couples and/or families :)
If you already have something similar (I didn't see one on your channel), please point me in the right direction! Thank you!!!
You can add multiple accounts to the transactions table to allow for your wife's accounts.
Thank you for getting back to me promptly and for the proposed PFD... Cheers
Hi, love your video and learning a lot of excel. could we run this financial tracker for tax year period and should I go about doing it. My tax year run from 1 of April to 31 of March
Sure. Add a column to classify the dates into your fiscal periods and then use that field in your reports. You'll have to modify the queries. It's too much to explain in the comments here, sorry.
This is absolutely fantastic, but how to change the currency? I live in Sweden and we don't have dollar.
You can change the currency format on the Home tab of the ribbon.
This the best excel tutorial and most helpful excel video I have watched so far. You have literally gone out of your way to put in all the efforts, and we cannot really ask more. However, out my selfish nature and obliged to ask if we can have similar budgeting dashboard for family as considering both the husband and wife's income, expenditure, debt etc.,
Thank you so much once and may God bless!
Thanks for your kind words! You can add more accounts to the transaction table for your husband/wife's income. Hope that points you in the right direction. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Mynda thank you so much for your efforts.
how to prepare excel with daily schedule,weekly plan,to-do list,smart calender-task tracker like a Google sheet ?
You're welcome! I'll keep your request in mind for future videos.
Hi, this looks very complicated. Complete novice at Excel. Will have a go see how I get on with this. Looks amazing.
Yeah, probably not ideal for a beginner, but if you just modify my template, you should be ok.
Hi,
Thank you for this template. It is a great guide to create your own personal financial dashboard board. I have a question regarding the timeline slicer, I want the slicer to only show a particular date range. For example- just from April 2023- March 2024. how can I do that? My tables only have data from April 2023, but in the timeline slicer I am getting data from January 2023. How can I get this custom date range?
Thanks.
Thank you! The timeline slicer always shows complete years.
Thank you so much for continuing to present practical Excel solutions...much appreciated.
🙏 So glad it was helpful!
My only quibble with this is that the profit and loss pivot table is cumulative up to the current month. I'd like to see what each month looks like.
You can add the month and year fields to the column labels.
Nice video.
Couple of questions:
1. If I have multiple checking accounts, how would you suggest recording a transaction from one account to another? The money hasn’t been spent obviously , but just moved from one place to another.
2. Consider the following situation: suppose in January I go to see a movie with 3 other friends and suppose a ticket costs 10 usd and that I paid for the whole group, meaning I paid 40 usd. Suppose my nice friends pay me back 2 months later only(meaning in March, I receive back 30 usd), how would you record such an activity? If I record it as I paid 40 usd for the ‘entertainment’ category, this won’t be accurate since I’ve only spent an actual 10, so how do you tackle this problem?
3. A similar situation: suppose I’m renting an apartment with 2 roommates and that every 3 months someone else pays for the house rent which suppose is 3000 USD. Meaning in Jan I’ll pay 3000 usd, and in April again, and so on… , how would you suggest recording such an activity?
4. Follow-up question for the previous situation: suppose now, we decide to split the bills in the following way: roommate A pays the internet bill , and roommate B pays for the electricity bill, how would you suggest recording the correct spending activities for every individual?
5. Last question: suppose I want to break up my spending categories into 3 bigger categories namely: fundamentals,fun, and savings and show reports for them.(meaning classifying the sub-categories into these 3 bigger categories) How can you do that?
1. tag it as a transfer and omit 'transfers' from your reports.
2-4. as an accountant you would enter an accrual to represent the money your friends owe you in the period it relates to. The other side of the accrual goes to a debtor account on your balance sheet and when your friends pay you, you enter the payment received against the debtor account to clear their debt to you. You could set up another table to represent your balance sheet debtors accounts so you can monitor them, but balancing things could get messy without a proper accounting system ensuring your double entry bookkeeping is correct.
5. Add another column to the categories table for the upper categories.
AMAZING! This is an awesome system. Thank you. This is my first time looking into power query. I work with fiscal periods. Where would you add the column for this ie in the transactions table or the power query transactions settings? Any suggestions would be much appreciated. :-)
Add your fiscal periods in Power Query: ruclips.net/video/bo-43zg1R8U/видео.html
I did something like this a few years ago, but, this takes it to another level, thank you very much.
Glad you liked it! 🙏😊
Hi, I have created a budget based on your previous video's and it been working Great!!. However I always like to upgrade and improve. I am struggling to display the transactions linked to the category for the period selected. Meaning, For February I Spend 100 on Takeout's but where, so a short descriptive Section on the side would help understand instead of jumping back to the transactions sheet and start filtering. And as you select your period and category then the data changes as per what is logged in your transactions table. I Know how to do it in Power BI as it would be simple. But How do I go about this in excel. Just taking a shot here to ask you.
Great to know you're making use of these techniques. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello, thank you very much for this amazing template. I dind't found the way to update the sub-category slicer in the Transactions sheet when they were changed in the Categories sheet.
If refreshing all (data tab) doesn't work, then try deleting the Slicer and inserting it again.
@@MyOnlineTrainingHub indeed the refreshing all does not work. Thanks
Thank you for sharing this great video! Using it to track my expenses. Only challenge I am having is the doubling of the budget figures on the P&L report figures. Does anyone know how to go round this?
Sounds like you might be loading the data to the worksheet instead of keeping it as a connection only.
@@MyOnlineTrainingHub feedback is appreciated. I'm doing all the updates on the transactions worksheet.All the other worksheets update upon clicking the refresh icon.Noticed that even the worksheets you shared for this video is also doubling bydget figures.
In the file you can download from the link in the video description you'll see my budget isn't being doubled. If you look at the Personal Profit and Loss statement on the Report page you'll see it reconciles to the Budget table figures.
@@MyOnlineTrainingHub You are very right 🙆♂.I seem to have followed all the steps. Just cant figure out why my figures are doubling. Thank you.
@@MyOnlineTrainingHub after comparing your template with my file, I finally discovered why my file was doubling the budget figures. The transaction sheet in Power Query had a budget column with null values in all cells. I simply deleted that column, and everything worked perfectly! Now I have a nearly perfect template 😊.
Thank you!
Can someone explain why Appending the Transactions and Budget queries at 10m:20s is appropriate? Seems like merging queries would be more appropriate… Or would they both work? Thanks!
With this data you can use either. In the file I made available for download, you'll see I merged them 😉
I spent quite a while trying to make a cumulative chart not reset on the new year as yours seemed to do on the savings sheet. Then I realized it doesn't, it just looks like it does because the Jan saved is larger than for Dec. 😋I want to do something slightly different so will work around it. Thanks for the great content, I always learn something.
Glad you found it helpful 😊🙏
Absolutely love this spreadsheet and your easy to follow instructional videos. I have used Mint for many years and I'm sure like most of us, I was scrambling to find an alternative when they decided to discontinue it. Is it possible to rollover budget surplus or deficit amounts to the next month's budget? Can you please provide some direction?
Glad it was helpful! You can keep a running total of the surplus/deficit, but it's not trivial to explain how and largely depends on where you want to see it. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Happy New Year Mynda! This is excellent, thank you. Gives me some ideas for my own budget tracker that I've been using for years
Glad to read that 😊
Was searching to build exactly something like this the last few months. Perfection 👌.
Thanks so much!
Hi and big thank you for great content again. One question though - when combining tables in PQ would the solution where you connect tables and load them to datamodel and have power pivot afterwards to create reports later also work and be less „cluttered“ ?
You're welcome. You can use Power Pivot, but it wouldn't be less cluttered as such. It would just load the data to the Power Pivot data model instead of the Pivot cache. Both are held in Excel's memory rather than the worksheet.
Hello Mynda, Love this as it's a very insightful goal planner🙂however I'm struggling with the pivot, because I have blanks seeing that the power query is not reading the budget categories when I load the pivot resulting data not showing under the budget column in the pivot instead blanks that have all the budget figures. Please advise🙌🤲
Blanks in your budget shouldn't be an issue. I guess it's not clear where they are. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Okay will do, thanks for the prompt reply, much appreciated☺
You are amazing, Greatings from Brazil 🇧🇷
Thank you! 😃
Thank you very much for your valuable information
I want to know how can I make a perfect project using excell?
How can I sell on Etsy if I make a project?
I recommend you research what is already selling well on Etsy and see if you can make better versions of those templates.
@27:33 I noticed there were two Transport values, but later they were combined (211 and -102). Is that because a refresh all was done?
When I demoed using emojis in the categories, I accidently chose a different car to the one I had used for MV Loan that I later copied in, so I ended up with two Transport categories. 🤦♀️ In the file download there is only one though 😉
Hello, would it be possible to make this workbook without pivot tables? I find it cumbersome to refresh data. Thank you kindly for your outstanding efforts.
Sure, you can use formulas 👍
May I ask for help on how to do this please?@@MyOnlineTrainingHub
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
This looks great, would this work on google sheet?
Thank you! No, not available for Sheets because it doesn't have Power Query. You could try this one instead: ruclips.net/video/MRtHNqafufg/видео.html
I'm a newbie at excel, haven't used it in at least 20 years. Would you suggest to try and build one from scratch or edit yours with my information?
Also, if I add categories, and then more sub-categories to your existing chart on the categories sheet, will those changes be reflected across the rest of the sheets and 'talk' to each other? Thanks so much
Yes, you can add more categories and subcategories and my file will automatically include them, so it's up to you whether you start from scratch or modify my file, but it's probably easier to modify my file.
Very beautiful dashboard!
In order to automate mapping subcategories to transactions based on counterparties, should i create a mapping table TblCounterparties?
Thank you! Yes, you could add a mapping table for the counterparties and do the mapping with a Power Query merge.
Great video! How would you go about splitting a transaction between multiple budget categories?
Thank you! If the transaction relates to two categories, then enter the amounts on separate lines with the different categories, so that the two amounts add up to the one transaction in your bank account.
Hello, first of all, congratulations because your tutorial videos are awasome. I really like the way that all is explain and show. The second is a question regarding power query. I have done all your steps in the same way, and I also saw that in your video the budget amounts have a "null". My problem is that the budget values are not showing in the pivot table, or shows are grouped in a blank category. How do you fix this? I am not very familiar with Power Query but looking foward to learn more about it.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Amazing video, great table and great explanation, thanks for your video :D
Glad you liked it!
I need a video to forecast budget for upcoming year using past three years separated by month and actual doses.
Check out the Excel Forecast sheet tool: www.myonlinetraininghub.com/excel-forecast-sheet