An invoice knowing how much money you actually made on that invoice that's what we're going to be going over today i want to know the costs i want to know how much we sold what did it cost us what did our actual labor cost us the money we invested in our technicians so i'm going to show you how to do all that this week how to create your own one we're going to do it from scratch we're going to be starting with a blank sheet and we're going to be creating this invoice together so i'm going to show you every step of the way every line of code formula format and function all with you today so i'm really excited on this training i bring you these each and every tuesday absolutely free even this workbook is free all you need to do is click the.
Links down below either with your met email or your facebook messenger we're gonna get that sent over right to you if you do like these trainings there are many ways to support them and keep them free each and every week for all of us the first is which with our patreon campaign our patreon members get exclusive benefits nobody else gets such as early workbooks pdf code books which display all the code all the icons and pictures you see in these trainings come that in the resources package you also get exclusive details on our upcoming sales and recurring information on that we've got lots and lots of benefits there on our patreon campaign for just a few dollars a month that's a great way to support us so we've got lots of things for you there also.
If you like these workbooks i do have a pack of 200 workbooks and that's on sale for just 77 that's some great ways to support us and keep these trainings for free all right we're going to get started this is a sample so this is exactly what we're going to build we'll be able to save it we'll be able to create a new invoice we'll be able to load previously saved invoices just by entering the invoice number here we're going to be able to track items we're going to have item types we will also have service types we'll be able to select labor for specific employees and then we're going to be able to determine the cost of those employees we're going to be able to calculate that.
All in this amazing graph we'll be able to know the profit we'll be able to know the total cost and of course the total sales all of that is going to be available to us on for each and every invoice so we're going to create this live we're going to have dynamic tax in total so we're going to be able to create the total we'll show you how to create this really cool invoice background we'll be using conditional formatting some simple formulas advanced filters so i've got so much in this training all right so this is the sample what i'm going to do is i'm going to save that in case we made any changes and i'm going to put that away right now and we're going to start off with this.
Blank sheet here now this blank sheet of course is going to be our invoice now what else is in this of course we have an invoice list that's going to track all the invoices that we previously created right we've got invoice items these are the individual items that are going to go on the invoices themselves right we also have a list of customers we're going to be able to create and add new customers forgot that to show you in a pop-up and also that's going to be a bonus coming up since i forgot to mention it and we've got the technicians different technicians how do we calculate their actual hourly cost we're going to be going over that that's really cool and also we've got an item list a list of items right and the cost of those.
That we're going to need and so i'm going to give these a specific color i want to use some specific cells for some formulas and some functions that are going to help us moving forward and so we're going to put those right in here and then what i want to do is i want to color this blue for our invoice right i want to create a kind of a blue color here but on the header and then our invoice is probably going to e through j e through j and we're going to go all the way down to let's say about 34 on those so i want to put a background on those just so we get some kind of.
Framework for around our invoice so what i'm going to do is i'm going to put a border around there just so we know where our invoice is i'm going to choose a little bit of a thick border and i'll go with maybe a dark blue on this and then we'll just go all the way around that way we kind of have an understanding of where invoice is going to go the rest of that i'm just going to give it this blue background here give you a little bit of a blue color we'll just go all the way to let's say that's a bit too far all the way to z is sufficient for our purposes here and then i'm going to format those and just give it a little bit of a fade out color not too much so we're going to go probably with one of our primary colors here and then down to our second color here which we'll use so just this is sufficient then we're going to create our background color at that remaining so our background color we're going to highlight all this.
And then what i'm going to do is just those those first two rows i'm going to hold down the control and i'm going to continue the rest of it down here and then also down here so that just kind of gives it a good background here that we're going to cover okay so now i'm going to cover that this blue color here which is going to be let's call it this lighter color all right now i'm going to give it that primary background color which is this one right here and then we're good to go on that okay so we've got our background color and we've got our our basic invoice here so we're going to put that in there now what i'd like to do is give it a title and give it some information so i'm going to add some shapes so the first thing we want to do is we want to add our main shape so i'm going to.
Insert a shape here and then that shape is going to be for our title so i'm going to enter a text box here and that's going to be for we're going to call this service invoice so i'm going to do caps lock service invoice and since that's the title of it we're going to give that a bit bigger i'm going to format that right i'm going to put no outline on that and we're going to put no fill on that we also want to increase the font here to about let's say 22 and then we're going to make it bold and italic and we're going to give it maybe one of the darker colors here so something like that service invoice okay that should be sufficient here and we'll drop this up here we won't be.
Using grid lines here so i'm going to do the view and we don't need those gridlines i want to show that and what i also like to have is some company address here so our company address is going to go in the upper right so i'm going to insert shapes here and then inside that one another text box but we'll do a larger text box around here and that is where we're going to have our information so we're going to put freder's this is our our company name garage and then a new line so i'm going to do enter and then we'll do 123 main street and that'll give it the address and then any town usa and then just give it a zip code so that's sufficient there we have a zip code there.
And also what i want to do is put in a phone number here so let's add a phone number there and then what we'll do is we'll format that looks a little bit big so we'll make it smaller here
And then we'll do 310-555-1212 okay so that's sufficient for the phone number now let's add the font we want to make it a smaller font i don't want it quite that size so we'll do it something about 10 okay so we'll do a 10 we don't need 11. and then i do want to make it bold i want to make it colored right i want to give it that same color and then we'll expand it here and then also i want to do it italicized here and also we have too much spacing around that so what i want to do is i'm going to right click that go to the format the shape.And then we'll go to the text box and i'm just going to remove any padding around that we don't need any margins or padding around that so that's going to help us it's not necessary and then just increase that that looks good okay and that looks good so now we've got it for now what we want to do is we outset some logos and some information here i want to put the invoice number so inside invoice number and then that invoice number is going to go in j so below that let's go down put an invoice date and then an invoice date so that invoice date will go here let's just say 131 right and then we'll format that a little bit and then what i want to do is i want to right justify these but i'll do one more i also want to put the invoice status here we're going to be creating an.
Invoice status so i'm going to take all three of these and i'm going to right justify those and i'm going to take all three of these and i'm going to left justify those i'm going to format this as a short date so that'll be fine i like that here so our invoice number will go here and let's say our status will go here this will create a drop down list of statuses that we can choose here we don't need a border on that so we can again no fill here and then what we'll do is no outline here all right so we're also going to put a logo here and we also want a customer address right we want to put that customer that customer address is probably going to go into g5.
The customer name and then six and seven will be the address and then eight we're going to put in all of our headers so we're going to start our headers off right here so this will be the description and then we're going to have the tech here's going to be the tech technician we want to know who that could be a technician or it could be an item name so we'll put backslash item could be either one of those previously back i want to put the date if there's a service date on that an item type on that let's capitalize that just as we are item type okay previously before that i want to do the item id item id i want to know what is the id of that and then also i want.
To know the invoice item row invoice item row what does that mean that's going to be the row where it's stored remember if we have a set of invoice items i want to know that row three four five or six where is it stored on our database so we're going to put that directly inside here all right so beyond the description i also want the quantity what is the quantity of how many of that and then i also want to know the amount the reason amount and the total for the for that line item also i want to know the item cost this will be not be printed right the cost will not be printed so it's off and i also want to know the total cost so this is so the customer will see this when we print it out and the admin we will see this so i think that's going to be perfect so i'm just going to give those some nice borders some colored borders i'm going.
To format those cells and using that same blue that darker blue so i'm going to use that border and to give it that a little bit thicker line here we'll use this actually this is sufficient here this color and then we'll go all the way around with the outline i think that'll be sufficient and then also i want to bold the font here all right so that's good and then just a centered and give it a little bit of color here so we're going to center that and then i'm going to format those cells and then what we'll do is we'll use a fade color so i'm going to use the fill here and then fill effects i'm going to use our two primary colors here that we've been using which is the darker one into the lighter one those are almost the same here but they're a little bit.
Clue and then so let's go with this lighter one here i like that better okay so there we go so now we've got this little fade out here it's a little more obvious okay so we've got our primary so i want to put inside f5 i'm going to put here the customer customer and we're almost ready to create our buttons so that customer name will go in a drop-down list right here we're going to right justify that customer name the address and that okay so we've got it primarily and we'll put some lines around here and i also want the totals to go in here so here right i want to put we have three lines so i'm going to put the subtotal here sub total then i'm going to put the tax here.
Now this will be temporary because we're going to change that name and the total here is going to go so we've got the total the subtotal and the tax right here we're going to right justify those bold them and then the amounts are going to go here so these are going to be all amounts we'll put those in here this will be a sum so i'm going to put borders around here so format those cells sorry it's off the screen bringing the borders up here and what i'd like to do is i want to have the left and the top border maybe the thin thin board let's go with a thin border on this and the top and then the middle not that right here and right here okay let's take a look at that i like that and then what i want to do is here i want to have a footer message so i'm going to put a border inside here let's scroll up a little bit so you can see that what i'm doing here.
Format those cells here and then what we're going to do is we're going to put that thin border right up the top now this we can use a merge and center so our footer message is going to go right here and we're going to emerge and center this and i'm going to go to the left and the top so we can put something like in thank you dude thank you for your business i like that now we just need to bold it and italicize it make it maybe a little bit bigger okay that looks nice so that's a good footer we're going to be this tax line will be dynamic based on the tax that we're charging okay and then the rates here so we'll be adding that in more it's going to add some lines in here some break point line so we can separate those columns here on the invoice so i'm going to format those.
Cells just as we've been doing and i'm going to use this same color but we're going to separate it with this dotted line here on the left top not this one not that one but this one here okay
So i like that there we go that looks nice now we got a good framework let's save our work we got an idea of what we want to do i'm going to add just to make things a little bit easier to read i'm going to add some conditional formatting here so i'm going to hold it so actually we'll go to here so all the lines that we're going to have and i'm going to add some conditional formatting here let's hold down the control once again the shift go here the control for non-adjacent cells and then down here on holding down the shift so now we've got two sets of cells and what i want to do is i want to format those cells based on what is located inside here and based on odd or even so.We're going to add actually we don't need to form we can format let's add a border in here just the center border that should be sufficient just as we did previously and what i also would like to do is add some conditional formatting in here so conditional formatting new rules it's going to be based on two conditions so using a formula we're going to use equals and again two conditions the first condition is c or and whatever row so we're going to remove the absolute from that should not be blank does not equal empty another condition is must be on an odd or even row right so so i'm going to do mod that's our auto hotkey that automatically typed it in now this is going to be for even rows because it ends in a zero so for this what do i want to do well i just want to.
Add a little bit of a lighter color so we can show those alternating i'm going to give it a fill and maybe a maybe a little bit more of a lighter color so we've got some colors here but we want to know is to use some recent colors so our recent colors are located right here in fill effects so we're going to choose one of these lighter colors like let's say this one here and that's a little bit lighter color so that's our lighted color so we can find that in the reason and click ok now nothing's going to change on that because we haven't added any data but as soon as we start adding some data we can see that we have a nice alternating row and that's what i want just a nice look so we can alternate okay very good so that's looking good i like the way that that looks and what i want to do is just give it a border around here because we're going to have specific items that are going to be centered so i'm just going to put a border around this.
Here there and now inside here what we're going to have is we're going to have these databases so it doesn't necessarily matter what the look is here but we do want to make make sure it's separate so i'm going to give this kind of a lighter gray this is going to be for admin purposes only we'll be able to hide these two columns so our invoices coming together here we get an idea of what we want but i also want to know we want to be able to search an invoice so we want a user input field so we're going to color that white and we're going to call it search invoice let's spell that right so our search invoice is going to be here we can move that to the middle here and then right justified all it wouldn't matter okay what else i want i want to know the service.
Sales right so how many what's the total service i want to get some total so let's put something in here invoice totals so i want those totals as you may have seen in the sample invoice totals here we're going to merge and center that here we're going to bold it we're going to put some borders around it here so any border okay so now here i want those totals to appear here so first is going to be our service sales or i want to know how many sales we need service sales what is the total and i want to know the item sales how many items did we sell i also want to know the service cost what is the cost our actual cost of that service our employees cost and i want to know the.
Item cost and i also want to know the total profit what is the total profit that we made on this specific invoice okay so now let's just give it all borders here and then i'm going to use the double line here on the top because it's a total so we're going to use this double line right in here and we're going to put it on the top good i like the way that that looks that's going to give us some consistency we can just kind of color that a little bit of our lighter accent color which is here to stand it out a little bit so we know that we're going to be putting in now we're getting we know the service sales our item type whether it's service or item is going to be here so we're.
Going to base those totals but let's get some data in here first okay so the first thing we want to do is we want to we want to create a customer drop down list now we know we have a customer list here and i've created already a few named ranges that will help us move things along so if we take a look inside the formulas and name manager we see that we have something called client name client name okay our customer name that would work let's change that i just want to keep consistent i'm going to change that to customer name i just want something a little bit more consistent customer name okay and that's basically our names on our clients and our customers so clinton click.
Ok and then going to close it so what i want to do now is create a drop down list so data data validation here but i also want the user to be able to enter additional information if it doesn't exist so i'm going to unselect this right that means they can put in any value they want there's no rules and the reason is is because they may want to add a new one so what i'm going to do here is going to put in equals customer name and that's the one so now when we drop down list we have a drop down list of customer names that's customer names again taking a quick look.
Are in here this using an offset formula based on b2 starting on our header row and then removing one row down we're going to count all the values where customers a and column a so basically we're counting all those values to determine how many rows that encompass our data as we add new clients or customers we will automatically have that ability okay and click close okay so i just want to make a few changes we're going with customer here so i'm going to call this customer id right just so we have some consistency and i also want to call this customer name although it's customer.
Name just wanted to make sure it's consistent customer name okay so we have that there we go and again i want to make a change i want to i don't we're not really using customer ids in this one but i had it for okay it's good i like that customer ids all right so what i want to do is i want to add some information here the first thing what i want to do is i want to know what row this customer is on this list now you notice they start in row three that means betty white is on row three well when i select betty white here i want to know what row betty white is on i want that three to show up here so the first thing what i want to do is i want to use customer row that way.
If somebody enters something other than other than what what is already in the list i want this to show empty so how are we going to do that well the best way to do that is to use a match formula and use if air around that so let's go ahead and put view we're going to need that formula bar in here so i'm going to put equals if air and then what i want to do is i'll match i want to match i'm going to look up this name and i want to run a match based on that customer name okay and i want an exact match so we're going to put in 0. now i want the row that it's located.
On our first row is row 3. so i'm going to add 2 to that if it's found it would return 1. but when we add 2 it's going to return 3. now if there's an error meaning it might not be found i'm going to put in blank there okay so that way 3 is found betty white is for 3. so i'm going to bring those then i'm going to left justify this if we decide to enter something other than that right that's not found this is going to be blank when it's blank and a user makes a change here i'm going to let these you know hey this name doesn't exist would you like to add it if they get yes then they're going to click a little icon here and they're going to be able to add it okay.
So we know now the difference between a name that exists and one that doesn't exist okay i also want to know what the role of the invoice is invoice row i want to know just like we do the customers invoice row just like we do with the customers i want to know when they search for an invoice here i want to know what row it is on just like we did so here in our invoice list right here i want to know that invoice id number one is located on row three now we've got some named ranges in our name.
Manager and it's called invoice id here invoice id so that is also an offset formula based on the invoice id so when i use the match formula here i can determine what rows on just like we did here i'm going to imagine based on whatever they're searching for here right so that's one way but what i want to do is in this case i want to know what row whatever has been entered here which row is the current invoice when they search for it we are going to find a different one we're going to put that differently so the invoice row is going to be a match based on this what's inside.
Right here in j1 and we're going to increase the font on that a little bit so i want a little bit bigger okay so let's do that equals if air and we're going to run a match that match is going to be based on this invoice number we're going to use invoice id for that we also want a 0 want an exact match and again i want to add in what row it's on and i want to know so the first row is on 3 therefore i'm adding 2. if it's not found i just want to show blank here so that's all i want to hear here just blank okay good so we know that invoice 1 is on row 3 if there's an invoice.
That isn't available it's going to show blank okay perfect i want to do pretty much the same thing for the next one i want to know if they're searching so this is going to be for our invoice whatever is located here but when a user searches for it i also need to know the row because if it exists i need to load it so we're going to do something called search row here search row and we're going to use the exact same formula here that we did here except we're going to be using a different original cell right that cell that we're going to be using it's not j1 we're not searching that lookup value in j1 we're now searching for it based on l1 right that way when they make a.
Correct search for correct we know what row it's on right once we know what row it's on we can simply place the id right in here right in here and right inside here we can just move this id into j1 that's all we need to do to load it and that will load up because we know the row all right so what else do we do i need to know the next invoice number as i create new invoice numbers i need the unique invoice number for every single new invoice so how do we get that so we're going to put next invoice number and we're going to use the max formula for that but if but if there's no invoices that exist it could create an error so we're going to use equals if.
Air again in this case we're going to use the max and we're going to use max based on the invoice id but i don't want the maximum i want the next available so we're adding 1 onto that and if there's an error i want the first available which is going to be number one the first available invoice id if there's an error that means since we already have one uh invoice here we know the next one is going to be two okay so we've got that already and the last one i want to know the difference between invoice load and not what does that mean i wonder if this could be true or false now the reason is when this invoice loads right we're going to be making a lot of changes we're.
Going to be making changes to to the item id the technician right so when we make those changes i want some things to load up when i add an item here i want that item information to load here however when the invoice loads it's also going to be making a change here and loading information so i really want to differentiate between those two types of changes one change when the user actually adds the information manually the other changes when v we search for an invoice number and it.
All gets loaded in automatically there's two types of change so i want to differentiate between those two type changes and that differentiates is going to be located directly inside b6 okay so that's all we need for now now we're ready to add some buttons and then we're going to add some icons so saving our work we also want to add a really cool background to this so why don't we do that page layout i've created a background and we'll call it background here and i've got this really cool background here that i found and i'm just going to insert that now it's been sized accordingly so that it's going to end up directly on that because i have size lines i've sized the background accordingly so that's cool and the reason it's showing up here and not here because.
There's no background here right if i were to put in a background here like this it wouldn't show up right so you'd see it's kind of differential but because there's no background here it shows up on the invoice but it doesn't show up here and that's kind of a nice effect great so we've covered that so far now let's go ahead and add some buttons in so i'm going to insert a shape we're going to use this rounded rectangle here and i'm going to put it right here we're going to add some buttons here we're going to get the first one just the way we like it and then what we're going to do is we're going to then let's go ahead and uh add a style like let's say this style but i don't quite like that background so i'm going to give it a shape background that's a little bit similar to our theme which is this one here we're going to call this save and then backslash update so.
This button is going to be used both for saved and update and then what i want to do is i want to center this here so i'm going to do in the center here and then i want to right justify it okay that looks pretty good but i want to add a little bit more onto that we'll give it a width and i want to give them all the same width so let's say about 1.3 should be sufficient and then what that's going to do is allow enough room for the icon okay like that might be a bit large we can go slightly lower 1.29 okay that's pretty good so what we want to do is i'm going to duplicate that because i want so i'm going to use ctrl d that's going to be for our new invoice then what we want to do is we want another one for our print invoice and another one for our save as.
Pdf so i'm gonna move that last one up and then i'm just gonna hold ctrl and then i'm gonna click on all those and i'm going to go in to our shapes here and then i'll just go ahead and line and i want to distribute them vertically so they're all safe okay so now we can rename them so this one's going to be new invoice this one we will have print invoice and this one we are going to have save as pdf save as pdf i like that but i need one more button to add a customer so i'm going to duplicate that i'm bringing that down here now we don't need any text on this so i'm going to delete the text on that one and i just want a square button so i'm going to move it down here and then we'll just change this to let's say 0.26 so it has both the same.
Width and the same height and now i'm going to do is add a plus onto that keep that simple i want to make sure we don't print this so i'm going to right click and then format that shape we'll go into the properties and we don't want to print we're going to unselect print right we don't want to see this button on the printed invoice we just want it on the invoice for the user good so i like that that looks pretty good we've got all of our buttons lined up i'm just going to left justify that or center it and make sure that they're all lined up now they're lined up now we're ready to add some icons so what i'm going to do is i'm going to insert some pictures and i've got some already saved up so i'm going to click on this these icons here and then we're.
Just going to select all those and put insert okay so we've got them all but of course they're all size accordingly so i'm going to change the height to 0.2 and i'm going to reduce them all down so for this particular new invoice this plus here i want to add that here but i also also have a main one so let's put this plus this is going to be used twice it's going to be used once for the customer and i'm going to duplicate that we're going to use a smaller version for the new invoice this is our little invoice icon we can zoom in a little bit and take a look at that so we have to strain our eyes and so we see our invoice here so i'll use that for both save and update that base icon so i'm going to duplicate that using control d and i'm going to place that right in here so new.
However for the new invoice what i'm going to do is i'm going to use this icon right here except that's a little bit big so we'll go to 0.13 on that and we can add that so we've got our new invoice now our save we're going to use this a different icon also smaller than it is so we're going to put that right here and i'll drop that down to 0.132 so that they're consistent and want to bring that above so i'm going to bring that right on top i want to bring it right here on top so we've got save update we're going to use that check box on the upper right corner we've got the new invoice in which we're going to also put that in the upper right corner we have let's see we don't need this cancel so we can get rid of that we should add a delete maybe i'll be adding that on to uh for patreon because i didn't add that save as a pdf we're going to put that right here.
And print invoice i'm going to be using that right here so we've got premium voice so now all we need to do is make sure they're centered here and then i'm going to group them do the same thing for each one of these here and i'm just going to group them not center them because we have multiple icons in there and the same thing for here holding down the control selecting all the shapes and grouping them together same thing for the print same thing for the pdf here this one we can make sure it's aligned and group it okay great so we have it i'm going to do the same thing for this one i'm going to use my selection tool i want to make sure that both of these are centered here so oops i did that okay so they're both centered vertically and horizontally and then group them now with all.
Of those using my selection tool i want to make sure that i am not sizing them i'm only moving them with the cells so i'm going to do all of them including this one here zooming out here and then what i'm going to do is i'm just going to right click here and select those again actually turn off the selection tool then we right click format all the objects go into the properties move but don't size with cells that way if we change the our column widths it's not going to change those shapes okay good so i like that it's looking really good now notice keep in mind when you zoom in the background changes but at 100 it's just perfect and that's what we want.
Okay things are looking really good and really up we've got all that we do need to add an icon here for our invoice so i'm going to go into pictures and we're going to open up and i've got an icon right here for that so we're going to insert that right here this fretter's garage this is the name i've gotta like that cool icon and then what we're gonna do is i'm just gonna place that directly inside here so we've got our icon here make it a little bit bigger so we can see it moving this over give it we'll move things over a little bit so we can give some space and then the service invoice we can bring this over a little bit here center that one i'll center that one both directions here all right good it's looking really good we've got a really nice invoice okay looking good our invoice is now really professional we've got a good looking logo.