The download is free you can check the links in the description we move rather quickly on these so feel free to slow down the video or pause it and re-watch it each every time this is for intermediate to advanced excel but all types are welcome regardless of your excel scale i welcome you here all i ask that you do is just go ahead and subscribe to our channel make sure you click the like button down below and of course i love to hear from you so add your comments down below i reply to each and every single comment every week and every day i'm there for you replying so i just want to let you know that i'm here for you if you have any questions that's what the comment section is for down below if you ever thought taking your excel skills to the next level turning it into.
Passive income well the truth is you can in fact in my mentorship program i am showing you just how to do that we're going to take you every step through the defining phase then we're going to design development and deploy i'm going to show you the complete process of how to create your own excel applications and release them for passive income just as i've been doing since 2015. i'm going to show you that in the mentorship program i'm going to be creating an amazing accounting application and showing you every step of the process and that's going to include invoicing inventory purchasing full customer deployment a complete dashboard an amazing email notification.
Center for over 50 triggers and a ton more if you would like to learn how and see how an amazing accounting application gets created the mentorship program just might be for you go to my excel mentor dot com and learn how if this is right for you all right let's get started we've got a lot to cover i want to make sure that we give this a name i've got a blank worksheet but i've got some things to help us move along we've got some formatting and what are we starting off with well i want to put our projects i want to create unlimited projects on here so i want to have a button set it's going to allow us to save projects add new projects and print projects it's.
Going to be really simple and then what i want to do is i'm going to have a list of those projects so once they get saved they're going to go in here in the list of projects and then there's products projects that have specific items so inside each product will have items so we need to know a product project number and then have those items one of those items type of labor or materials and then what we also want to do is make sure that we have the budgeted cost the actual cost we want to save all that information in the table so we've got that i've also got a list of customers here just kind of a basic customer with not much going on there when i load a customer on.
Gonna list be dynamic so that all of our project numbers go those are the three what you see here is criteria extract those are from advanced filters that have already been run on testing and we can delete those they're just going to be recreated each time that we create an advanced filter which is pretty normal so really we have these four that we're going to be focused on so let's get to it in the training i want to make sure that you get so what kind of name am i going to call this i want to call this project notice starting format just to make things easier costing budget let's try versus actual and i'm going to double check my spelling because you guys always.
Correct me because i mess up my spelling all the time project costing budget but that's going to look good now okay so let's go with the green now often you ask me what color sets i try to use different colors sometimes i've also got some information here these two columns we're going to use for admin and they're going to be hidden and with you when you get this if you want to take this to another level and you want to release this to your clients you're more than welcome to do that just right click and hide these columns and make sure you lock your worksheet okay so i want the customer row here whatever the customer rows i want a project row and i want a new project we're going to write some formulas for those a project load whether it's true or false in your body so.
The new project is going to be true or false we're going to just set that up right now and then the project loads can also be true or false so we'll set that's false we need to know what the project and then i need the next project number what would the next project number me if i've got the project number one i want to know the next one what would it be we'd use the max formula so let me write a little formula for that i'm going to use if air just in case there's nothing if error in case there's no projects i want to use the max on the current numbers what is the max of what the current project numbers that's that named range but i don't want just the max i want the max plus one so plus one what if there's no project numbers i want to default.
To at least number one so we can do that why would we do that well just in case notice it's two it's gonna give us the next one that's what i want but if for some reason this got removed and there were no projects at all i'd want this to default to number one so that's gonna help us we do that okay i'm gonna put that number one back it'll help us for loading purposes i'm going to keep the data in here so project costing budget so basically what i want to do is i want to have a table down here i want to have some project information here i'd like to have some summary information here and maybe a small graph here and then just information so let's let's go ahead and bring this out a little bit i want to just take a look at this here we'll go all the way to acid i'm going to format those cells just to give it a kind of look we'll go with green on this one green is the.
Color of money usually in some countries so why don't we go with that because i want you to use this to not only make money with your clients but i would like your clients to make money with that when they create this application and use this application they can then reduce their costs and increase their profits that would be nice so i'm going to fill effects and then i'm just going to give this slightly off the screen just a basically a green color and that's fine just a faded green okay so what do i want down here so basically i want to color let's just say column c i'm going to use this as a buffer column and that means we could increase or decrease this column width.
Posts Related:
- How To Create A Contact Scheduler Application In Excel From Scratch Masterclass + Download
- Encrypt Database with Password and Set up DB Connection - Oledb .NET E13
- Excel Today - Cross Functional Charts in Visio
Nothing will happen in that column and then probably around column oh let's just say i'm going to hold down the control and then i'm going to go here all the way to s so that way i'm just going
To go down as far it doesn't really matter how far we go down i'm going to color this this standard green color that we're using okay so we're going to color that green so now i've got that so let's bring this up we don't need this extra we can color this white too we don't need this we'll go up okay so let's add in some company name here so let's go ahead and fretters construction and we'll go ahead and put an address in here that way your projects can always look professional main street anytown usa okay so we've got that so we've got a little bit of a company information i'm going to add a logo in here and then i also want to put in the project name that's going to be helpful too so let's go ahead and project.Name and then i want to store the customer of course so we're going to put the customer name name here right justify those are all ready so i'm going to put in a drop down list i want a data validation if we notice remember we have a data validation so let's put one in for the customer and it's going to be a list type and then what we're going to do is put in customer equals customer underscore name that's the drop down list we've given so now we've got a list of all of our customers which is what i want now when i select a customer what i want to do is i want to put the address 1 here and address 2 here so that's it we're not going to need these.
Grid lines much longer we'll use them just a little bit to help us but we're going to undo those very very soon so i want to put the project number here let's i want to put a project number number and then i want to have the project number go here let's just say one or two or whatever it is there and then i want to also want to have a date here the project date and i want to have it created by who created this created by so we can keep track of that that's all the information i'm really going to store here let's say in j7 let's say i'll put some formats and i'm going to format those because i want to go i want a green part so let's go ahead and fill effects i'm going to put in a green i'm going to go from white to this light green here so the white is going to be our first color and then our second color is going to be that light green and i'm using vertical and then go left now why do i want to do that because i want to have this area right in here all for our.
Admin so we can see our project costing summary kind of i want to put the summary in here it can look nice and then i'll put a border around here because i want to have it all the way around here so just on a big border here let's go we go all the way down about here and then i'll put in maybe a dark green border so it fits with our our theme go ahead and into the borders a thicker one we'll select a darker green color maybe this or maybe a little bit darker here just a little bit darker here and then click ok and then surround it okay that's what i want there that's going to set up our project we can now what i want to do is i want to put.
In some underlines here so we know which i'm going to hold down the control format the cell and i'm going to use maybe this double line here and put underneath because i want to know which lines are going to be the user to fill in i think that would be helpful there so the address is going to go here i'm going to put the header here what i want and then i want to put my table down here so what would be the first one i'm going to call that item name and i want that on a different row i want that on two rows so i'm going to hold down the alt and then click enter and then put in name that's going to put it on the second row once they have the item name what the item type is going to be labor materials so item type and then i also want item description.
And i also want i want a total i want three columns for budget and three columns for actual so let's do in i also want to total it up so total budgeted let's see the budget budgeted we'll go here and then i'll put in down here i want to put in the quantity of the budget so qty i'm going to put in the cost and then the total and then i want to do pretty much the same thing but slightly different but this is going to be actual so i'm going to copy this paste it here and this of course is going to be total actual so i'm going to change this to actual and then what.
I want to do is i want to have a formula here and here for the totals for the total budget so i'm going to merge and center this holding the control merge and center that and then i'm going to format these particular cells so i'm going to give it a format the cells and i'm going to give it that fill using the green theme fill effects but i'm using a little bit darker so i'm going to use the mid to the mid light here on this one and then we're going to click ok and on the bottom one here on all these bottom ones here i'm going to should have done it too here format the cells fill effects and then we're going to use just a slightly lighter one that we did previously which is this mid and then we're going to go to the light one that's the one i want okay perfect.
And then i'm going to do the same thing here and here format those cells because i missed those and but i want the totals oh we yeah we could keep those white but we're going to they're going to be formulas so we'll put the same effects that we want to here which is that mid green here and then we're going to go down and that's it okay good so for the rest of these what we're going to be doing in this one and i'm going to hold the control down for these i'm going to use the full from the mid to the light automatically that's going to give us that nice blend so again we're going to go from that mid green all the way to the lightest screen so that way because those are merged and centered that's what i want let's give it some borders format those cells i'm going to go.
For a top bottom and then inside so the borders in the middle of course will be this and then i'll put the top and the bottom here that's what i want we can actually add it here too as well for those ones that are not merging that's what i want so here so i'm going to right justify these two because i want the soda so this is going to be the total of the budget so equals the sum and basically we can just start here and then go down to whatever row we want 99999 that's fine that'll cover here and then i'm going to do the same thing here so i'm going to do is just going to copy and paste this here that's going to cover us for the total so we have the total actual it's going to be.
Whatever here we have the quantity and the cost so this but i do want some lines that i can separate
These so i'm going to put go all the way down here hold the control and then i'm going to do the same thing control and then go all the way down here so we've gone down to the bottom and then let's hold the control one more time now what i'm going to do is i'm going to put some right borders on there i'm going to right click and format the cell sorry it's off the screen bring it up here so i'm going to put this black in here and then on the dotted lines on the inside that's what i.Want so that we can differentiate between those that's what i like so so we can separate clearly separate between the budgeted and the actual cost here we're going to put something like i want to know the versus the budget so in this case i want to know what the actual is versus the budget so we can put in vs budget and then i'm going to put an amount here budget and then alt and then enter and then amount so we can put amount in parentheses this is going to be the amount differences and this is going to be the percentage difference so we can put in versus budget and then we can put in percentage here in parentheses so percentage because i want.
Both of the percentage and we're going to use conditional formatting with some icons percentage all right so we've got amount and percentage that's good i want the item name here so the item name here we have it let's put this in a data validation too because i want the item name to go here so data data validation and then we're just going to click yes whatever is in i had it previously there on item name so that's okay all i want to do is click item name and that's going to provide i had it in some cells already that's why it appeared there because i hadn't cleared it out from a test that's fine so what i want to do is i want to select a specific item here and i want the item type and the item description to show up i want.
Default the quantity of one the cost and then i want to set the quantity here and the cost here maybe maybe on this one but we definitely want the user to be able to change these obviously so we can get the total this is going to be formulas and this is going to be formulas and this is going to be so let's go ahead and differentiate between in which what the user will be changing which is you know all these informations and not the formulas so i'm going to hold down the initial shift go shift and then hold down the control and go all the way up i'm going to add some conditional formatting for this specific one so first i'm going to format the cells and i'm going to give them this back color here and then i'm going to also add some conditional formatting here.
New rule i'm going to use a formula and i'm going to use the mod formula i want every even row so mod it automatically comes up using auto hotkey i'm going to format those i'm just going to go a little bit darker on this color here that's it that's so i'm going to do something similar on the other one so that's what i want a little bit darker cells now what i'm going to do is i'm going to add conditional formatting to the rest of them which is here holding down the control scrolling down here and then holding down the control a third time here and then we're going to go down and now i'm going to add some conditional formatting on these so basically it's going to be very light a new rule using the same rule in fact equals mod also the even rows here and then what i.
Want to do is format but just a very light color so i'm going to pick this color but i want even lighter so i'm going to go a little bit lighter here right here that's what i want okay so that's going to give it a very light color that's what i want so something nice like that okay let's go ahead and save our work and we now we don't need the grid light so we can remove the gridlines it's going to give us a cleaner look so what do we want now we want a date in here we want created by we've got a lot of information now we're almost ready to write some macros but i want some information in the summary here what do i want to know well i want to know what the total budget is that's important i want to know what the budget is and basically it's going to be i want to kind of a summary so this is going to be equal to whatever this is here that's our budget.
And then i want to know what would what would 20 below our budget be so let's just put in some number here just for the fun of it and then i'm going to put in let's just say fifty dollars let's just say a hundred dollars to be so it's easy and then i'm just going to put in a temporary formula here i'm going to update that formula in a moment once we get them all but i just want to get that total okay so we know our total budgets it's a 100 so let's format this so if our budget is forma i want to know what is 20 below the budget so we're going to put in some text here 20 below okay and that's enough and i also want our 20 above what's 20 above the budget if we go over.
The budget i want to know above the budget and then i want to know what the actual costs are and then i want to know the dollar difference here what is the dollar difference and i want to also want another percentage difference of that percentage difference all right okay i'm going to right justify all of these because they're going to be based on that so i'm going to right justify them here and now what i want to know is what's 20 below well 20 below is going to be equal to 100 times 0.8 and it's going to be that's going to go so our 20.
Below if it's 20 below would be 80 above would be 120 so obviously equals here 100 times 1.2 that's going to give us our so that's going to give us let's format these not the percentage one let's format those uh with a currency here we'll just go ahead and currency that's fine so we can get some dollars all right so now what are the actual costs of course the actual costs are going to be right here so there we go so what if we have a quantity of what if it took a little bit longer 1.2 and the cost was 100 so it took a little bit longer and then we'll just put in a temporary formula here i'm going to add some more onto that equals this times this okay so now we have our so.
Now we have our information 20 above we know this is job now let's put in some dotted lines i want to add to this a little bit more i'm going to put some double lines so format the cells i wanted this to stand out so i'm going to put the double lines on the top and bottom so it stands out a little bit more these actual costs will stand out all right and this one's going to be a percentage so i'm going to format it's already formatted from some tests that i did in here what i want to do is i want to put some graph or a needle graph which is needle gauge we call it the color-coded gadget that's going to be really cool and here i'm going to i need some information for that needle gauge so what i want to do is i want to show in basically a graph format you know are we above are we out or we below the budget so let's add some information in need of gauge here and what.
Do i want to know i want to know the actual cost in a percentage format but differently basically if it's at if it's actual cost if it's above the cost i want to know that it's it's going to be different it's going to be the actual cost is going to be either from 0 to 100 in this case i want a 0 to 100 percentage-wise based on if we're below the cost so basically if we are at 20 below i want it to show zero if we're at 20 above i want to show it 100 so that's going to be help.
Us just for our gauge you know it's really for a gauge and then what i also want to know i want to know the needle that's going i need to know the needle thickness needle it's just a thickness so that's going to show us for our gates really cool and what is the limit the limit is going to be 100 on that so the limits 100 and also i want another one view i want to be able to insert and search for additional projects by entering a project number so do project and then what we'll do is we'll make this white so the user will actually enter a project number here and then it will automatically load that's what we're going to do that's it that's all i.
Really need to do so let's go ahead and put some borders around that format those cells i'm going to use that same green go all the way around and then i'll just add in some border here okay good that's pretty much what i want here now we're going to put some formulas in here and this formula basically if they're exactly on budget i want to show fifty percent if they are below budget by let's say twenty percent i wanted to show zero and if they are above budget by twenty percent i wanna show one hundred so that's exactly how i want so how do we do that we can do that with the formula first let's just if there's an arrow i don't want to show anything so.
If there's an error and then what i want to do is i want to make a subtraction so it's going to be l5 i want the actual cost minus l three minus the twenty percent below minus l three that's going to get us the twenty percent below and i wanna divide that by the difference between the twenty percent above and the twenty percent below so that's what i want to do i want to divide it so how do i do that well again we divided by 20 above minus 20 below just like that minus l3 and then i want to multiply that times 100 because i don't want the decimal value on 100 or 50 or zero so how do i get that just multiply it times.
100 and then of course if there's an error comma and then we're going to put double quotations if there's an error because i don't want anything show there we go that's what i want so i wanted to work 20 above in this case we're 20 percent above i want it 100 that's exactly now what if we're on budget which we're exactly on budget i want to show 50 and if we're on 20 less so 0.8 then i want to show zero that's exactly what this wouldn't be a this wouldn't be let's change this to a number here so we're going to change that to a number and then the same thing with this is already numbered.
So okay we're good to go let's change this i'm going to format it as a number in case we want to use decimals and not general because especially in a number field so number that's good all right so now that we have our gauge and we'll set our needle that's a thickness that we're going to use and i'm going to show you that just in a moment okay great so now that we have all that information we're ready to write our graphs so how do we do that i want to create this really cool gauge graph so we can gauge it so let's start out it's really going to combination of two graphs so how do we do that well first we're going to insert and then we're going to insert okay and then we're going to insert other charts and we're going to insert a donut we don't necessarily need to select any data that's fine so i'm going to right click i'm going to click select data and then i'm going.
To add it and basically what we want to do is we don't have we don't necessarily i just want the background in other words that we're not going to be going on any data in this case so i really want five parts above and one part below so i'm going to use a comma i'm going to put five ones in i want five equal which is exactly right and then i want one that's large on the bottom and i'll show you how to do that so that's all i really want i don't need a series name also so perfect that's all i want so notice basically i've got five equal parts on one side and i've got one equal part on the left so okay so now what we can do is just click here on it and then we're going to format.
The data series and the first thing what i want to do is let's move this over so you can see both and the first thing what i want to do is i'm going to rotate this because i want this big section on the bottom the best way to do that is rotate it 270 degrees that's going to place this part on the bottom now what we can do is we can format those so let's continue and format this data point or all the data points this particular one i'm going to give it a fill i don't want any fill on this so no fill on that one these i want to basically go from green to red and kind of a blend this we don't need also so how do we do that well we can do a fill effect so i'm going to select on this one specifically i'm going to go into the fill and i'm going to go into gradient fill here i'm going.
To set it up so this particular and what do i want i want a linear which is correct and i'm going to start i want this one on the bottom so this linear down here is the one i want to focus on but the colors i don't want so this color here i'm going to focus on the dark green i want dark green here like this color green here then i'm going to just focus on this dark green here and then the next color i'll select that and i'll go to just a bit of a lighter green like this that's what i want to go from darker green to lighter green now what about the next one all i need to select on the next one go back into fill gradient fill in this case all we're going to be doing is switching this time i want the lighter from the darker i'm going to use this one right here this one the bottom left to top right but in this case we're not going to be the dark green so we.
Need to change this color we want this to more of a yellow color so i'm going to click on this yellow and that's what i want so i want to go from dark green to yellow good that's it now what i want to do is i'm going to select on this one i'm going to go back into the fill gradient fill here again we want the yellow but we don't want the green so we're going to change this to maybe an orange like something like this orange here so now that we have that orange but now we just have to get the fill right so now what i want to go from left to right and orange so it's going to be this one here that's what i want selecting on this one here you're going to do pretty much the same thing gradient fill but this time we're going to go from yellow.
To more of a darker orange so let's change this to more of a darker orange something like this now we've got the darkness the only thing we need to do is just change it around from left to right so we can do the direction in this direction is going to be like this that's what i want and the last one we're going to go from orange to red so let's click on the last one again go into the fill gradient fill we want this darker one correct but we're going to change this to the red so this one we're going to go to just a red right here that's what and now all we have to do is change it so in this case we're going to go from upper left this orange is a little bit probably this one could go a little bit lighter i guess probably probably something like that.
Make sure we've got it right okay so this one we want to go from upper left just so we can see it upper left this is what we want upper just like that now we can change it back to our original orange so we can see it now we can change it back to original orange just so we can see it better okay good that's what i like that looks nice okay so now we're good so now what i want to do is click on the shape itself and i don't want to fill on that so no fill on the shape there and i also want no outline so no click no outline that's what i want and then we can just square it up we don't want a square feel so let's put it a little bit in the square all right now i just want to make it a little bit smaller so i want to fit it in our area so we're going to put it in a box and i'm going to make it equal to this border here so i'm just going to bring it up here so it's equal to the border that way it gives us some guidance there perfect okay so what else.
Do i want before i move that into place i want to put a few more things inside here i want to know again in this case the actual costs and i want to have them here and i also want to know the labor costs and i also want to know the material cost we can abbreviate that material costs so i want to know all those things so what is the actual cost we have those of course right here i'm gonna repeat it again we have it in a few different places but i want the actual cost here i wanted to show up here because i wanted to show up under the gauge okay so we've got that we're going to put in these two formulas so let's go ahead and sum out our labor cost i want to actual cost so.
Equals sum if right we're summing it if and what is the range and the range is going to be basically on this item type here so we're going to go all the way down we can go down to let's say 999 it doesn't matter it's just a large row and then what is the criteria the criteria in this case of course it's labor so we'll focus on labor types and what is the sum range we're going to be this total so we're going to use the same thing and just go to 99999 gonna be no data right now which is fine okay so let's copy that the only thing i want to do is change that sum if.
Copy that and then in this case we're gonna go to material so i'm gonna copy this i'm gonna change this to material i'm gonna double check on those names those are the ones that are going to come from our material so material okay so we've got that and then i'm just going to left justify all of these that's what i want now let's go to the item list so we have materials i should add an s on to that and labor so that's it that's all we have to do put an s on the end of that so we're exactly right all right so we have material cost labor costs and as soon as we add our item zones so as soon as i put labor in here that's going to give us a number and as soon as i put material in here and give it a number of course give it the cost here let's just say 1 120 and then give it the formula here equals okay so let's write up that formula now because i want.
To make sure that there's actually a value here in here so let's write up the formula and bring it down to all of them so equal so we're going to start out with equals if and it's going to be and two conditions because i want two conditions this one should not equal empty does not equal empty which is the j column j the quantity column and what else i also want to make sure that we have a cost cost here that cannot be empty as well cannot be empty okay so once we have those two we know it then we can only then can we multiply j times k10 okay otherwise i want it empty in case if one of those are blank i want it empty that's the formula that i want now i can copy that down copy that.
Ctrl shift down arrow and we'll actually go down a little bit all the way down here actually and then what we'll do is just paste in that formula and i want the same thing here so i'm going to just copy that all the way down and it's going to copy over correctly right click copy that and then bring that same one over here right click paste the formulas okay that's what i want and of course there that's all i need now we have our now let's go ahead and save our work now we have our material costs here we have our 80 let's just set the currency on those so we know that they're all currency perfect okay so now we're good now all we need to do is build out that gate so let's bring that back over here right where we want it up right there line it up.
Accordingly now i just want to gauge so how we're going to build that gaze that's going to show it very easily all we need to do is just select on this information here that we added earlier and we're going to insert and this time we're going to insert a pie chart so we're going to enter the pie chart here that's going to give us what we need to have so we have the three data we're just going to hide it so let's take a list we have our basic pie chart we don't need this legend here so we can remove that delete that and we actually have three notice there's this little light blue that's the one i want so that one that everything else is going to be hidden so this one we're going to fill right click let's go into the format the data point that's what i want bring it over here let's go into the fill here i want no fill on that outside click on the data point here fill on that.
I want no fill on that one but this one i want to fill on so this data point right here that's the one i want to fill that small little one that's the one i want to solve there you go that's it that dot if we add increase this two it'll make it larger so i want to make that small one black the only thing we have to do is we need to rotate it just like we did the other one 270 degrees 270. that's going to give us our information we want now actually cost of 300 so it's not going to show up in the right area that's fine but as soon as we add some proper data in there it is going to show up so let's do that right now before we move forward i'm going to bring this down here so let's add some normal cost here so we notice we have a 200 100 we can't have that so.
