Streamline Your Finances with the Excel Bill Tracker for it

Excel bill tracker for IT

keeping track of your finances can often be difficult most trackers are either expensive very manual or not very customizable that's why in this video we're going to make a simple automated budget tracker in Excel for free in just 15 minutes by the end of the video you'll have a dashboard tab breaking down your income your expenses and your savings not just by month but also by category you'll also have entries tab to add all of your transactions which will automatically feed into the dashboard so let's get into [Music] it the first part is the entries tab which is where we would add any of the transactions and you can download this exact same Excel file to follow along basically you can see here we have all of the different headers so let's go ahead and fill in a sample transaction let's suppose it's the first of 2024 and for the month here we want it in text format because we'll need that for the dashboard so we'll go equals type text there as the formula hit the toab key the value is the date that we want to reference comma and the format we want it in monthly format so in quotations we're just going to put the M four times there close up parenthesis and hit enter that's basically saying to spell out the full month for the amount let's suppose This Is 50 bucks and the category you'll notice here on the side that we have this category table so it needs to be one of these categories this is a fairly comprehensive list so it should cover most transactions so for the category there we can go over and create a drop- down list by going to data then going to this button right here which is the data validation we're going to want to make a list and that list source is going to be all of these categories down over here here then we just want to hit on okay there and you'll notice that we have that drop down for us let's suppose that this F50 is for transport and then it should automatically detect whether it's an income or an expense based on this table over here to the side so to do that we can use the X Lo up formula equals x lookup hit the top key and don't worry if these steps look a bit overwhelming we just need to set them up once and then they'll be ready for all future transactions so for the X lookup here the lookup value is a transport and we want to find that transport within all of these categories as you can see you'll find it in here comma and what do we want it to return well whether it's an income or an expense so we can highlight this column right next to it close the parenthesis and hit enter now you can see it's an expense which is making sense there for description this is fully optional in case you need to remind yourself I'm just going to put an NA there now to make this table Dynamic we just want to hit on contrl T that's going to convert it into a table for us now that we've done that if we go ahead and add something new let's suppose it's on that same day hit enter there you'll notice that that table fills up to a second day and we can add new numbers here and let's say I put a category as for example groceries here then all of a sudden we get that it's an expense there already so if you want want to add any new transactions you simply need to put them towards the bottom there now let me fast forward how I add a few more awesome as you can see here I've added a dozen more transactions just to make it a bit more realistic once we have that we're ready to move on to the dashboard tab but first as we're here looking at all of this data it's important to learn how to convert it into meaningful numbers a great way to do that is using hubspot's free introduction to data analytics report which you can download using the link in the description below as they're kindly sponsoring this video in this 50-page PDF you can find a comprehensive breakdown of what data analysis is what types of data analysis there are and some best practices as well this resource is great if you're a beginner or if you've already taken some statistic courses as well like myself I personally find it most useful to brush up on some important terms and make sure I do all of the best practices when it comes to analyzing data so if you want to check this out head over to the link in the description below to download this completely free report from hopspot to level up your data analysis skills all right back to the video all right now that we're done with the entries let's move up to the dashboard Tab and over here you can see that we want to get the year to date monthly breakdown and for this we're going to break it down into our income expenses and savings so let's go ahead and fill those in so income expense and saving for each of these we want to break it down by month so January over here would be the 1 and so forth February so let me fast forward how I do this great so we have the full list of months and now we need to find the income for January and to do to do that we're going to need the entry stop but we're going to need a formula so let's use the sum ifs which is going to sum only if it's in the month of January and only if it's income not if it's an expense so the sum range is going to be all of the amounts so we'll select that with control shift down and you notice here that instead of having a cell number like maybe d58 we have the amount that's what it says the reason for that is because this is a table using a table format when we insert new transactions they're going to be detected in the amounts as well making it more Dynamic comma there and the criteria range is all of the separate months so let's select all of these with control shift down should be the whole month area comma and the criteria number one is that it needs to be equal to the month of January which we can select right above here you can't quite see it there but you get the idea comma and the criteria range number two is that it needs to be an income based on this column over here so let's select them with control shift down comma and the criteria number two if we go back to the dashboard is that it needs to be an income so make sure you select that income this case we need to lock it to make it Dynamic so basically if we drag this formula down right now that income row is going to start U moving around which we don't want we only want to move it from here to an expense or a saving so for that we can just hit on the F4 key there you'll notice that it adds some dollar signs we want to put those dollar signs only under that 10 and hit enter I did that by pressing the F4 key a second time close the parenthesis and hit enter great once we have that we can drag it down by double clicking there on the edge and we can select this whole area right over here contrl C and paste it over to our expenses as well and finally paste it to our savings this is going to require a different formula which is simply the income minus the expenses and that's pretty much it there so we can double click there again to update it to make sure the sum ifs is working po properly we can always select any random cell and you'll notice that it's now linking to expenses as opposed to income and it's in the month of February so that's all looking good there hit escape to get out of that now we can format this whole area so this first part I could select and maybe choose a green color as income is positive I could also change it to say a white font and this one in red and so forth so let me fast forward how I do this so I've done these titles but now we can also find the total down below over here and for this we can just select alt equals that's going to get the sum of all of these totals that looks good and we also want the average just to see what our monthly average income is we can use the equals average formula for that hit the Tab Key there and we want to select all of the months close the parenthesis and hit enter you might notice though that average is very very low looking at our numbers we don't really have anything below 3,000 there the reason is because we have all of these zeros it's also counting them so instead of a normal average formula we might want to use an F at the end there so it's the average if formula and this is the range we're happy with that we'll put a comma there and as our criteria in quotations let's say that it's greater than zero this way it filters out any zero values now you can see it's looking a lot better once we have this we can contrl C to copy and just paste it across these other ranges you'll notice that they move dynamically to this last part so it's looking good let me continue fast forwarding some of the formatting awesome you can see what this is looking like now I basically added some borders as you can see up over here and added some lighter fill colors down below once we have this an optional plus could be to select this area with the month values and go to conditional formatting under data bars we might want to select say a green bar just to see more or less what the numbers look like could do the same with the expenses in red just going to do that quickly here just for you to see and the Savings in yellow so again data bars and select the yellow that's fully optional great now we have this monthly breakdown but it doesn't tell us much about specific categories we can do that down below though with the year-to-date category breakdown over here we're going to have both the income categories as well as the expense categories and based on these we're going to want to select the types so we'll go equals and under the entries we can select all of the types based on the table over here so we have three categories for the income we'll select those three and for the expenses we'll do the same thing we have these over here and hit enter awesome with that we can use the same sum ifs formula that we used earlier so the sum range is going to be all of the different amounts as well so control shift down comma the criteria range number one is no longer that it's a month right instead it's by the category so let's select all of the categories there with control shift down comma and the criteria number one is that now it needs to be a salary we don't have to filter by income because the salary already implies that we'll close the parenthesis and hit enter you can see the amount there and we can drag that down already and now we can copy this and paste it down over here and again we'll just drag it down by double clicking we might want to find the totals down below like we did earlier just by going to alt equals and hitting enter let me fast forward how I do this one and reformat as well awesome we now have this breakdown by category as well we could add conditional formatting just like we did before one thing to note though is the file is getting kind of long so it would be nice to group these areas we can do that by selecting the rows kind of like that and going over to data and clicking on group there you'll notice we get this bar on the side which I'll show you what it does after same thing with this part over here we can just select that area up to row 25 say and click on group now we can press that minus sign and it's going to collapse that whole area and the Plus sign is going to expand it we can do both at once by pressing that one or expand both at once by pressing that two finally you might have noticed there's an empty area up top over here this is to add a few of the Key summary values so let me first put the text and then we'll work on all the areas so you can see here I've added a few text values that I think are useful first up we have the current date now you don't want to type it manually and instead to make make it Dynamic we can use the today formula which Returns the date today so basically whenever you open the file then the percentage of month is basically hey how much of the month has already passed that way we can gauge more or less how we're doing in that month we can do that by going to equals and first we're going to go ahead and select day formula hit the top key and we want to select the current date close up parenthesis that's basically basally going to give us that hey it's the 11th from there we want to divide that by by by the end of month e formula this gives us the last day in a month hit the top key there but we need to tell it what month we want for that we just put the month formula inside of it and we want to select the current date so it detects that it's the month of December there we can close a parenthesis add a comma and we want it to be the current month so we're going to put a zero there close the parenthesis and hit enter now you can see that we've completed 35% of the current month so if we do like 11 = 11 / 31 you'll see we get that same number the idea here is that now it's Dynamic for the year to date income we would just link the total that we have to date same thing goes with the expense and then the savings over here that's the idea let me fast forward how I format these awesome now that we have the finished product let's go ahead and try if it actually works so we can head over to the entries tab down towards the bottom let's suppose we have a new entry on the 1st of August for example just to see what that works if that works we'll put the date there it's August let's put a huge amount there just for us to see if everything works okay and let's say that this is something under groceries hit the TP key so it's an expense and we don't need a description it's fine as is now when we take a look at the dashboard you can see that we have a massive amount in expense there in August and very bad savings as well if we scroll lower down you'll notice that groceries is looking particularly bad there so it means that it's all working correctly if you want to learn more about Finance in Excel the next step is to build a financial model which you can learn how to do over here or by taking our complete fin evaluation course over here hit the like And subscribe and I'll catch you in the next one