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.