learning how to budget income and expenses for personal or for small business is important for any financial plan but how do you create one in Excel hi this is Randy with Excel for Freelancers and today we're going to create this incredible income and expense budget completely from scratch and that's going to be complete with adding updating and editing transactions along with both monthly and annual budgets it's going to be an incredible training you won't want to miss I I cannot wait so let's get started Al righty thank you so much for joining me I am super excited to bring you this income and expense budget in which we will be creating from scratch doing all the designs all the coding everything right in front of you today so you're going to learn how to create this by yourself and of course use it to your content this template is absolutely free all you need to do is Click link down below and I'll make sure that that gets to you just look for the word download I do really appreciate your continued support on this channel so make sure you do get subscribed and don't forget to click the notification icon Bell that'll ensure that you get these trainings alerted when they become available I create them twice a week in fact every Tuesday I have comprehensive application development and every single weekend I do VBA basic training so you've got the full picture right here on Excel for Freelancers if you do want to support this channel I have just launched my brand new Excel template library with over 350 workbooks inside it and that includes every single template I have created on this channel with these incredible done for you workbooks so make sure you pick that up that also comes with a library and that means in that Library a single click to open the workbook and a single click to go to the training video so I'm really proud after 7 years to launch that 350 workbook pack all right let's get started on this training what I'm going to do is I'm going to go over a brief overview of this application then what I'm going to do is I'm going to switch to a blank workbook template here and we're going to get started creating that so inside this particular workbook here what we have is a budget we can select a month we can select a year from a drop- down list and we can also add a brand new transaction we can also edit or delete an existing transaction all we need to do is just select and we can do that we've got an overview we've got the June budgeted income we've got the June actual income here we have budgeted expenses June actual expenses whether we're above or below the budgeted monthly income and whether we're above or below the monthly expenses so we've got all that plus we've got some really cool bar charts for both the monthly income versus the budget the monthly expenses versus the budget we also have the current year income versus budget and the current year expenses versus the budget inside a dut chart and as well as a line graph that we're showing both the actual income and the actual expenses so we've got all that covered up and also inside the admin screen we've got some income types which are fully customizable when we create those transactions we can set any income type we want we've got expense types with the list we can set our monthly expense budget we can set our monthly income budget we've got frequencies here and years that we'll be using also have a database we need to keep hold of all those transactions that's going to be done directly here inside the transaction database and then all for the reoccurring we also going to have reoccurring that'll be available for our patreon members so if you're not a patreon member make sure you get on because I'll be adding that along with whatever else you want me to add in so patreon is the platform where I create an additional training and an additional workbook each and every week just for you all right so that's pretty much it so what I'm going to do is I'm going to take this template that's what we're going to be creating from scratch I'm going to move it to my other screen so that I can keep an eye on it and we're going to get started right away now this particular workbook that we're going to be working in it does have an admin screen to help us it does have a transaction database so we'll be using those but everything here is blank as far as you can see so what we're going to be doing is creating this I'm going to use those first two columns as an admin that means we can hide those columns and I'm just going to put those in Gray so we'll be using those for some admin I want a title and icon so I've got a title here I'm just going to copy that a little bit quicker so we don't necessarily need to format that I'm going to place it here and of course the title is going to be called here income and expense budgeting income and expense budget so that gives us our title and then we can move our logo over and then we'll be adding of course some buttons here so now initially in row two this is where I want to put my transactions here and then in row three I want the selected month so that selected month is going to go here selected month and I want to put that just on the right so here is where we're going to put the drop down list of months I want the selected year and it's going to go here so what I'll do is I'll write justify those and we also have a custom background that I'll be adding in here so this is going to be our transactions I'm going to merge and center that and then we're going to drop this down because we'll be using it quite often here in the beginning so we're going to always show the ribbon here I want to merge and center this we're going to call this transactions so it's going to list of our transactions and I'll probably have to move that over a little bit but that's okay and then what I want to have here is a list of all the transactions so we need to list which columns we want so I want the date in the First Column so we'll put date in that First Column I also want to know the transaction name we'll just put transaction here if it's a type what is the type or account type that we're going to be using so we can just use let's just put account type or something like that and then the amount is going to go here so that's going to list now what I'll give that is a little bit of a color let's bring these up in row four we don't need to skip that row so for these what I want to do is I want a very distinct background color I've got one Save which is going to be closer to our theme and I'll put borders all the way around it and then we'll give it this light blue color I've got that one saved here great so the user entered Fields will be here so what I'm going to do is I'm going to use control one and I'm just going to format them a little bit I want some border here and I want just the dot line on the left side so I'm going to select that dotted line and put that on the left and probably going to left justify those two all right so we've got a list of months here of course we need a drop down list we're going to be listing our information here we're going to need a background then I'm going to add a lot of graph and information here so that will help us we can zoom out a little bit here and to see to get a sense of that so inside the admin area I do want some information when I select a specific transaction I want the ID of that transaction to show up in B2 so B2 is going to hold that so let's go ahead and put that that's going to be called the selected transaction ID and that's going to be B2 I also want to know the row select transaction database row and I want the next transaction ID so that's all going to go here I also want to know the selected month number we do need to list down those months so let's go ahead down here I'm just going to put in months and I want just want a list of those months that way we can use it for the drop down list so put in months and then January and then of course with Excel we just can drag it over here all the way to December and we are good I'll also do one for the transaction type there's going to be two transaction types when I create a new one we have expenses so I need a a drop down list in our user form so it's going to be expenses and income those are the two so what I'm going to do is I'm going to give this a named range here just going to highlight those two we're going to call this trans type it's going to be income or expenses I'm going to highlight over these months here and we're just going to give those a name called months and then what we'll do is we'll just give those a little bit of a color so that they're distinct and uh bord that sufficient it's unnecessary but it's good to highlight those so that's it and I want to do the same thing here also border and just give it a distinct color this green so that's pretty much it now what I want I've got a transaction I've got a table here now this table is called transactions so what I would like to do is a named range for the transaction ID so what we're going to do is we'll set up a named range for that so we're going to go into the formulas name manager now we can create our own I've got some named ranges here that's going to help us so I'll go into that in just a moment so what I want to do is called transaction ID and we can use our table that we've set up so we're going to call it transactions and then we're going to use the bracket and then I want transaction space ID so that's exactly the same as the header there so if I Tab out and tab back in I want to make sure that it encompasses all the data and because we're using a table we don't need to use offset which can help us a little bit so that looks good so notice how the dance against around all the transaction ID once we have that set up we can then determine what row any given transactions on so we know that transaction ID number one is on row four so if we go back into our in expense and we put in transaction let's assume that we've selected transaction ID number one I want to know what row it is on so how do we know that equals if a we're going to match we're looking for the value of one and I want to look up in Array that named range that we just created transaction ID I want an exact match and I want to know the row number that's associated so plus three and then if there's an eror just leave it empty so that's going to tell us transaction ID is on row number four which is correct the next transaction ID we're going to use the max formula equals if eror if there's no data there would be an error so I'm going to use the max transaction ID the maximum number plus one is going to get us our next transaction ID if there's no data it could create an error so I'm just going to default that to one very good so now we've got selected month here so what I'm going to do is I'm going to go into the date of validation and then what we're going to do is we're going to create uh list so data validation here and then we'll do the list and we've already created it called months equals months very good so now we have a drop- down list of months here as you saw that in our admin we have a list of years and I've already created a named range for that and it's called years so to do that all I need to do is create a drop- down list for the selected year so date of validation here and then what we'll do is list here and then equals years very good so now what I want to do is I want to know the month number that's going to help us in some calculations so to do that we can use equals and we'll do match well you could probably do if a just in case match and then what I want to do is I want to select the value of the month and I want to know which one it was months and then we can just do zero and then if there's an error we'll just set it to one although there shouldn't be so that tells us February is the second month that tells us April's the fourth month and so on and so forth so I want to call this the selected month number all right and this we're going to call the selected month selected month cuz we can use these in formulas and this we're going to call the selected year so the selected year we can set a year of 2024 so we know 2024 because I need to set some criteria in the dates now what we're going to do is we're going to have some data that comes down here we also want to add a background so let's do that page layout background I've created a background in mid journey and I've got one saved I'm going to do it from file and I've got it already saved up so it's going to put it in our file and then just go to the file here and I've got one right here and then that's our background so that looks pretty good now what I would like to do is the user entered fields we want to make sure those are going to be set in white so we'll set those to the white background I would also like to create some conditional formatting here for alternating rows but only if there's a data inside the row so how do we do that so what I'm going to do is I'm going to highlight some cells I'm going to go into conditional formatting I'm going to manage rules and I'm going to create a brand new rule it's going to be based on two conditions because it's based on two conditions I'm going to use the and the first condition has to have D5 or at least starting in row five so we're going to take the the dollar sign out of that does not equal empty so that's the first condition the second condition is going to be the mod of row two equals z and that simply just means even rows what I want to do is I want to copy this because I'm going to use something similar for the next rule so I'm going to set a format to that and we'll do a fill and then what we'll do is I have a saved color that saved color is available on fill effects I want to use this blue color right here and then what I'll do is I'll just use it one more time that's a solid single color I want to set the Border we can add a border and I'll use a blue here and then I'll use the dotted line on the left the bottom and the right clicking okay so that's pretty much it for the rule as long as there's data in there it will fill and I want to make sure it's going to be available to row let's say highy R 999 so that's good so now what I'm going to do is I'm going to create a brand new rule and I'm going to use a formula and I'm going to paste this in now this is the same rule however I want this for odd row so I'm going to use the one in that now what I'm going to do is I'm going to format this and once again I'm going to choose the same color and then what I'm going to do is the board on the left the bottom and the right this time I want to use a lighter color so I'm going to use the fill effects and I've got another color that save either this white or very light color and we're going to click okay and we're going to click okay so this is going to be for odd rows so that looks pretty good and then what we want to do is we want to make sure the applies to is exactly the same as the previous that's good I want two rules but now what I want to do is I want to have a specific rule for the selected row see this transaction ID in B2 I'm going to put all the transaction IDs in call colum C here when I load up those transactions it's going to actually be hidden but it'll be there in column C so what I want to do is if I find the row in column C where the transaction ID matches whatever is in B2 I want to highlight that row very distinctly so I'm going to create a new rule for that and I'm going to use a formula and this again is going to be two conditions so we're going to use and for that the first condition is B2 must not be empty so I'm going to put does not equal empty the second condition is B2 must equal whatever is located in column C starting on row five so I'm going to put this starting on row five so I'm going to remove the absolute dollar sign before that so that's the rule right there we're going to give it a distinct format that's different from the others so what I'm going to do is I'm going to do F effects here and I'm going to do these two colors right here and I use a gradient and click okay and I also want a bold and white font so the font's going to be bold and it's going to be white here so going to click okay on that and click okay and that we'll be testing this out and then we'll just apply to the same rows and columns as our previous so we're going to click apply click okay so let's take a quick look at that to see how we've done as we add any data here we see that we've got the alternating rows which is what we want if I were to add let's say a one right here we see that that row becomes highlighted because it does match whatever's located in B2 so that's working good so our conditional formatting is set already very good so we're going to save our work so far and now we're going to move on to creating a framework for some of the graphs and data that we have we're also going to need buttons to add update and delete transactions so let's go ahead and do that now we're going to insert shapes and we'll use a rounded corner here and then what I'll do is I'll just add it we might need additional space so the first thing what I want to do is I want to do the shape format and we'll select a color we have a few colors to choose from we'll choose this one right here that'll give us a color for our theme and then I'm going to Simply put add transactions and then we'll just do a single transaction so what I want to do is I want to use control one we're going to format that and that's going to bring up our format here and I'm going to go to the text options and then we'll do the text and then we don't need any left margin so we can zero that out the right margin we'll use a small one and then zero and zero on that and I do want it right Justified here so we're going to put in the middle and write justify that so we'll add transactions so we also need room for an icon so let's go ahead and increase the shape format we'll do the width to about 1.4 very good so we've got enough space we can also zoom in if we want that thir to make sure we add enough I'm going to move that over here I do want that inside the box but obviously we're limited here but we can left justify that and then what we can do is we can indent the transactions until we get to a point where it's almost centered and that should be fine along with the adding transaction we also need to edit and delete it so what I'm going to do is I'm going to copy this contrl C and contrl V or use contrl D to duplicate and then I don't need any text on these so I can clear that out and I do want a relatively Square button for both our editing existing and deleting so I'm going to make the width. 23 which is going the same as the height okay and I'm going to duplicate this one because I need one for edit and delete now that we've created all our buttons we are ready to add in those icons so I'm going to do just that I'm going to insert here pictures placing over cells in this device I've got some icons saved here this is going to be for the ad new delete and edit I'm going to use those here so we're going to click insert but I do want them formatted at the same so Point 2 we're going to set the height and width now we just simply need to place them so this is going to be for our add here our delete and our edit I'm going to make those a little bit smaller cuz we're limited so I'll do 8 and I do want those centered so the delete going to go over here and our edit's going to go over here I do want them as mentioned Centered for each individual so I'm going to Simply Center that and group it together I'm going to do the same thing with the delete I'm going to highlight both of those I'm going to Center them and then group them together now that they're grouped together I also want both of them individually grouped together so we can do that in just a moment but we got them placed up I'm going to do the same thing with the ad again it's a little bit big so I'll do .8 on that cuz we don't have a lot of space for our buttons I'm going to Center that and then I'm going to group it individually I'm going to hold down the control here for each one and I want to go into the properties I want to make sure that we are moving but not sizing with the cells very good saving our work so far we can take off the selection change our buttons are pretty much good we can zoom out to check the size we're looking good now what we want to do is we want to create a little bit of information for our graph up here and we want some shapes that we're going to use to display some information about the current month or our current year budgeting versus actual so we're going to do some shapes to represent that so I'm going to use a rounded rectangle here and I'm just going to create right about like that and so I'm going to set the shape format we can do this to .9 and we'll do this to 1.6 so that should be sufficient I'm going to remove the outline on this and now what we can is we got a good color here so that's good so what I'm going to do is I'm going to give it a white and bold font although we will need to update that and then we'll do about 12 so this one let's just call this temporary June just a format June although these will be linked budgeted income but I do want to get the format just right before I duplicate them makes it a lot easier I'm going to put this on the bottom I'm going to increase the font a little bit and I want it centered something like that that looks pretty good so now that we have that what I would like to do is I would like to duplicate that for the additional shapes actually before I do that each individual shape is going to have additional text so I'm going to insert one additional text then I'm going to duplicate it's going to make things a little bit quicker because I want that amount now we'll just put in any amount initially that mount's going to be linked and so what I'm going to do is I'm going to increase this to 12 I'm going to set it to White and bold I want the shape format no fill on the format and I also want no outline on the format and so we could just put in any number here just to get the spacing correct so let's do 35,000 just so we can get it and I like the way that it looks a little bit so what I want to do is I just want to make sure the formatting is set up we can move it to the middle and the middle so that we get all right so let's expand that a little bit we want to make sure that our text has sufficient space within our shape again everything's going to be linked so that it automatically changes I like the way that that looks now that we have that what we can do is we can use contr D duplicate that and then I'm going to use this one so this one's going to be for our actual income so we have budget income and then actual income just going to put it as a placeholder however keep in mind that it's going to change so June actual income so that way the month is going to be dynamic of changes but it's a good start I'm going to use control D and I also need a shape that's going to show up for our budgeted expenses so this is going to be color let's do june budgeted expenses just so we know what goes where June budgeted expenses very good I want the expenses colored slightly different so that they're clear so what I'm going to do is I'm going to go into the shape fill and I'm going to give it this color right here this is a standard color so for expenses are going to take on that maroon color once again I'm going to duplicate it here and put it right about here so it's going to be for our actual expenses so that's going to be a placeholder once again and we'll put this actual very good next up what I want to do is I want to have the whether we're above or below the monthly budget and I'm going put that in blue so I'm just going to take this one right right here and then what I want to do is I want to duplicate that one that way I don't have to change the color and place that right about here this is going to be for our above or below budgeted income I'd like to know what percentage so we'll just put this as let's see above budgeted and now again once again it's going to be dynamic and it will change let's do monthly income I want to just want to make sure that we have enough space for all the text Incorporated within the shape so if we need to make the shape bigger we can do that and then lastly what I want to do is I want to have whether we're at expenses so again I'm going to take the expenses one here I'm going to hold down the shift and then duplicate that and put it right about here okay and this is going to be for our above or below monthly expenses so we can just put above budgeted monthly expenses we can reduce the font just a little bit on these shapes just so that we can make sure that we have enough text so what I'm going to do instead of 14 let's go ahead and put it at 12 that way we've always got but I'll keep in High and the percentage high things are looking pretty good and then now we've got an idea of what we want to do let's grab some transactions I want to be able to add update transactions so how do we do that well we do that with a user form the user form is going to capture the transactions or it's going to allow the user to add in the information so let's create a user form to do just that to do that what we're going to do is we're going to go into the VBA editor alt f1s of shortcut we can get to it also from the developer using Visual Basic here if you don't have the developer you can select any ribbon Tab and click customize ribbon make sure the developer selected there inside the developer what we want to do we've got an open I've got two workbooks keep in mind this is our sample that I've got on the other screen here is where we're going to be creating a brand new user form so I'm going to do is insert and then click user form and we have the basic user form here so we need to extend it we've got some fields and the first thing what we want to do is give it a name so we use the properties to do just that if you don't have the properties available it might be somewhere else you can just select here Properties or F four and what I'm going to do is I'm going to give this a distinct name we're going to call this transaction form and then also inside a caption here we're going to put add and then we'll do edit transactions very good I like the way that that looks now what we want to do is we can add a title to that so we can use the toolbox to do just that we're going to use a label here so I'm going to select the label and I'm going to do just that put it at the top so the user understands what they want to do inside the caption here again add and then edit transactions we can put that all on caps actually add and then edit transactions can also set the font and also text Aline we're going to put that as Center so center right there and I also want to set the font here so we're going to go into the font and then I'm going to go to let's say 14 and bold and click okay it's a little bit big on that I guess 12 is sufficient enough on that so let's go to 12 and also we want it centered so to do that we just need to select inside it here and just put Center centered that way it's centered in the form so now that we have our title what we want to do is we want to add some labels so again I'm going to add a label here and the first one is going to be for that transaction name so we want to set the label just the way we want I want it right Justified and I want to set the font here so the font will set that to 10 and click okay so this one is going to be first our transaction name well I will put in the date first that's always important and now what we want to do is I'm going to copy this contr C and then paste it crl V so our next label we'll place it about right here and then I want this to be the name so we'll put in the name transaction name next up after the transaction name we want it in a specific order so what I'm going to do is I'm going to look in the transaction database and I want to make sure it is in this order the date the name the type category and amount notes so this is the order that I wanted in so that's the fields that we're going to be creating so just to double check as we add them I want it exactly the same so after the name here then we want want our type and the type is important and that's going to be whether it's income or expense which is why we created that transaction type drop-down list so transaction name next up we want to know the type so we'll just put in transaction type simply going to be income or expense after that we're going to put in the category that category is either going to be a drop down list of expenses or it's going to be a dropdown list of income accounts so it's based on that transaction type whatever the user selects so we're just going to put in the category and then next up we also want to put in the amount amount and then we'll make sure that they're spaced accordingly and then we'll put in the notes okay we also want some button sets so we'll be adding those great so now that we have that we're going to enter our text box so I'm going to select on a text box and I'll set them all the same height and width here so we'll set the height to 18 so 24 is a bit big here 18 so now we see that this date has a top position of 36 so we want to set the top position to this also at 36 that way it's going to be the same now that we have that we've got the date set set up I also want to make sure that we have everything set up so this is going to be called field one so I'm going to give this name called field one and then subsequently it's going to be called field two field three so on and so forth the transaction name we're simply going to duplicate this copy and paste that and this is going to be for our transaction name so we're going to call this field 2 so field two next up I want to drop down list so to do that we're going to select on our form and we're going to select this combo box here and that's going to be either income or expense so again I wanted exactly the same width and the same height we see that the width is 126 and the height is 18 I want exactly the same so we're going to look the width is 126 the height is 18 so it's perfect so this transaction type we already created a named range if we look at our income we take a look here we have income or expense these are called trans types so I'm simply going to copy this one here I want to use it in there I want that drop- down list and that's going to be the row source so right in here inside the row Source I'm simply going to paste transaction type and that's going to give us our income or expenses so when I click on here we see that we have both income and expenses and likewise I want to make sure that we'll create them and then we'll set the fonts to all of them so the category is going to be another drop- down list or combo box so I'm going to copy and paste it right down here so that's going to be our category and that category we're not going to add a row Source because it's Dynamic it's either going to be the income accounts or it's going to be the expense accounts all based dynamically to do that I also want to set the amount so what I'm going to do amounts going to be normal field I'm going to copy this and paste it down here and then the notes we're going to do here copy and paste we do want to update the names I want to make sure the names are very distinct remember we said this is called field one this is called field two so likewise I want this to be called field three so I'm going to copy this field and then I'm just going to Simply put in the three and I want them in order that's going to be very important because it's going to speed up our coding a lot because with the namings that we're used this is be called Field Four the amount is going to be called field five and likewise the notes are going to be called field six so they're going to have the six total field we also need some buttons for that and then field six lastly so let's enter some buttons here and so what I'm going to do this command button right here we're going to select on that and I want a button for Save and one for cancel so here I'm going to put save let's reduce this a little bit and I want to increase the font so giving this name called save button and I want the caption here on this to be called save so incap save I also want to update the font here to 12 the same as our title so we're going to set 12 and bold and click okay and then lastly what I want to do is we're going to make these transparent in fact all of these are going to be transparent so that means the background is going to be that transparent not opaque but transparent likewise for the buttons as well I'm going to copy this button contrl c contrl v this is going to be for a cancel button so we're going to give this a name called cancel button and I also want to give this the caption of cancel so very good I like the way that that looks again both of these buttons are transparent so not opaque I also want to background on this form and I want to make sure that these fonts are all 10 so I'm going to hold them down font here so let's go to 10 and I like that click okay I also want to make sure that there's no margin on any of these so these are going to go to false so everything's set up there we do have to set up the height notice the height so we see that the top position is 126 I'm going to set 30 pixels between them so the top position on this one let's take a look at this one the top position on this one will be 36 so I want this to be 66 so we can do that 30 pixels and that's how you set a nice different top position for each one of them so let me do that now that everything's been spaced out and named appropriately if you want to make sure that this is centered if we got two buttons we can group them then we can make sure they're centered so they're centered now and also would like to have a nice background for this now I have a JPEG background that I've already created so I'm just going to select on the user form here going to go back into the properties and we're going to go inside picture here and I'm going to select a picture which is this form background here it's a JPEG image and then also instead of clipped uh I want to put that stretched which is fine here and that looks good so again we change that to transparent here and uh that's looking pretty good so our user form has been created great so that's looking really good now we've got our form completed here and then what we can do is just save our work now once our form is created what we want to do is I want to make sure that anytime time somebody changes this our categories automatically updated so for example if we take a look and we run the form we see that we have income and expense now what I want to do is I want this to populate with either our income or our expenses now I have created some named ranges for this so let's take a look inside our admin we have two lists we have income types and we have expense types let's take a look uh let's go formulus name manager and take a look at those so we have something called expense types and I used offset for all those expense types I've got another one called income types so knowing that we have two named ranges both of those types we can use the combination to create a row Source based on when the user changes this and what do I mean by that it means whenever the user makes a change on the transaction type I want the category to update so when's that going to happen if I double click on here it's going to happen on field three change event if we want to get to that we just look for field three and then then we look for the change event here so when the field this changes I want to automatically update the field four dropped on this so how do we do that so if there's a change I want to make sure that the field three value isn't empty so if field three we can use me. field 3 do value does not equal empty then what do we want to do then I want to determine it's actually relatively simple because we have income and because we have expense we can . field for. row Source equals let's take a look at those again those name it's either expense types or it's called income types so that's it so if we know it's either expense types or income types the row source is equal to me. field 3. value so that value is going to be either income or expense and types so the combination of either this income types or expense is going to be the row source and that's going to happen every single time the value changes so that's all we have to do actually we can just put this in a single line of code if we want to do it like that and we can get rid of the end if as it is not needed very good we also going to take a look at the cancel button and this is going to be relatively easy so all we need to do in this case is just use unload me and what that's going to do is going to clear out and it's also going to close the user form very good now we're going to go back into our form we going to take a look here let's go ahead and run the form and it's going to open up the form we now see we have a drop- down list of transaction type for both and we see that we have the default income so we see automatically that category is changing based on both the income or expense that's been selected so it is a dyamic drop down list that we have all right very good so we're on to that continuing on so now that we have that we are going to go back into our income and expense and we see that we still have to add some information we have here our selected month and we have our selected year we're going to be using that let's go ahead and write the formulas in for that we see we've got our selected month number right here we've also got our selected year here so knowing those we can create a formula that's going to be used as our criteria for our transaction so the first thing we want to do is enter a formula so it's going to be equals then we're going to use quotation marks greater than or equal to and we're we're going to use a date then what we want is want to use that year now that year is our selected year our named range we're also going to use the selected month and we're going to use one as the first day of the year great now that's going to give us a number and that's exactly what we want so 45383 if we know what that number is all we need to do is write it in a new cell we can go up to the home and then format we can change that to a short date format and we see that 4124 so we see that the date is correct we have the right number now to get the last date all we need to do is just simply copy that and what we're going to do is we're going to update the formula in this case it is going to be less than or equal and we want to use the end of the month so to do that we're going to use the formula EO month so EO month and then what we're going to do is we want to know how many months ahead or behind which is going to be zero in this case so we're going to go ahead and put in zero very good and now we have 45412 so we can put that number in and we see that we have the last day of the month perfect so we know that our numbers are correct and we know our formula is correct next up what we want to do is we're going to have those results come in all the way from transaction ID date name category and amount and that's exactly what we want we want to have the results appear here we also want to have the results come into our transactions and we want to bring it down there so that's exactly what we're going to do we have the same columns in both and of course when we change a month or when we change a year we want those results automatically to to appear inside there we don't need the grid lines we can remove that let's go ahead and save our work and now what we're going to do is we're going to go into the VBA here and we want to insert a module so what we want to do now is we want to give it a name so we're going to go into the properties and we're going to rename it so we're going to rename it to transaction macros so once we have that inside there we can now go ahead and write some macros that can help us with those transactions and so the first thing we want to do here is dimension are variables so first thing is transaction row as log we also want to use the transaction column as log and likewise we want to have the last row as a long variable and also the last result row as a long variable and next up what we want to do is we want to also Dimension our field as a control that's what we're going to use inside our user form to define those fields first thing we want to do is write a sub so we're going to load the transactions that's the one that's going to be based on the date and when do we want to run this we want to run this anytime we're going to change it so we need to add a little bit of space inside column C we are going to have the transaction ID appear although we'll eventually hide it but we want that transaction ID to appear in column C and the rest of the data show up so to do that let's go ahead and start writing our macro we're going to use budget here range and then inside C5 we want to clear out all of that data all the way 99 so we're GNA clear out the contents of all the data then we're going to focus on the transaction database that is where all of our data is going to come so we need to determine the last row so that we can run our Advanced filter and that's Auto Haw key that writes it fast next up what we want to do is want to make sure that we actually have data so that is our last database row if the last row is less than four that means we have no data and we can exit the sub so no data all right continuing on we also want to run an advanced filter and that's Auto hot key that helps me out so we're going to make the adjustments here let's take a look at the original data we see that it's from A3 all the way through H so that's what we're going to do we're going to update that original data so let's customize the code just for that all right so back in our data we're going to change that all the way through column H then we need to focus on the criteria and that criteria is L2 through M3 so we can see right here L2 through M3 so we need to make the updates on that let's save a little more room here now what we're going to do is we're going to change that to L2 all the way through M3 very good we want those results to come into P2 all the way through T2 so we're going to make the change here P2 through T now that we've done that we do need to determine the last results row and that's going to be based on column P that's the ID that's required so we're going to make the update to column P once we have done that we then want to check to see if the last result row is less than three if it is we can exit the sub that means there is no results for the given month if we do have data I do want to sort the data so I want to make sure that if there's just one row of data we can skip the sort so if it's less than four we're going to go to no sort we're going to drop that down write out no sort with a colon next up we're going to put in the sort I'm going to use Auto hot key to automate this and so once we have that it's going to automatically write that for me to help me out we do need to set and update the sheet name of course our sheet name is the transaction database so we're going to copy our transaction database and we're going to write it there so that's the transaction database we need to update the key and the range as well actually we want to do it by date so we're going to use descending I want it sorted by date and date is located in Q3 so we're going to change that to Q3 we also need to update the range and that's going to be based on P all the way through T so we're going to make that update we also want to make sure the budget range and we're going to bring the data over C5 all the way through G and the last result row we need to add on to that because our original data starts on Row three and our data that we're bringing over starts on row five so we need to add two to that equal P3 from our transaction database all the way through T and the last row so P3 through T and the last result row do value very good so now that we have that we do need to save our work we can run the macro and we see that we have that so we have it so we see the earliest date to the latest date so very good just going to make that quick update to descending which is what I want and we run it again and we see that now it is descending from the highest date to the lowest date very good so our results are coming in and we take a look and we see that inside our transactions we do have our results along with our transaction ID inside column C so perfect and we also have our transactions here we have our conditional formatting that's running out and if we were to change the month to February we want to make sure that we automatically make those updates so how do we do that when we make a change either to E3 or G3 we want to make the change so what we're going to is we're going to copy the macro name we're going to go in to our budget worksheet and we're going to focus on a worksheet change when the user actually makes a change so we're going to select on the change there and we're going to focus on just those two Fields so the fields are E3 or G3 if user makes change to any of those we're going to load the transactions so we just need to update this to E3 and G3 very good so now all we need to do is just simply double click on that and we can see February's transactions so that's working good and we can change that back to January perfect so things are working quite nicely we see that those are just January's transactions if we're going to change the year we don't have any data in 2025 but we see if we change there's no data and back to 2024 where we have data so that our first macro is working great we're getting all the transactions that are coming up perfect things are looking really good now what we want to do is when I make a selection of any transaction I want the edit icon and the delete icon what we're going to do is we're going to group them together so we want to make sure that they appear when we select it we're going to give it a name called transaction group and that means when I select a transaction I want that group to appear inside column H along the same row that was selected so how do we do that it's going to be on a worksheet selection change event so we're going to write a selection if the target do count just to make sure if the user selects any large row is greater than one then we're going to exit the sub that means if the user selects a large number of cells it's going to exit now if a user selects any type of transaction we want it to appear so it's going to start out in D5 and it's going to go down all the way through G and a large row so we can write that up changing the range from D5 all the way through G and a large row and if they make a selection we want to do a few things we do want to make sure that D contains a Val you want to make sure there's a transaction there so the target. row. value does not equal empty once they do that then we want to run in some macros so what we want to do is we want to place that group and we also want to place the selected transaction ID into cell B2 so we're going to write that up also we want to make sure that if the group shapes are displayed we simply want to make sure to hide it so how do we do that if the transaction group is visible so we can write visible equals true then we simply want to hide it no matter what they select so shape transaction group do visible equals MSO false so we're simply hiding the shape if it is visible so perfect and now we only want that group of shapes visible if they select a transaction so that's necessary to do that so what we're going to do first thing I want to do is take whatever's in column C and the selected row and place that directly inside B2 so let's write that up range b2. value equals range C and the target row Dot value so that's going to place that transaction ID directly inside cell B2 very good so now that we have that I do want to place that group of shapes what we want to do is we want to continue on and make sure that it appears inside column H and whatever the selected row is so we're going to focus just on that shape so we can do with shapes transaction group and we want to do is set the left position now left position simply going to be based on column H and the target. row do left we also want to set the top position so the top position is equal to range H and the target row dot top okay so that's going to set that top position lastly we need to make sure that it's visible so we're going to set the visible property to True very good that's it let's get rid of the Extra Spaces as we don't need them and then what we're going to do is we're going to test that out and so we see that now our group of shapes appears directly exactly where we want we have the edit icon and the delete icon both appearing and we also have our transaction that has been selected very good so let's continue on we also want to be able to add a transaction we want to edit and we're going to be deleting so we need to write those three macros to be able to add edit and delete individual transactions so we're going to go back into the transaction form we see that we have our Fields here for the date the name transaction type category amount and we have a save button so when we click that save button we want to be able to either save new transactions or update existing transaction actions so to do that we need to understand that if B3 is empty we know it is a new transaction and if B3 has a value we know it is an existing transaction so that's what we're going to use to determine when we save it whether it's going to be a new or an existing transaction let's go back into the code here and we're going to also continue into that macro and the first one we want to write is to be able to edit that macro so let's go ahead inside our transaction macros module here and we're going to draw drop it down and we want to be able to edit that transaction so let's write a macro that's going to do that so we're going to write in sub transaction underscore edit the first thing we want to do is we want to understand exactly what row they have selected what is the database row if there's no database row of course we cannot edit any transactions so we certainly need to make sure that B3 contains a value so let's go ahead and write that in inside that macro if budget. range B 3. value equals empty then we need to let the user know message box please select a transaction to edit nice spelling Randy and we're going to exit the sub continuing on to make sure that we do have a value we want to make sure that we're can to use the transaction row that's the database row so we're going to use that and that's going to be what's in B3 so we simply need to just copy this and we see that our transaction row is based on whatever is in B3 that is our transac action database row very good once we have that we can then move on now we want to load all of that data into so from the transaction database into our user form so it's going to come directly from here so we need to Loop through the columns we already have transaction ID but we need to bring in all the data into our user form it's going to go all the way to column 7 so from column 2 to 7 we need to create a loop we already have the transaction ID there so we don't need to add that we know that that's in B3 so let's go ahead and write that up inside a loop for the transaction column equals 227 we need to close our Loop next transaction column inside that Loop what do we want to do first thing we want to do is I want to set that field so we have that field control so what we're going to do is we're going to set the field it's going to be equal to the form so we need to call out the transaction form do controls and what is it it is field based on the names and our transaction column minus one since our field starts at one so transaction column minus one that is our field so once we set the field remember our transaction column start on two but our field names start on one so we need to of course make accommodation for that once we do that we can then bring in the data into our field so we can simply do transaction database our cells what is a cell it's going to be our transaction row comma our transaction column do value and it's going to be our field value so all we need to is set the field. value is equal to that field. value is equal to whatever's inside there and that's simply going to bring over the data from our database into our user form using that Loop and it's a nice way we can use many fields with this Loop and then all we need to do is display the form using transaction form. show and that's going to be the edit so we want to tie this macro to that icon if the icon is small what we can do is we can use our selection Paine so I'm going to click on this and then what we want to do is we can set the icon and the picture we can hold the control down and select both of them and then what we can do is we can use right click assign macro and then we can paste in the macro that we just wrote click okay and now it's assigned so now saving at work before running any macros we're going to select it and we're going to edit it and we see that the information has loaded up directly from the transaction so things are working very good all right great so that's good we can cancel that it's going to close out let's try another one edit all the information from the transaction has loaded into the user form very good now what we want to do let's go for adding a new transaction so subtransaction add new this is relatively simple all we need to do is just make sure that we clear out B2 B2 is going to tell us whether it's a new or an existing so as soon as we clear the B2 field we know it is a new transaction so we can go ahead and clear that out so B2 clear the contents of that that'll signify that it is a brand new Trans transaction once we do that we can simply display the form and it's going to be blank and that's exactly what we want so transaction form. show very very simple there and let's go ahead and assign this macro to the button that we've created for adding the transaction so we're going to right click assign the macro and then add transaction pasting that in here now when we click it we see that our user form launches and it's blank and also we see that B2 has been cleared out which is exactly what we want now what we're going to do is we're going to write a macro that's going to allow us to save the transaction and we need to know whether it is a new transaction or an existing we see if it's an existing B3 and B2 have a value however if it is a new transaction B2 and B3 will be clear so we can use that as our differentiator when we write the macro so let's do that sub transaction save update because we're going to use it for both the save and the update we do want to make sure that they've filled in the correct fields we want to make sure that we have the date transaction name transaction type category and amount are all filled in so we need to make sure that those fields field one all the way through Field Five have value so that's the first thing we want to check those required Fields so let's do that so we're going to focus on that transaction form and we're going to run some checks if field 1. value equals empty or Field 2. Val equals empty or field 3. equals empty or field 4. Val equals empty orfield 5. Val equals empty then what we're going to do is let the user know to please fill in the required Fields with a message box please make sure to fill in all required Fields before saving this transaction very good we can exit out the sub because they need to fill in the required great so once we do that now all we need to do is determine is it a new transaction or is an existing transaction to do that we're simply going to write a little bit of code if budget. range b2. value equals empty then we know it's a new transaction else it is an existing transaction and then we make sure we have our end if very good so now we've got that if it is a new Transaction what do we want to do I want to make sure that we take that next transaction ID located in B4 I want to place it in B2 and I also want to place it in column A of the first available row so we're going to go all the way down locate that first available row and place that transaction ID right in column A that's what we need to do for New transactions so let's go ahead and write that up so we're going to say budget. range B2 is equal to whatever's in B4 budget. range B4 do value it's going to take that new transaction ID from B4 and place it into B2 next up in column A of our first available row so transaction row is going to be that first available row a and we'll use a large row end XEL up. row + one that is our first available row once we know the row we can then write it in in column A of that and the transaction row. value equals what whatever's in B2 budget. range b2. Val that is the next transaction ID so value next transaction ID very good that's all we need to do for New transactions however if it's an existing all we need to do is take that transaction row whatever is located in B3 and place that in our variable so we can just simply copy this and then we're going to make the update to B3 very good so we're going to make that change that is our existing transaction database row let's go ahead and write that up inside a note existing transaction row so once we have that everything else is going to be whether it's a new or an existing and it's very similar to this so we can simply copy and paste that and make the adjustment it's the same loop as we did before so we're going to set those up bring those out a little bit so setting the field is exactly the same no different however we're simply going to reverse it so whatever's inside the field we're going to bring inside the database so again all we need to do is reverse it so we're going to place the equals can just drag that and put it right here after the word value and that's it so that's going to take all the information from our user form and save it into our database very good so that's going to be good for both saving new and updating existing so now we have load transactions we've already created that macro here so we can copy and paste that and also we see we want to reload the transactions once we save it so every time we save an existing or new one we want to reload the transactions and of course we're going to hide our user form we don't need that so we're going to reload the transactions very good saving our work and continue on now let's go ahead and copy that and I want to go into the user form view the code I'm going to select on that save button so we have the save button and I'm going to paste in that code that means when we click the save button we're going to run that macro let's go ahead and give it a try so we're going to go inside here we're going to go to existing transaction we are going to edit that transaction clicking on the edit and we can just simply make a change and then we save that change and we see that the value has updated to 150 very good so we see that that's working very good and let's try adding a new transaction so we'll click add a transaction and we can put in a date here 120 and 2024 we'll give it a transaction name we will just put in me me S and we'll do a transaction type as an expense and we'll select water now the amount we set here to 100 so we see that once we save it we see that automatically it updates and that new one has been added at the top very good now back into our module we go to write that last Macro for deleting so we're going to call this subtransaction delete first thing we want to do is we want to let the user know if they want to delete it so message delete I've got that here automated you want to delete this instead of appointment we're going to change that to the transaction and along with the title to makes things a little bit quicker transaction giving the user the way to opt out if budget. range B3 which is our row. value equals empty then exit sub without a row we can't do anything so transaction row is simply going to be equal to here once I have that transaction row then I have a row that I'm going to be deleting inside our database so this is our transaction row once I have that we can then delete it so it's going to be inside the transaction database. range our transaction row and colon and transaction row. entire row. delete so we're deleting that once I delete that I also of course need to refresh and I also want to clear anything in B2 so our budget. range b2. clear contents clear selected transaction ID once I have that we need to load the transaction we can copy this and reload the transactions to clear it out we also want to make sure that this group here we don't need that display that transaction group must also be hidden so budget do shapes transaction group. visible equals MSO false so hide transaction group great so that should be pretty much it that's all we need so we're get saving our work here we're going to assign the macro this transaction delete it's going to be assigned to those buttons again so when I select on here it's a little bit small to select individually so what I can do is go into the shapes here and then again we're going to take a look inside here which is these two shapes here it's holding down the control again right clicking anywhere within that and then clicking the assigned macro is that delete so this is the current workbook that's the only one I want delete is the one we're looking for so again let's go ahead that one we just created I'm going to select on it here going to delete it are you sure you want to delete this transaction yes so it's now deleted it's hidden it's gone if we look in the transaction database and we go all the way down here it's no longer here so that's working perfectly great so we able to update delete and add new transactions so we're good on everything like that now we're ready to add in our graphs and charts for our budgeting all right let's go over some data that I previously created to help us move things along a little bit faster so if we take a look inside here we've got some current year and current month information right here I've got a list of months just simply a list of months and what I want to know is I want to know the income budgeted Insider admin we have a monthly expense budget and I have a monthly income button I've given those named ranges this is called budget expenses this is called budget income that way when we change it it automatically changes so the first thing what I want to do is I want all of the monthly budgets for every month individually of course it's the same value so all I need to do is just say equals budget income and then just bring that down so that's kind of relatively easy now what I want to do is I want to know the actual income and I can use this with some if so here just to help us move things along I've got a transactions amount remember the transactions that's what we're summing our transaction data and the amounts so we can see all the amount based on a type now that type must be income if we take a look inside our transaction database inside our type we have income and we have expenses so I really want to focus only on income and I want to focus based on a specific date I only want it for January or I only want it for February how do I know if it's only January well we can take a look we see this is in row six we see the February's in row s so how do we extract January from row six we can subtract five if we subtract five it's going to return one one is the correct month likewise in row seven February if I subtract five I'm going to get month two so that's a great way to extract the data and that's exactly what I've done so again this time our criteria range is the date and it must be greater than or equal the date of the current year I wanted the current year the row number which is 6 minus 5 is going to be one so that's going to get us our month number and then one which is the first day of the month so it's going to be greater than or equal the first day of that January and it's also going to be transaction date less than or equal end of month the last day of January so that's all the reason we're doing it this way is all I need to do then is just bring this down like that because the month number is dynamic the month number goes from one to two all the way to 12 just like that so that's the only thing that changes so that's how we get our actual amounts of income for the given month likewise in expenses is exactly the same however our budget is equal to the budget expenses so that's again what's tied to whatever this expense so if our budget expenses changes to 27 50 it's automatically going to update at all of these fields likewise when I want to add up all the actual expenses for a given month I'm going to do something very similar the only difference is January in this case is in row 21 so therefore I must deduct 20 from our formula and that's exactly what I did so it's our current row minus 20 so if it starts in January it's can get us one and of course the only difference is we're focusing on expenses our transaction type is expenses and that's what we're doing we're summing if all the actual expenses so once again all I need to do is bring this formula down here and it's going to extrapolate all the expenses for the given year for all the individual months and I have no data for the last three months of the year very good so we understand how we get the income versus the budget for the given year and the expenses budget for the given year I also want some additional information for the current year so for annual I want to know the 2024 current income however I don't want this fixed as the year years change I want this title to change so to do that we just need to set the year of the current day so that's the current year always and then the word income and then also I want the current year and the word budget so that's going to help for our names I also want to know if we're going to be above or below the budget and so to do that we need to use some numbers so the first thing is our 2024 total income is simply the sum of all the actual so I want to know that actual income I also want to know what our budget is and again I simply have to multiply our budgeted in time 12 I could add it up here but that's just as easy so we see for the 2024 budget we budgeted 42,000 but our income was actually above that at 48,000 and I also want to know if we're going to be above or below and I would like this to be dynamic and that means that if we've budgeted a lower so for example our budget let's say it's 5,000 and now we're actually below the budget right so I want this text to be dynamic to change notice that we're actually below the budgeted income so how do we do that well what I want to do is I want to look here if our income is less than our budgeted income I want to put below above or at if it's exactly the same then I want to know that too so for example if we see here if I change this to 4,000 we're going to be exactly at our budget so I want that to be reflected as well so they're the same so I want to set at budget so here if af7 is greater than one then I know that we're above the budget if AF 7 equal 1 then we're at the budget or we're below the budget so I'm just going to use this number here now this number is simply a division of af5 divided by af6 so we're simply dividing our income divided by our budget it's going to get us that so we're either at above or we're below budget so this text is going to be dynamic based on that and we can use this text in those shapes to be reflected very good so we see how we're going to be doing that so we're going to change this back to 35 now we see that we are above budget 114% we're going to do exactly the same for expenses so again we have our Dynamic names for our year expenses and budget we have again our total expenses based on the expenses that we have we have our budgeted expenses and whether we're above budget below budget or out the budget we're going to use something very similar great so we're going to use all this information inside our shapes here it's going to be dynamic next up I want to know some current month information once again I want to use a dynamic name I want that month name to be dynamic so whatever month it currently is it's currently June while I'm recording this so I want to make sure that that is reflected to do that we're going to do the months I want to use an index we're indexing all the months we created that named range based on the month of the current day it's currently 6 so the sixth month right so the month of today is June and so simply it's going to extract that indexing our months determining the sixth position which is June and extract that and then we're going to put the June and then actual income and then likewise we're going to do the same thing except this time we're adding budgeted income so that as the month changes so will this names once again I want to then determine the amount of income for the current month and we're going to use something that we just did we're going to use a sum if just like we did we're summing all of our income based on the date but this time we're going to use the month of today the month of today is going to get us that sixth month and we're also going to use the less than or equal the end of the month so again the month of today is six we're using the end of the month so what this is going to do is going to capture all the income for the given month next up I want to know if we're above or were below the budgeted income so I want another Dynamic text here so if af19 is less than 20 I know we're below so that means if our actual income is below the budgeted income then I know we're below the budgeted monthly income otherwise if it's greater than we're going to write above budgeted monthly income and then if not we're at so we're either at above or below the income so we also want to know how much we're over if for some reason af19 is less than af20 we're going to take one and subtract it from the division of af19 through af20 so we're simply subtracting and dividing to determine how much above or below the monthly income was so for example here we are 157% above it so if we change our monthly budget to 10 ,000 we see that we are going to be then below so now we're 10% below the budgeted income so that's a great way to demonstrate that dynamically that we can do that so again for the current month expenses it's identical to that except we're simply adding the expenses in this case for the current month and we're determining the expenses based on that budget and then we're determining whether we're above or below that so here we see we are 58% above the budgeted monthly expenses here because our actual expenses are more than 50% above that so we can see that so now that we have all that information we want to make these tiles and shapes Dynamic based on that so how can we do that well the first is I want to know what our budgeted income for June is so to do that I simply want to link June budgeted income so I want to link the shape now as soon as we link it our format's going to change except we'll format everything at the same time which is a lot easier so I'm simply going to write in equals and I want to know what that June budgeted income is so we're going to set it to right here so the June budgeted income Dynamic text is right here so we're just going to hit enter and I also want to know what that amount is so that amount also we are going to link to whatever that budgeted income again we're going to change that back to the font that it belongs so we're going to do equals and then simply that text box is that June budgeted income which is right here at 35 great I want to know what the actual income is so now it's just simply linking up so equals here that actual income is located right here and then also we want to know the dynamic text for that actual income so we're going to do equals and it's this actual income right here also with the budgeted expenses we can imagine that it's similar so again equal to the June budgeted expenses which is first we'll do that here and those budgeted expenses we'll select on that text box and we want it linked to the individual cell equals which is our budgeted right here next up we will going to focus on the actual so here equals and then what we want to do is the June actual expenses which is located right here that Dynamic title which will change with the month next up the text box we want to link with those actual current month expenses so we can do equals here and then those actual expenses now again we want to know whether they're above or below that income so again this shape is going to be equal to above budgeted month going to put that right here that way it changes and we determine the amount that above budgeted monthly income is simply going to be equal that tax box right here I want to know the percentage of it so again equals here the above monthly income which is going to be right here that 157% all right next up we're going to focus on the expenses equals here then what we're going to do is located on that monthly expenses which is above monthly expenses that text right here and next up which is that actual percentage amount of how much we're above in expenses or below which is going to be this one right here great so now that we have everything link we can then reformat these and update those so I'm going to use my selection tool because everything's going to be similar and then I'm going to select all the shapes I'm going to change that font to White so we're going to select on the white I'm going to select it bold and I'm going to increase the font here and let's bring it down to 13 on that one however our numbers I do want to increase those numbers so I'm also going to use my selection tool this time I'm going to focus just on those numbers here and I'm going to bring those up those are going to be about to 14 so let's do that and we'll set those to 14 or we can go even a little bit higher that looks really good so saving now what I would like to do is I would like to group these and shape them accordingly so I want to make sure that I'm going to Center and group these individually and doing each one of those ensures that there's equal spacing between two of them so we want to make sure that both the text box and the shape is centered together and then grouped together so we're going to do this individually then what I'm going to do is I'm going to space them accordingly so I want them equally spaced out and then what we'll do is we will group it together and the last one we're going to make sure that we're not spacing I'm going to use my selection tool now I want those spaced accordingly make sure they're all lined up I'm going to group them completely as a group and then I want to make sure that I'm not going to change the size so I'm going to use control one I'm going to check inside the properties here and I want to move but don't size so things are looking really good I like the way that that looks now what we're going to do is we're going to focus on some graphs we also want to hide this information we don't need to see these numbers so what I'm going to do is I'm going to select on the entire column I'm going to go into the home and I'm going to go into the more number formats and go to custom and I'm going to use two semicolons since only numbers we can use two semicolons if it's letters and numbers we're going to use three semicolons and what that's going to do is going to hide the numbers now they're still there if I select on it we can see the number there but it's hidden we don't need to see it saving our work so far now what I would like to do is based on our monthly data I would like to add in a bar chart so how do we do that well what I would like to do is I'm going to focus one on the ink so I'm going to select on here and then I'm going to insert here and then we can use a bar now I'd like to use probably this one here this one looks good this 2D bar chart would look pretty good I like the way that that is that's the right one and we'll just customize that so we're going to bring it down here a little bit like that making sure that it all fits in the existing screen we're going to maximize the height and then we can format that so now that we have that idea I do want to make some updates accordingly the first thing what I want to do is I want to let's see RSE the order I want January 1st so control one to do that we're going to select on here so let's select categories in reverse order that's going to be January 1st very good so we're going to set the chart title now the chart title it's going to say this monthly income versus budget so monthly income now we could link this also versus budget that looks pretty good and we can stretch this out to make sure that it incompasses all right I want to color all the font inside that so I'm going to format this we don't want any fill on that so we're going to remove no fill I don't want any outline on that so we're going to do no outline I also don't want any of this here we don't need that one so this particular access I really don't need so we can hide that again we can customize it here the access here we want the chart title The Grid lines we don't need any of the grid lines the legend we can keep here so the axis here let's take a look here so we don't need the primary horizontal axis so we can remove that looking good I would like to format that I want all of our font to be the same color so we're going to select on a unique color here for this font and then we can set it to bold very good so I like the way that that looks let's bring it over a little bit here inside the individual chart it's looking good but I do want to increase the font here so that our months show up just a little bit so we're going to select on the individual months and increase that to 10 that's good we're going to focus on the colors here so our first one is our budget so this one right here our budget I would like to be a light blue so we're going to look in the fill and we're going use a gradient fill here I'm just going to use a two color fill so I'm going to select on here and we're going to use this lighter color here so I'm going to use these two colors here and here so it's a little bit lighter for our budget and I want that here going to set the dark on the left and right so a little bit like that I like the way that that looks good so we're going to also set that to a pipe here so if we take a look at that we can set the series Gap and overwidth that's okay for now but we'll customize it a little bit so now what we want is the actual so the actual I'm going to give it also a gradient fill but I want that as a darker color so we're going to select on this one and I'm going to use these darker colors right here and then we'll set this to this so that's looking really good so we can differentiate between those two I do want to show labels here so we can add those data labels and I also want to format those on both so we're going to use the actual and then right click here and adding those data labels I do want to format those so we're going to select on some we're going to look in here and we're going to go down to number here I want those Cur currency so I'm going to change those to currency but I don't want any decimal places so we're going to just highlight that and press zero it's going to save some space likewise I'm going to do exactly the same to our actual so again I'm going to select on this then what we want to do is I'll select right here and go down into our numbers down here and do the exact same thing so that's going to be also set to currency and then we're going to also highlight the decimal places and put zero so things are looking pretty good so far let's take a look here and add a little bit of formatting maybe we'll want that pipe look so let's do give it a 3D look let's update the look and feel of this so we're going to right click here we can now format the data series here let's go through a cylinder I like the way that that looks so we're going to give it a nice look and we're going to do the same thing for our actual so we'll select on here and we'll select the cylinder so that looks nice very good we're just about done with this we can raise it up a little bit we'll also add a background to it I want it a little bit raised up we don't need that Gap there looking pretty good so as we see we've got our data we can increase these fonts just a little bit so they stand out 10 is a good idea and we'll do the same thing for our budgeted so we're going to set both of those to 10 once we get this just the way we want it we can then duplicate it and use something similar for our expenses so I'm going to use controll D which is going to duplicate it I'm going to drag this over here although we will need to make updates accordingly this one is going to be called monthly expenses versus budget so we're going to update that and we also want to update the data of course accordingly because it is based on expenses so let's go ahead and select that data and we see that we've got that selected the data here but another way to do that is just simply bring this down here this is going to be focused on our expenses so I'm just going to drag this all the way down here so let's bring this down here all right that's fine just the way it is so we're going to into that now what we want to do is we're going to update the colors accordingly monthly expenses versus budget so our budgeted here which we have first is we want to make sure that that colors are a little bit light so we to use control one here and we're going to go into the fill and I'm again I'm going to use a gradient fill here but I want to set the colors to that light for our budgeted so this is going to be our budgeted going to update that first color to a little bit lighter orange which is like this and then I'll set it just a bit lighter color like this so I like the way that that looks and then our actual is going to be that darker color so I'm going to select on that our actual and we're going to then scroll out up and go to a gradient fill this one's going to be a little bit darker orange so I will select on the single color I'm going to update it to that darker color and we're going to go into that little bit lighter color so that's going to give us that nice look very good so now we have the expenses versus the budget we certainly need to add those data labels so I'm going to go ahead and add in the data labels here and I'm going to also add in the data labels for our budgeted expenses here so adding those data labels here we also want to format them just like we did before so going into the text options here and we want to go into the text let's select on the individual ones or we can go in the label options here num what I want to look for going back to the currency and then just like before using the zero for the decimal places doing exactly the same thing we can also format that accordingly to based on the source but this works just fine so once we get the hang of it it's a little bit quicker so we can see that we have our monthly expenses versus the budget and everything's looking good we have our budgeted which is a lighter on our actual which is dark very good saving our work so far I would also like to add in some donut chars based on some data now the first one is an income versus budget for 2024 so I want to focus right here on this value here and we're going to insert a doughnut based on that income and what we're going to do is we're going to insert we're going to use this piie but I'd like a donut for this and we have here now I'd like to put a dynamic title on our title so I'm going to use equals here and I wanted our 2024 income versus budget so I'm going to select on this one right here that's it so now we have a dynamic title we can update that so we also need to add some data labels on it I want to select the data and I would like to add the categories so not only do we have the data labels here but I want to show the categories as well so we can select on any one of them select here label options and we want the category name as well because I want to know the budgeted versus the actual income and a donut form it's a little bit big so we don't need it that big we're going to have to put in two of them within this space and so to do that we can reduce the size of it and we're going to check our sizing here let's bring this over here and uh we also need to format that accordingly so I'm going to put in about two here so I want it smaller we can also increase this a little bit here we have some extra room so I'll do that and make the update and we can just ungroup this because I want to bring it out a little bit I've got some extra space we might as well use it so I've ungrouped group this and then what again I'm going to just create a little bit more spacing between this maximizing the space regrouping it and then of course the properties here and just making sure we're not moving so we have it here so I'm going to select on here and we take a look inside here I want to make sure that a little bit or less on the dut hole sizes but we do need to want to add some information into that so now that we have that I don't want any line so we'll select no line on that I do want the fill consistent with our colors that budget is going to be that light blue so I'm going to select on the budgeted here I'm going to use a gradient fill here and we're going to use that same light blue consistency color here something like this and we can select on this so we're going to use a light blue gradient for our budget and then for our darker we're also going to use a gradient fill on this but we're going to go with the darker color which is consistent with our theme here and then we can use these two colors so I like the way that that looks I do want to give it a little bit of a 3D look and we need to stand out so I'm going to select on this I'm going to look in the shadow I do want to give it a bit of a shadow on this and also I would like to do a 3D format on the edges it's just a bit and so we can bring it down here and we can give it a nice bevel here so that's looking pretty good we also want to update the font consistent with the color that we're using on this so inside the home we can set the font here and we can set it to bold here and we also don't want any background on that so the shape fill is simply going to be no fill we don't want an outline on that so we'll do no outline on that we don't need the legend so I can just select it and delete it things are looking updated quite nicely so I'm going to bring it up here maximize the area that we're using here and we can also reduce the whole size just a little bit so things are looking really nice as we add in the information I'm going to select on the plot reduce that whole size just a little bit I want to add some information inside here I want to know whether we're above or below the budget and I want to know what percentage so to do that I'm going to select on the individual shape and I'm going to insert a shape and I'm going to use a text box here and I'm going to just put that text box right in here now inside this individual text box as I select here oops let's do that again let's insert the shapes and the text box if we don't add some value in real quick it kind of disappears so let's add the value in just temporarily what I want to do is I want to make sure there's of course no fill and no border on that I also want to make sure that it's consistent with the font color that we're using which is this dark blue color and I also want to make sure that this is linked to the actual percentage so we're going to set the Bold here and also I want to go to the text options we don't need any spacing on these so I want to make sure that the left margin the right margin is nothing that's perfect and I want to also Center it now that it is what do I want to show in here I want to show whether we're above or below the budget and I want to show the percentage so the percentage of whether we're above is right here so I'm going to select on here equals and then that percentage that's exactly what I want to show show and of course as soon as we do that of course our formatting is gone away but that's okay I'm going to then duplicate this using controll D and I want to put a description down here whether we're above or below it this one equals is going to be whether we're above or below which is above budget right here so that's that Dynamic text bringing this down here we need to maximize this because I want to make sure that it's nice and big so we're going to show it and we're going to also increase the font I'm going to hold down the control again go back to our font color which is this and I want to make sure it's bold and we're going to increase that a little bit very good so now we're going to add that in so we're 114% above budget perfect so I like the way that that looks I'm going to Center those and I want to make sure they're centered inside this things are looking quite good here we can bring this out a little bit here now that I like the way that that looks what I would like to do is I would like to duplicate that and use it for my expenses so again we can probably group everything together here I'm going to Center that I'm going to group it and now I'm going to duplicate it using contr D and we're going to bring it over here we're going to use this one for expenses oops let's grab everything here I want to grab the entire group inside the text as well perfect so we can ungroup it temporarily and let's move these over let's get those nice and centered they got moved up a little bit next up what I would like to do is this is going to focus on whether we're above or below our expenses so this is simply going to link our above budget for our tax which is right here our expenses are here and also we'll format those and this is the percentage this is 117% linked tier whoops let's fix that not minus it's going to be equals now that we have that 117 we can take a look at this we see that the format on this is set to 14 bold so we're going to do exactly the same thing here holding down the control going in here and making sure it's bold and then setting it to 14 I think the color we'll make sure that color is consistent we want to update the data the data of course is not income it's focused on expens expenses so we're going to bring this down and focus just on these expenses here that's exactly what we want of course it's not income it's linked to expenses so we might as well just link this to the dynamic one for 2024 expenses versus budget which is right here things are looking good now let's go ahead and format that according for our expenses so I'm going to use control one here we're going to go into the fill here and I'm going to use a gradient fill here I'm going to use no line on the border here also on this one this is our budgeted expenses here so we do need to add the data labels make sure that we add those data labels selecting on this one bringing it out here I also want to show the category on this one category name doing the same thing for here adding that data label and just bringing that data label over here and also we want to make sure to show the category on this one too so selecting on that and the category name now that we have the category name we can bring it out we need a little more space for expenses things are looking pretty good here we've got a lot of information let's update our fill effects so we want a gradient fill for this one however since this is our budget that we're focused on right now so we want that a little bit lighter so our first color is going to be that light maroon here and our second color is going to be that little bit lighter color here that's going to be that now for our actual expenses we're going to use the dark maroon which is going to be here and then we'll use the darker orange or maroon color here so that looks pretty good again we're going to format it just like we did before here running the format I also want to make sure that there's no line on the border here formatting here we do want a 3D bevel on here and we don't want a shadow on here so that it matches the style of the other one so things are looking pretty good our expenses here we can set these to bold making them a little bit bigger here increasing the font let's do that a little bit and I also want to increase this one so that they're consistent both of them have the same font and we'll both set to 10 things are looking really good last thing what we want to do is I do want to add in a line chart that reflects the given income and expenses on a month per month basis Let's Line these two up to make sure that they're lined up things are looking good and we'll also add a background on that just so that it's a little bit easier to see lining these things up actually sometimes if we don't group them they line up quite nicely but that's okay we've got everything so let's add that line graph for both our income and our expenses so we can start out with just one here in this line chart I would just want to reflect the actual expenses and our actual income so I'm going to hold down the control and just start on the income and I'm going to do insert and then line chart and we'll do this line chart here very good bringing this down here I also want to add in the expenses so what I'm going to do is I'm going to select the data and I'm going to add one additional entry here and we're just going to call this actual expenses and then the data for that is going to be based directly on these actual expenses here and clicking okay very good so I'm going to rename this one this one I want actual income so we'll call this actual income all right very good so we want to show some data labels and click okay so we're going to right click here and then I want to add in those data labels and I want to do the same thing for here right click and then adding those data labels here we also want a chart title on that we can select the chart title I want to know our 2024 expenses and budget so we can use a dynamic label on this one let's go ahead and make that it's a little bit easier if I do it so what I'm going to do is I'm just going to copy this here I'm going to place it right up here just anywhere is fine so I'm going to paste it in here so we're going to do call this actual expenses and income so actual income and expenses I like that and then we're going to use this this is going to be our Dynamic title so that we have the current year it's always going to so selecting our chart title we're going to use equals here and it's going to be our 224 actual income and expenses selecting on it let's format that accordingly so we want to go in let's do bold selecting all the font let's stretch this as much as we can we're going to need a little bit of additional space here so we'll bring it all the way to the end going into the format making sure that we have no fill and no border on that also when need grid line so I'm going to select on the individual grid lines and delete we don't need that the colors are just about there let's select the colors here for our expenses I do want to update that so we're going to use control one I'm going to use the fill and uh we'll use automatic but I do want to set the color to this dark color which is consistent and if we want to use a gradient line we can but we'd have to set it up so the solid line is sufficient for us same thing here I do want to set that that color should be okay right about there very good so we're going to format that I want to show these above so if we take a look inside here our label options and I want to make sure they're set above it and I'm going to do the same thing here with our expenses I do want to set that above all right very good we do need to format those so we're going to go into the number here and just like we did before we're going to set the currency and the decimal places are going to be zero so we'll set those we're going to do exactly the same thing here with our income again going to currency and then selecting zero so things are looking really good here we can customize this a little bit more but I think it's a good start 200000 actual income it's showing everything what I'd like to do is I want to make this a little bit more clear so it stands out our graph to do that what we can do is we can add in a shape behind it so I'm going to click on shapes and you can use this rounded rectangle and I'm just going to highlight over all of this here and then what I'm going to do is I'm going to reduce the corners to a small I want to make it white so we're going to do the shape fill is going to be this white I'm going to use no outline on the shape I do want to set the transparency obviously so we're going to go to more fill colors so we can also use control one here and we're going to set it to about 80% transparent here or 708 that looks good I want to send it to the back so I'm going to use send it to the back now everything stands out quite nicely on that we can change the transparency if we need it but it's looking pretty good all right very very cool in this training we created this incredible income and expense budget with the ability to add new transactions we're also able to edit and delete existing transactions with this fully Dynamic mini dashboard showing our budgeted income and our budgeted expenses along with the actual for the current year and the current month in many forms thank you so much don't forget to grab my brand new 350 workbook pack that is in massive collection probably the world's biggest library of excel done for templates I do hope you'll grab that I'll include the link down below I've got a special promotion thank you so much for your continued support and we'll see you next week [Music]