Learn How To Create Your Own Monthly Budget Application In Excel From Scratch Today [1 Hour Course]

hello this is Randy with Excel for freelancers and welcome to the personal monthly budget in this week's episode what we're gonna do is we're gonna be creating a personal monthly budget all from scratch we're gonna show you how to create your own budget using a very little bit of VBA code some really amazing charts a very cool drop-down shape based semi-transparent pop-up menu and a bunch of other really cool things so I can't wait let's get started alright thanks so much for joining me today I've got a really cool very unique training something we haven't covered before a lot of new features if you're joining us for the first time make sure you hit the subscribe button below I create these videos each and every Tuesday absolutely free you can get the downloaded workbook just check the links in the description below always for free click on any of those links either with your email or facebook Messenger we create these each and every week for you so it's a lot to cover today so we should get started right away before I do so I want to make sure to let you know during these difficult and strange times nothing is better than controlling your own destiny your own finances in your own future the best way to do that is take your excel talents and create automated reoccurring income and I'd love to do that I've been doing that for many years and I want to show you just how you can do that through our Excel for freelancers mentorship program a mentorship program I started a few months ago we're gonna be creating an amazing accounting applications every step of the way I'm gonna show you how you can define design develop and deploy your own excel applications our students are loving it gaining brand new jobs and a brand new confidence on how they can create their own applications to sell on the marketplace so I hope you'll join us there thanks so much let's get started with the application in this application what I want to do is I want to create a very simple very easy to use personal monthly budget so let's get started I'm gonna oh this top row all the way I do have a few things all I have is a list of types of basically expense types and I have a list of months that's all we have just to make things a little bit quicker but that's all I've done so far just trying to make things easier I do have a list of expenses based on those types here's just a small table of expenses that's gonna help us move things along there's no code no formulas nothing else we're gonna do everything from scratch right with you so let's format this top so I'm not gonna go with the fade on this one just a basic color green keeping things real simple and I'm gonna do that actually I got let's clear that out we don't need that and I also want to clear this top bottom row put that as greed - so the idea is to create the first row but just put a title on that something we'll call this personal monthly budget I think that's an appropriate title and I wanna obviously if I've got a green font I'm gonna put a white font there with a green background make it bold and then I'm gonna use one of my favorite font switchers Arial routed this kind of clear and nice so let's select that that's one right here something you can use - and then I'm going to increase the font probably to about 26 or so that should be good I'm gonna merge and center that across just about here and we're gonna put in some icons - so the idea is the first thing I want to do is I wanna we need to know the year so I'm gonna put it in the year here and then I'm gonna have this b2 used for the year and the next one I'll move one over let's go with D and I want to use just to select a specific month so I'm gonna put the month in here let's just say we have January here and then we have let's say 2020 here okay so we get an idea I'm gonna write justify these two and then left justify the month in the year here so we can get a little bit clearer I also want to make these bigger a little bit bigger just and also white font too obviously we need to clear it out and make them big 14 should be perfect here so basically I want to select them let's make that a little bit more clear bold we'll get it done okay so and also I want to know the monthly budget right we have to be able to set a monthly budget so let's put that in here monthly budget and then whatever the monthly budget would be let's just say 3,500 so that gives us and I'm gonna format this as a currency because we want that and I'm also gonna left justify it here actually we'll make it a currency here and then left justified and this right I'm gonna write testify again also fourteen on the font and white font and bold on that so it's consistent with everything else we have alright so basically the users gonna be able to select the year select a month here and I'm gonna make a really cool I'm not gonna use a drop-down menu as we always do I'm gonna use a pop up shape based very something very cool something very unique and then users will be able to enter whatever monthly budget they want here okay so that's gonna be great everything else is gonna be white here kind of basically sim and what I want to do is I want to create a table here of all of our expenses and then the amount here probably so let's let's do that let's copy over those so we know what those are I'm gonna copy them over here I just want to know I want to list them there's gonna be two different lists one for the graph data and one for our other data so I want to show you that and I'm gonna put some icons in there okay so I ten what I'll do is I'll paste in those values and that's just gonna paste on all our expenses and what I'd like to have is the actual expenses here based on the date that they select and based on the year that they select so I want that all in here we can bring this over a little bit so the idea is to have totals here a graph here put it maybe like a donut chart here based on those and then I'm gonna put some icons so let's bring in those icons because I would like to have some icons that also show you so I've got all these icons based on the categories that we've inserted and let me just size those down appropriately and let's say point four should be sufficient on both the height and the width that'll give us a nice look that's big enough and then I'm gonna bring those over here one of those this dollar said I'm going to use for this here but we do need to change it the way I guess I could leave it it's kind of a nice color all right we'll leave that that kind of looks nice just like just like it is we don't need to color it white it stands out nice so we have personal monthly budget we have all these icons so let's set this up in order and then basically I'll show you what I want to do so I'm gonna put in just I want to try to keep it as consistent as possible so the auto would go up here and I would like to have the utilities here just basically in the same order that it is currently all right let's make all the widths the same that way all the heights can be relatively different than the width so let's do point four and then the heights can be different okay so basically what I want to do is I'm gonna have let's say I've got twelve different icons so I would like to put something like four across by three down now I'm not gonna do all this aligning with you watching because otherwise would be boring so let me go ahead and align these and then I'll be right back okay now that I have them aligned basically I've got twelve different icons representing the twelve different categories here the last one being unrecognized that's gonna be this everything else to obviously auto utilities bills everything's here so under each one of those what I'd like to have is a total and then under that I would like to have all so basically what it is the description of it so I'll go ahead and add some more text based shapes here and this one's gonna be for the total so let's add that in right now the total is going to be basically based on a specific cell so I'm gonna set that a specific size probably something around the 0.43 and then a width of about right about maybe with the one maybe we can make it a little bit smaller we don't need it that big okay in the width of one okay yeah that's good okay so basically what I want to do is there's I'm gonna Center this and then I'm gonna give it a font something a little bit larger so that we can clearly see it probably something like fourteen and then I'll make it bold and then I want it to equal basically whatever's gonna be the total here so that's important I don't want any border on this so we'll say no border and then once we get it all just right we'll repeat it so we're gonna have to say no fill and also no border on that so no outline on that okay so we have our text base now what's it gonna equal let's just say that we have a total here let's just say 101 I wanted to equal that I want it to equal that so let's just put in that formula that's gonna automatically set that shape to be equal whatever's here so 101 is fine but I want to format that also I want to make sure that that's set as a currency so we can do that just clicking here that's going to set the currency here automatically well make that a little bigger 214 remember as you link it it's always gonna link it so just keep that in mind if we link it again if I clear it out and I link it equals it's gonna revert back to whatever the cell is so keep that in mind so notice the formatting also reverted so keep that in mind you'll we want to you might need to reset that a few times if you're gonna be linking it so we'll be sure to do that and then I'll set this to bold so now we have a good clear amount field now I want that amount field to show up directly under this so keep that in mind I want it all centered indirectly so I wanna do the same thing for each one of those of course there'll be a formula in here not just the amount so we're gonna set that up soon okay so I want this same thing and let's up I'm gonna update the font just before we duplicate it so I'm gonna go to size and properties and then I'm gonna set the text properties I just don't want to make sure there's any spacing or margin internal margin around that so I'm gonna remove all the internal margins and I'm gonna duplicate that ctrl D and I'm gonna duplicate that eleven different times so just give me a chance while I do that what I want to do is I want to then link each individual one to that and we're gonna put in a sum if the sum if is gonna be based on our expenses located in sheet two so that's gonna help us and then what we'll do is we'll just keep I'm just using control D to control to duplicate these so duplicate and the debate okay so let me go ahead and line those up just a second okay I'll so what I'll do is I'll align the middles here align the middle just like that and then I'll do the same thing for these just holding down the control and then align the middle and then the same thing here holding down the control aligning the middle and then I'll do is I'm going to position them right above there and now we just have to do one more type of alignment so I'm and hold my selection and then for each one I'm going to make sure that they're also aligned inside the center so we want everything centered just like that so I'm going to use my selection tool highlight everything and then align the center and then align the center here align align the center here and then I'll move them over make sure they're spaced equally apart because I want and we're going to get rid of of course the gridlines - we don't need the gridlines in this but I'll keep them for just a little bit so we can see okay so they look all properly spaced out sometimes it's hard with the gridlines to see it I also want to make sure that we have it looks like everything is lined up properly so we have one last thing to do as far as this I want to put the description what type of description I want to put it's gonna be after small text so we can see everything but let's add I'm just gonna duplicate this here and then what I'm gonna do is I'm gonna I want a smaller font it's gonna be something like eight and probably not both we've got a lot of text to fit in so what is the text well the first thing is basically it's going to be equal we can of course we can type it in here or we can do equals either one would work just fine so basically Italy equals that Ottoman transfer again notice how it it reverted back although it still says eight so we just need to enter it eight click on here in to the eight again and then just we're good to go so probably eight is going to be it's gonna have to be kind of small because I need to fit in all that text there so that's important okay so we can probably increase it slightly let's say eight point five all right that should be good that'll be cover so now I'm going to do the same thing for this and then I'm just gonna update the formula so this is gonna be 11 and then I'll do all the fonts at the same time it's much easier this is going to be twelve so all we're doing is moving down it's a little bit easier if control D duplicate it this will be thirteen again control D duplicating and bring it down here right below it and change this to fourteen and then I'll do all the fonts back to about eight point five I think and then control D again sometimes if you watch me do it even though it's a little bit boring sometimes you can at least see the shortcuts that I used to see that one control D again this would be sixteen control D and then seventeen so that's going to cover our kids and family control D one more time dragging this over below our other words and make this eighteen so you see it we can build these pretty quickly just by using the links and then once we do that I'll select them all and use then update that nineteen and then we have two more to do so this would be 20 in our last one of course is uncatted so we're going to duplicate that and go to 21 okay so now we need to do is update fonts so that they can fit 88.5 should be good let's go ahead and select all of these holding down the control selecting every single one of them we just created and then changing the font to about 8.5 so 8.5 re-centering that now they're all pretty much covered okay that looks really good now we're getting some so we can see if we click on the View and click on the gridlines it's a much much easier to look at here based on this but we'll keep the grid lines just for now and then at the end we'll take them so you gives you an idea of what we're doing I'm gonna also use my selection tool and just make sure everything is centered again here just make sure that we have everything nice and lined up here and then that's going to cover all of our text I want to make sure to incorporate all the text as well and then one can line up so it's looking really good so now what we can see is we have our different categories all lined up and I'll update this okay good so it's looking really good now we have all of our things here lives a few ones let's just make sure to include this one drag it over here all right so let's Center these here align the middle make sure we get everything covered good and then the last thing we want to do is just make sure that the font on these these texts are all equal I'm gonna bring these drop them down I'm gonna align the bottoms of these and then I'm just gonna bring them up everything looks good I'm gonna do the same thing for this also aligning the bottom so we have the center and the bottom all aligned everything's nice and aligned accordingly looking good and the last one will be again aligned the bottom and I'm Bobbi bringing this one up a little bit so this right under that okay and then this one down a little bit it's too close okay so everything's nice and lined up for the most part all the monkey with just a little bit later on but for the most part it looks good with the grid lines it's a little hard to see but we can get a better idea of how things are gonna look from here now one thing we really want to do once we get it all I want to group it I want to group everything because it's much easier to deal with that but we need to update the links individually notice they're all 101 so what I want to do is I'm gonna first work on our formula here and then what I'm going to do is I'm gonna update these amounts so that they're accurate so what is that formula well to create a funnel let's create some named ranges first I think that's really important so what are those named range is going to be well they're gonna be based some of them are going to be based on they date the category and the amount of our expense history data so let's start out with that and we'll create some dynamic named ranges based on each of those so we can use them inside the for most okay so we can create our named range take a look new the first thing we want to do it let's see if the expense date and what is it gonna be we're gonna use an offset I'm on a dynamic named range so what is that named range is gonna be based on this date here this one right here I'm gonna include the headers just to make sure if there's no data but I'm using offset and then set it down one row below so expense starting there comma one started out one row comma comma and then count a what do I want to count I want to count all the data in that column starting with the header row because if there's no data won't create any air but then of course I'm gonna create a lot of obviously not two five nine nine nine then n minus 1 why am i - thing 1 because I really don't want to count the header but I want to included in the formula because if there's no data it won't create an error comma 1 so tab out tab in make sure that encompasses all the data now we have an expense data includes all the data I'm gonna copy this form the ctrl C click OK now I want to create named ranges for both category and the amount so new expense category that's fine we'll just call it category tap down and then when all do is all I need to do is now is to change it to call them beep B click OK tab in make sure that it covers a category one more for the account I'm at the expense amount expense amount and then tab over just gonna change that to column C we can still count with column a that's fine this goes to C tab out tab in okay good now we have our named ranges located for our expenses so we're good we can close that out what else do we need I also want to make sure that we have the months and the year so let's create a named range for months because we have a list of months here all I did was just created months I'm gonna call this months that's gonna help us out and then also what I want to do is I want to know the selected month and the selected year that's important using the formulas this is our selected year so I'm going to call that selected year this and our selected month is actually not this because I want a number I want to use the number this is not gonna help us too much but I really want the numbers so what is the selected months I want to know the month number what is the month number well we can use doing match equals match what is it and we're looking up what are we looking up I'm gonna look up January and I'm gonna look it up within the name range we just created which is called months and I want an exact match so I'm gonna use 0 good that's gonna give us one I want this to be our selected months so we're gonna call that selected month that's the one I want on that's gonna be on Oh 8 that is our selected month I'm just gonna wrap that in if air just in case there's an air it'll go to blank just in case everyone said Waukesha is the wrong month and or something it's always good ok so if there's an area so this is the month that the user selects that's gonna help us in our formula we need to know that formula cuz I need to know the totals and I want to totals I want one total for this and I want another total these gonna be relatively the same this is gonna be for our graph why don't I want two totals I want two totals because our graph I want two if there's no value I want it to show n/a and because if there's no value if it's a zero I want to show any because in our graph it won't show anything that has n/a but in our on categories I do want to show zero so I'm gonna put two of them so how do we determine now we have our named ranges how do we determine how many what is the formula to determine the total auto and transport expenses for the selected month in this case is January how do we determine that well we can do that using a sum if formula what formula would that be you'll be using some if so let's use that equals some ifs and what are we summing what's the sum range of course that's going to be the the expense amount the one that we just created so the sum range is easy but what is the first criteria is gonna be multiple criteria and it's gonna be based basically it has to be within January and it has to have the auto and transport so any transactions aren't expenses within the month of January also that are auto and transport are going to be included should be summarized so let's set our first criteria nuts say that and would be after the first of January so how do we do that so we can use the die date so first of all we want another criteria engine and that's gonna be expense date right because that's the one and what is the criteria for that it would have to be greater than J right first how do we write that well we can use a quotation mark greater than or equal to and what and a date January first but how do we write the data in so it's gonna work always regardless of the format we can use the date function date what is the year selected year we know that because we just use as a named range what is the month well we know it's the selected month because that's the one we just did and what is the day it's going to be the first regardless of the day it's going to be the first so it's got to be greater than the first that is going to cover it what else we have more criteria it has to be less than the end of the month right less than the end of January so how do we do that well again quotation marks less than or equal to but this time we're focused on the end of month so it would be and e-o month we can use a Oh month what is the month again the end of the month again same thing January dates using date year of course would be selected year we have already figured that out selected year what is the month we know that selected month because we want the month number and then one for the day one but what about this is the end of the month so how many months before after would be zero we want the hero that's gonna get us the last day of the month in this case January 31st okay that covers two criteria we've got one more it's gotta equal basically auto and Transport so the criteria in this case would be the expense category the expense category would have to be equal to this Auto Transport right here okay one more thing we need to do of course we need to add in I just got to add in the criteria again this would also be expense date right because we have to add in two criteria expense date there we go so it's the criteria range and then the criteria again the criteria range and then the criteria the criteria range here on the last one right criteria range here and the criteria now we've got it complete so the total is 101 now we can just make sure that we use it well now we can just copy that down all the way down here because we've named ranges all the way down to cut it now it's perfect that's exactly what I want we can bring this down a little bit we don't need to create it so much so it's very nice now what I wanted to do I want to have toe expenses so how do we get the total expenses it would just be a total expenses here I'll create some more rooms a little bit too and then I'm just gonna be equal to of course this is the sum of everything above pretty simple they're just gonna highlight all the rows and that's gonna because I do want the total sum but I also want to know the budget right so I want to know what is the monthly budget for January but I want to put it here so what would that because I want to include January the month here total equals in this case January and because I want it to change with the month and quotation space budget just like that there so what is the January budget of course that's going to be whatever is located in i2 so here so now we have the January budget I also want to know what's remaining remaining and that's of course simply the budget minus the total expenses so it's going to be equal the budget of 3,500 minus the total expenses that's how we get our budget good I want to make these bold cuz I wanna make them look I don't know let's give them a nice look something a little bit different I also want to get some lines in here so I'm gonna format those cells just right-click format the cells the border I'm gonna give it just a nice-looking border maybe a gray and then a really a thick font here something a little bit lighter here dick font and I'm gonna use the the middle and the bottom so that way they're differentiated it kind of looks nice and we can see it here I'm gonna give it a little more space here too because they give it that and before I do that lets just group these so we don't move them like they did so I'm gonna highlight everything select everything I'm gonna group it now remember once you group something make sure you right-click good is the size of properties and make sure you always set the properties to move but don't size so I don't want this to move I spent a lot of time organizing this I don't want to mess it up right now so now we can move it everything's going to stay the same and we get a nice separation there that's what I want a little bit longer here okay so we have our totals let's set some more borders here so we can say I'm gonna format those cells alright I just right-click format those cells and make this a little bit more clear I'm gonna make a little bit darker scuzz it's more important and I'm gonna give it the double lines on the ball in the middle okay so that looks right now we can see differentially between those two so very important set these formats to the same as everything else which is the currency format and set that right here now they're all the same now we have it and now of course when we change our month let's just say February of course I'm going to use a drop-down list on this so don't worry okay February everything changes perfect that's what I want okay and of course there's no data for 2 0 to 1 so it should everything should go to 0 when I change the year perfect that's what I want but February budgets the same perfect ok so we'll change that back to 2020 so we know that our data and our formulas are working just the way we want to but let's add in some more what else do I need I want to have I need to link these remember I need to link these totals here because they're not linked correctly yet this one's on 112 this one's on 101 this one's not has not been this should be the 13 right our education is row J 13 so let's update that and then I'll have to update the fonts again as I link them let me go ahead and do that and I'll be right back ok so now everything's linked all these totals are not linked J 21 J 20 J 19 J 18 and so on so now I just once I reset the links of course I also need to reset those fonts so let's just hold down the ctrl click on each one of those make sure they go all go back to bold and all go back to font 14 so I'm gonna select that change that to 14 which is what I want and change it to bolt now everything is consistent everything is the same and now when we change this to February all those values will update and of course I'm gonna set a really cool drop-down list for the month okay that's what I want perfect looking very good let's save our work and now what I want to do is I also want to fill out this for the graph I want to do almost the same thing as this so I'm gonna copy that but I need to know if it's 0 if it's 0 I'm gonna put n/a so I'm gonna copy this basically and I'm gonna put it right here paste it in right here but what I really want to do is if the value so I'm gonna put it if if this value is equal to 0 then what I want to do and put it to n/a so any I want to have that n/a otherwise paste the value otherwise I want the value so end quotes okay we can get rid of this equal we no longer put the equals now we're good to go so now we can enter that and we get 112 so for anything that's no balance we want to make sure and we just drag that down there and let's put in something we want to make sure that it shows any when there's no best January had one so type in January and create the drop-down list shortly perfect that's what I want an a that's gonna be perfect for our graph what kind of graphs do we want I want a donut graph I think that would look really nice let's shrink this so we can see it and let's build a donut graph this so I'm gonna highlight all this in the data and I'm going to create a donut I'm gonna place it right about here so I'm gonna insert I want to insert other charts and I'm gonna choose donut something like this and that's about what I want but I want to custom I want to customize out a little bit here okay the first thing what I want to do is let's see I'm gonna add I wanna I don't want a legend but I do want to have data labels so let's show the data labels and I want to put them outside but I don't want them exactly like that so let's format the data labels here and what do I really want I really want to put the category name here but I don't want to have the values and I want to put them on the outside of them so we're gonna bring that out and bring it make it much bigger too so it's a really nice to see let's bring the whole size into something much smaller about like that that's gonna give it and I also want to make it bigger so we can see everything let's select it and enlarge it so we can get a good look at the field and then I'm gonna also take the values I'm gonna put them outside I don't want them inside there bring it up a little bit bigger so we can see it nice and big that's what I want to see great bring these out a little bit here so we can see them just gonna move them up a little bit because then this older version we don't have this on the donut chart as far as outside or leader lines but I like using older versions of Excel because many of our users don't have those versions so it's important that I try to stay fit and let's format these so we can see them a little bit better and what we'll do is I'll just format those and I'll give it a little bit of a bold font something a little bit clearer and maybe one bigger okay so that gives us an idea now remember newer versions of Excel will have leader lines for these alright that's what I want I want a title so let's move this down a little bit and just give this a little bit of a text box so I'm gonna insert a text box here and I'm gonna call this spending by category so spending by category and then I'm gonna Center that make it increase the font and make it bolts we can get a nice clear good I also want to duplicate that this time but this time I want to have something another chart I want to have maybe some type of a cylinder but this one I wanted dynamic based on this so I want I want this to be equal to whatever this is here January budget so I'm gonna show it by month so let's use that equals and then I'll put here whatever is in here January budget I want that and again let's reformat then make sure that it's the same as this so again 22 bold just the way we have the other one so everything matches 22 and then bolt alright so we don't need I'm gonna hold down the control I'm gonna line these to the middle make sure they're both the same and I'm gonna remove the borders I don't need an outline on these ones okay so one more so what I'm gonna do is I'm gonna highlight the total expenses and the January budget total it because I want to know both I want to know what the current expenses are and what the budget is and I want to show that in some more of a cylinder chart so we're gonna click insert and then bar and then cylinder I'm gonna choose this one right here and that's what I want but it's gonna have to make a lot of changes to this so first of all let's reset the let's set this color to gray just so we can differentiate this is the one this of course is going to be the budget so let's format that data series and I'm gonna give it a fill a solid fill of let's say grey on this one just this color gray and then the element is going to be green so let's change this one itself to green so I'm gonna format this specific data point I'm gonna give it this a solid fill but this one I'm going to choose our same theme green here so we can see it now what I'm gonna do is I'm going to reset the data here I'm going to switch that so I'm gonna go in to the Select data and then I'm gonna switch the row in the column what that's gonna do is gonna give us exactly what we're gonna I'm just gonna reset this to gray because I want them in the same so I'm gonna go back to fill solid fill and then choose the grey one again so I want to differentiate nice that's basically what I want but I don't want anything else so in this particular case we're gonna check we don't need a legend so we can click no legend we don't need any data labels also we don't need any access so we can just delete I can click on the access delete them I can click on this delete it I really don't need anything else all I want to do is see see the eventual data so that's all I want to do is see the cylinder here so I'm going to delete basically delete everything else including this one right here okay good so again format that I'm gonna format the Charter I want no fill so no fill and no border no line good that's exactly what I want I'm going to increase the chart so it's the maximum the width and size and then I'm gonna reduce the entire width just so it doesn't encompass everything so I'm gonna select it and then bring the whole thing down a little bit so we can get a nice look right above it so there we go so it's gonna fill it out that's what I want I want to show exactly not I all still want to show a little bit more I want to put that in numbers so I want to know again so let's take a look at this I also want to know what is left right so how much is left what is our budget what is left so that's important what is left in this case is 2259 that's what I want in a text so I'm just gonna copy this here duplicate this one and then I'm going to use it again basically and then reset the format so what I want to hear equals whatever is remaining here so I'm gonna click which is in j24 reset the font back to bold and 20 let's increase the font a little bit on this one bring it over we don't need that much space so that is what's left right we have that that's what's left and then that's what this control see I'm gonna copy another text box use it here increase the font I want to show that it's left so this is 2259 left of the month this one we don't need any formula on this one and I just going to type in some text call that left then I'm going to group everything so that's left right we have 2259 less than expenses available for the month ok good so that's really nice that's what I want to see here make that a little bit better so we can see it so we can see our graph our January budget we have 22 59 left total expenses of 1241 and it gives us a visual of what we have left so I'm gonna bring this let's bring this a little bit we can shrink this one a little bit here bring this here a little bit to left and then hold down the control and just basically group everything together so it's nice right-click format we don't want to hide we don't want to size that with cells so make sure we're always move but don't size with cells now it's really coming together I don't want a background on this format those cells will just go to format and then shape fill and then no filled won't make a differences white and background all right that is looking really really nice we also have to add an expense if we want to be able to add expenses let's put a little bit of shadow on here just something a little bit little picture effects and put a little bit of a shadow not a big shadow but that's a little bit too much so we'll go into picture effects shout-out and then I'm going to go into shadow options I just want a very very slight shadow three-point and then just go back just something to give it to lift it off the page a little bit that's pretty good that's kind of lifts it off all right what do I want I want to add an expense right we want to be able to add expenses to this we have to have a button and I'm going to actually use a user form in this one so insert what I'm going to do is insert a shape I'm going to create a button something very different remember when we're creating buttons we want to create something that stands out to the user so in this case an orange button would probably be appropriate because so let's put an orange button and we don't need an outline and then we'll put a little bit of a shadow on it shape effects and then shadow okay so that's what I want so what I want white font I'm gonna put a plus and just gonna call it in all caps add expense that's what I want I want to be able to use an add expense obviously we're gonna need to make that bigger so Center that center that and then make it bigger and then make it bold that's that's good that's good enough for our button and so we have add expense so when they click this button I want to pop up a user for him to come up it's just a simple pop-up form that puts the date the amount in a category that's all I want click OK and then when they click OK I want it to add them in this list so how do we do that well let's take it look all we need to do is go into VBA and create a very small user form but before we do that we also need to be able to select months in years so I'm going to create a shape based pop-up menu very cool let's do that right now for we get into that so when we create this formula we also need to give the user the ability to click something I want to clean you know we normally have a drop-down list but let's give them some kind of an icon to click so that they can get to that drop-down I'm gonna use something like this right here this picture frame this half frame here and then I'm going to give it a shape fill of white and then no border shape outline I'm going to see no outline and then what I need to do is I'm gonna turn it like this and then size it accordingly so obviously we need to turn a little bit more that's what I want and then I'm going to give it a size something like point two and then I'll do a point two that's kind of nice right so we move that over let's take a look at that and see if that looks that's kind of nice it's not quite though it's not quite let's zoom in it's not quite perfectly okay that looks good perfect now I'm going to duplicate that control D and I'm gonna do the same thing for January so now we can select the month now we can select the air so that's kind of a nice icon let's go back to 100% it's still a little bit thick isn't it that's kind of thick let's move that let's it's a little bit thick so let's bring in let's bring it's a little bit too thick so we can bring it a little bit lower and then a little bit lower here I like that okay I like that one better than this one so I'm gonna remove this one just duplicate this one here that's really the effect that I want perfect so that's gonna be our drop-down icon there so users can drop that down back to 100% so when they click this they're going to get a nice drop-down list what kind of drop-down list do I want I want a shape based pop-up so let's create that insert I'm just gonna use the square and let's set the size about let's say 0.3 and then with a width of 1 and what is the naming of this and I'm gonna give it the same shape fill as we always do no outline on this but I'm gonna use a transparency I kind of want a different transparency a little bit something a little bit transfer so I'm gonna go into more fill colors and I'm gonna set the transparency maybe about 10% that's gonna kind of give us a nice clicking ok so we can see a little bit through it kind of a nice effect all right now we've got our shape got our transparency but I want to give it a specific name 2020s the I want to give it the same exact names of the year and the text of course is gonna be on this 2020 so I want to be able to use this so let's Center that increase it and make it bold so we can there we go so now I've got 20/20 I'm gonna duplicate that control D bring it down here and control D bring it down here actually we could use a little bit of a border on that so I'm gonna do a very light white border and I'm gonna make that outline very light so I'm gonna go into the weight and just make it something like that that looks nice at least these you can see when the button separates so this one's gonna have a name of 2021 the name is very important name of the button and of course the text I'm gonna do the same thing of course with the second one I'm gonna give that a name of 2022 and the text of course is same thing 20 22 okay so we have our years now let's take a look and I'm gonna group them I'm gonna make sure they're all they look aligned but I'm gonna make sure they're all lined and I'm gonna distribute them vertically so they looks right and then I'm gonna group them I don't want to call this year's group okay now I'm gonna pretty much do the same thing for months but this one I'm gonna control see if I use duplicate it's gonna keep in the same group so I'm gonna I want it outside of the group so I'm gonna you control C control V that's gonna create a month but the month I need a little bit bigger probably so let's just go with 1.2 on the width and again we're gonna call this the same January and then I'm gonna also give it the text of January the name of the button is very critical because we're gonna use that in VBA with some very easy VBA code January all right very nice so we have January I'm gonna duplicate that I'm gonna do the same procedure for every single month so hang on while I do that and I'll be right back okay I've now created all 12 months and I've named each all of the shapes the exact month name very important just like we did for the years so each one has a very specific and unique name spelled correctly hopefully within the month alright let's go ahead and group those I'm gonna hold down the shift and I'm gonna group each one of those we're gonna call that month group because I'm gonna be able to show and hide that with a single line of code so I want to make sure that their group I'm gonna group them and then I'm just gonna call this month's group okay so we have months group and year group what I want to do is I want to click this icon here and I want to have this display I want to select with one line of code and have that month changed to here into E - same thing here in year I want to be able to select this have that appear and select any specific shape and have it appear here so we've got to write that code that's just a little tiny bit of code and I also want to add expenses so we're gonna get into the VBA and write a little bit of code to make those things happen to be able to add expenses to be able to change the year and change the month so let's do that now inside the DBA editor you've had the developers tab available it'll be here under the Visual Basic if not you can reach the developers under the options you go into the customizer ribbon you'll see developers select that will get you there you can use also the shortcut alt f11 to get you there as you see there's no code we have budget we're gonna write in just a little bit of on cheat code and we're gonna create also one module in one form so let's do that right now we're gonna insert a module here and let's just call this expense Mike we don't have very much code to write today so most of this was the work was on the sheet expense macorís something very simple and what kind of macros do we want well I want to be able to add an expense so how do we add an expense I need to create a form to do that alright I want to create a pop-up form do so let's create also the form so right click insert and user form I want to create a user form here's our user for me it's gonna be a very basic user form not much is gonna be really going on with that just want to keep something very simple why don't we give this call call this add expense form something basic and then I'll give it the name I want to give it a caption of add expense and just give the users the ability to add expenses with this form something very simple with two buttons and just a few different and we'll give it a title so let's go ahead and click on that will give them the toolbox and I'm gonna create a title for this I'll bring it up here we'll just call it add expense so I'll click inside here and call this add expense and I want a larger font on that so we're also gonna Center it on a line the center create a larger font so we'll go into Tahoma something like maybe 16 and then bold that should be sufficient maybe 18 a little bit bigger click ok that's good something nice and big we can see oh I'll have other ones I'm gonna duplicate that and what I'm going to do and I said I will make it transparent I want it transparent so let's click transparent and then I want to duplicate ctrl-c and ctrl-v and I want to create some labels but they're not going to be that big so let's create I'm just going to go down to the 12 font here I don't want something so big and it's just put on regular font that'll be fine and I'm gonna left let's write justify these because I want it on the right side but I'll bring it over the first one's gonna be called date so let's change this to date I want to date we can just put date not add a date here so I want to date and then I want the amount and then I want the category so those are gonna be the three so that should be finally on and duplicate that control-c control-v and then this one's going to be a mount amount and then I'm gonna put this equal to the same as day put that about right up here and I'm gonna make those and then also we want one more for category so let's control it's control V and putting the category that's gonna be on the lower left and two buttons gonna have an OK and cancel captain Goree make sure I spelled that right okay bring it out so we can fit it all in and that's gonna be placed down here and then I'm gonna have two buttons so let's create the fields for that I'm gonna make sure these are aligned and we'll line them of course to the middles here and that's pretty good so let's put it in a date now let's put in some fields so I'm gonna put in a field here I'm also gonna make that 12 and make it a little bit bigger so let's put the height is about 22 that should be sufficient and we will also make this 12 font so I'm going to go into the font here and then just change that to 12 that should be sufficient and I'm going to actually we can bring this over a little bit we can add a little more space for this so we can bring it over the date might take a little bit more space that 12 font okay good so I'm going to copy this and I'm gonna paste it and then I'll put it right up here for amount so now we have an amount and date I'm gonna make sure everything is justified so aligned to the Middle's and then also want to category so I'm going to control copy that now we have a category so actually category is going to be a drop-down list cuz we already have a category so let's put in a combo box here for the category a little bit larger again I'm gonna put the height also is 22 just as our others and the font also as 12 just like we did the other so that we're consistent with that so into the font we go and put that as 12 okay so we're good with that now we have everything set but now we need our buttons and when you know let's say let's make this background white so I'm gonna make the back color white alright good so we have X that's just a basic form but I want to create some buttons I really don't want to create these buttons I just don't like them I don't like these buttons here are not very pretty so let's create something different other than that button here let's create something more in line with our theme and how do we get that let's save will or work we've done I'm gonna take a picture of a button and then make that so let's go down here anywhere outside and outside of this just gonna scroll down and I'm gonna click insert and I want to create a shape and we're gonna make a picture of this so it's great way to make a button I'm gonna call this okay and then I'm gonna format center it make the font white on this one it's already white bold it and increase the font here okay I like that but not not the color shape fill and want the shape fill no outline on that and then I'm just gonna give it I want a little bit of more of effect so let's go to the preset effect this one here I like that that's kind of nice and it looks really good so how do we do that so I'm gonna duplicate this control D and then I'm gonna create one for cancel because I want two buttons so cancel now we've got two different buttons and now we can use it so but how do we get these into these are these are sheet buttons these are not form buttons right so how do we get that well we can actually take a picture of it I'm going to use Snagit screen editor I'm gonna take a picture of this and we're gonna since we're using a white background doesn't need to be perfect that's good for that so now I've got it in my Snagit software and I'm gonna do the same thing with the okay button so let's take a picture of it I'm using my link I'm gonna take a picture of this okay button perfect zoom it and I'm gonna save it now don't save it as a PNG save it as a JPEG okay now it doesn't matter because we have a white background it doesn't have to be perfect we've got a white background on our form but if we didn't we didn't might have to do some more work so we've got the okay I'm gonna save this and where am I gonna save it I'm gonna save it in the folder that I'm working in okay I'll just call this okay button and then I want to do the same thing for the cancel cancel the zoom it in just cut it up a little bit make sure we trim it out a little bit and we can keep the shadow on so that's kind of a nice look feel of it so we can keep the shadow on just trim it a little bit and then I'm gonna save this again I'm going to save this as cancel button okay very good so now that we have that in there and now we have those two so now I'm going to go back into my form here and I'm gonna bring those in so I'm gonna take a picture and I want to bring it right in here I'm gonna bring that okay button right in here so how do we do that I'm gonna browse for that button we just saved it so going into the picture I'm gonna click here and I'm gonna browse for that there it is so I want the okay button here and we can shrink that up just a little bit here I like that and then I want to do the same thing for the cancel button so I'm gonna bring in the cancel button here and then I also don't want any borders or any background on that so we're gonna fix that and then I want to click the picture and I'm gonna click the cancel button okay so now I'm gonna hold down the control both of these I want transparent and I don't want any border on that so we could just go with white board or something like that don't want anything that's really the way I want down that's what I want okay and cancel now we've got our form let's drag these over Center those a little bit good good let's take a look at that take a look at that add expense date amount category okay good so we do need to assign of course a drop-down list or category we have okay and cancel those buttons are not doing anything yet that's what I want that's just what I want kind of a plain form we can get rid of these buttons now we're done with those we've just taken a picture so we're good with those let's get in and let's start assigning some macros and of course we want to make sure we assign the CAD but let's do that we need a named range on those categories very important because we're gonna use those inside so back into our sheet we can just call this I'm gonna highlight all these we're gonna call it categories categories is that same name range that we're gonna sign within our code okay good so we have our form and let's just start assigning some values in some code to this so right click and go into the view the code and we're gonna we don't have any code right now but what kind of code do we want to have well the first thing what we want to do is I want to assign some a cancel button let's just try that so let's name actually we still need to name it let's go ahead and name our information because that's gonna be a lot easier to view the object we're going to call this I give that expense exp date very important to name those so we can work with them I don't want the default names and then we're gonna call this expense amount e^x big expense am amount okay and then of course we're gonna call this expense category expense category that's fine for now now we can refer to those by name very important also the LK the buttons to is very good I'm gonna call this okay button and then the cancel button once we refer to them in the code much easier we know exactly what they're okay call this cancel button now that we have everything name we can go back into the code view the code and start working on that so we have our kids our cancel button now watch this there's no let's close this we don't need this there's no click so how are we gonna get a click we don't well we can make it we don't need it right so let's just get rid of all this we don't need that backspace that forget drag over and just type and click click now we've consigned a click event so what do we need to do basically I just want to close the form so add expense form dot hide really what I want to do and maybe unload the form to unload which is going to clear it out unload what do we want to do it I want to load add expense form great ok so does that work let's just take a look let's run that let's view the object play that click on the cancel and it's working perfectly that's what I want you see how that works now I really like the looks of those buttons there is one disadvantages they don't have a tap stop so keep that in mind this one slight disadvantage but if you have a simple form like this it should be sufficient all right so now what do we want to do now what I want to do is I want to Nisshin I want to make sure we assign a category to that when we activate the form actually so we can go back into the code and when we activate the form we want to do something what I want to do I want to go in the user form and I want to basically create an event on activate when the form is activated I want to assign the expense category to categories I want to assign the categories to a drop-down list so how do we do that well we can do that a pretty simple expense category thinking category what do we want to do row source what is it equal in equals categories that named range we just created categories categories okay and also what I want to do I want to actually set the default expense date so an expense date dot value equals what equals the current date but let's just put in a format it pretty much equals the date but let's just format that format format doesn't work in every version of Excel sometimes you have to use application function text for formatting it so but we can use format it works fine here format what I'll format the date and I'm gonna give it the mm slash that's the same DD why why why why okay that's the format that I want so those two things are gonna happen when I run the form let's take a look let's go ahead and double click it and run that form now we have the current date we can put in the amount and the categories are all set up perfect that's what we want so now all we have to do is set the okay button and then just set the date so what do I want to do on okay when it's okay I want to find the first available row here I want to place the date the category and the amount but I want to make sure that the user has first filled out all those they're all required we can't add an expense without actually doing it and then of course I want to tie that to this button here so let's do that so back inside the code let's go in and view the code and let's start writing some code for that one we don't need this user form click but what do we do need we do need the OK button so let's click on the ok button here and let's take a look at this so now again all we have to do is right-click again just like we did before so click so empty this out and then just right click typing click now some versions of it have it already but we're just going to type it and click OK so it has a click so what do we want to do the first thing I want to do is I want to make sure that these are has entered values for all the forms so we're gonna check for required fields next up so if expense date dot value equals empty or expense amount dot value equals empty or expense category category dot value equals empty then what I want to do message box please make sure to fill in all fields before saving ok exits sub nothing else we can do unless you just filled out now moving on of course they have now slits a dimension the expense road as long so we need to set an expense road expense row as long and what does that expense roads can be based on sheet2 the expense row it's equal to sheet two those are expenses dot range and we can use column a a $9.99 dot end Excel up dot Rho that's gonna give us the last row of the value but I want the first available row so in that case it's gonna be plus one so first available row now that we have the first available or we just simply need to write the code in that's gonna put it in there we don't need data mapping it's only three lines so let's just write that sheet 2 dot range a and the expense row dot value equals what simple equals expense date dot value and that's called there will be the date expense this one's going to be super easy on VB acres its Penn State and then again we can just copy that and then just update it for both columns B and C very easily paste that in there paste that in there and this one of course is gonna be called category and this one will be the amount amount is located in column C and then B is for the category then we just have to update this this would be expense category and this would be expense amount okay I think we've got everything right that looks pretty good so we've got tied we've got the OK button we've got the user form activate okay good now we've got that right we need to do a few more what else do we do I'm gonna hide the form once they've done it add expense form dot hide on the height we know you no longer need the form and then I'm gonna unload the form which clears out the form unload and then add expense form okay so we're looking good we've got everything written now I do is double click let's try that out and see what we've got the current date in the right format let's go in $45 here and let's select a category bills and click OK perfect let's take a look down bills and utilities $45 under April looking very good alright that's it for adding expenses now all we need to do is write a macro that's gonna add this and create that pop-up form very simple inside our expense macro so here we go all we need to do is write a simple sub procedure sub add expense and what do I want to do on that Add expense form dot show simple is that or we can just copy this and then go into our sheet here right click this add the macro paste in click OK it's the only macro now add expense it's gonna be perfect 25 click on expense click OK nice now we have our edit ok excellent now all we need to do is get these months and years drop-down list working just right so how do we do that when I click this I want to display month grip when I click this I want to display years grip so let's write some code for the so back into our new module so let's do that let's write a macro that's gonna do show and hide sub show/hide gear selection want to write one if first of all I want to make sure that the years group if it's so what I want to do is if if they click this once it opens if they click it again it closes so I'm gonna use the same macro to both open and close it so how do we do that first we need to check to see if it's visible or not if sheet one dot shapes years grip let's double check the name on that I want to make sure I got the names right your your group or yours group let's take a look at that it's called year's group okay so years group dot visible equals true if it's true what do we want to do we want to hide it then shapes then we just have to copy and paste this dot copy paste this here then paste that visible equals and there so false nothing else to do because it's open we just want to close it else what do I want to do I want to make it visible equals simple equals and my so true that's it that's all I want to do with that okay this should be true I'm sick okay so we got true so let's see if the visible true then false so now we've got that's all we need to do so let's I'm gonna copy this macro I'm gonna go back into our sheet I'm gonna take on this little arrow here I'm gonna assign the macro paste it in click OK now when I click this it hides and shows hides and shows exactly what I want to do let's do the same thing for the months very simple all I need to do is copy this macro and make the changes two months so this would be month selection and then I'm going to copy this the words month and just paste it over because that we did everything else the same exactly the same and then we all we need to do is assign that macro to that again this would be month selection show or hide month selection so click on that click on the grip make sure it's name month group click on here right click assign the macro paste it in click OK now when we click here perfect okay that's what I want I won do a few more things if it's open and I select anywhere on the sheet I wanted to close same thing with this if it's open and I select anywhere on the sheet those listed close so how do we do that well that's on sheet selection change on sheet budget sheet and it's based on selection change so sub the worksheet selection changed now let's write some code again all we need to do is just make sure that those two are are hidden so shapes we don't need to push in shapes and if they select anything it should be closed months group dot visible equals M is so false right we don't want I don't want to show up same thing shapes years group table visible equals Emma so false okay so basically if they select anything else both of them get hidden that's what I want open up gets hit and open up its select on something gets it perfect now what do I want to do now I want to assign a Macker do each one of these this is the super easy part I can assign the same macro to every single one of those and it's going to work all I want to do is take the name of whatever shape no notice we name the shape to 2020 we name the shape 2021 2022 same thing four months we name the shape January so all I need to do is take the shape name and put it right in e2 or take the shape name and put it in b2 how do we do that I can do that with just one line of code actually so let's write a macro for each one of those sub year select so what do we do on that sheet 1 dot range B - that's our year B 2 dot value equals what application dot collar that is going to tell us the applications call it and what else I wonder I just want to hide I want to hide the years grip actually I wrote that in the wrong area let's write that I'm gonna paste that get rid of that it's not the right place for that we want to be inside the module for that and that's what I want to be I want to put it here sub your select I'm gonna do the same thing for months sub month select I'm gonna do the same thing here so what is it again sheet 1 and this time it's not XI 1 it's e 2 right dot range - dot value equals an application doc caller don't forget sheet 1 sheet 1 dots month group that visible equals MSO false okay and we got add sheet one up here - because it's inside their sub sheet 1 ok good so now we've got your selected months like so now all I need to do is copy year select go in select here select the entire group of all the years assign the macker and paste this I can do the same thing with month with months right-click assign the Macra and select month select now what are we gonna do now select their month January already that looks good February perfect March good very little data for those January nice I really like that what about the year 2020 good there's no data for the year 21 it's fine that's exactly what I want to show so we create this really cool drop-down list shape based very very cool just by simply selecting that let's bring that over a little bit needs to be over a little bit to the right it's a little bit too far for the larger months names so I like that in fact I'm gonna bring the whole group over here right justify them all line to the right to there everything's justified that looks nice that looks a little bit nicer you notice there's a little bit of transparency on it will looks good I really like that it's looking really good add expense all right let's take a quick look over that see if everything is accurate before I let you go on this one all right it's looking really good I hope you have enjoyed this personal monthly budget feel free to download this for free using the links in the description below if you'd like to get over 100 of these I have a $37 zip file I'll include the links down below if you want to purchase 37 that helps keep these videos for free and it gets you well over 100 workbooks all of the best templates on Excel alright thanks so much for joining us I appreciate it on the personal monthly budget next week will be something brand new have a great week thanks so much

Channel: Excel For Freelancers

Views: 209,416

Keywords: Excel Budget, Excel Monthly Budget, Monthly Budget Application, Excel Budget Application, Budget Tracker, Budget Manager, Excel Manage Budget, Budget Manager Excel, Budget Manage Excel, Excel Managing My Budget, Managing Your Budget Excel, Excel Budgeting App, Budget App, Excel Budget App, Manage Monthly Budget, Monthly Budget App Excel

Id: gIOj_6mIAR0

Length: 63min 37sec (3817 seconds)

Published: Tue Apr 07 2020