Create Invoice in Excel for Quality Assurance Effortlessly

Create invoice in excel for Quality Assurance

hi in this session I'm going to show you how to create an invoice and I'll say you are selling some items and you wanted to create an invoice for your customers and you had situations where when you entered in the quantity of an item you wanted to pick from a list of items that you had maybe you had a set amount of items 10 to 20 different types and when you selected that the price would automatic automatically come up and when you enter in quantity and it would automatically calculate that quantity and also calculate the total and whatever sales text that you had and give you a grand total that you would give to your customer and this would show up and you can print it out eventually you can print it out into this type of form and one page 8 by 11 and have by 11 form and give it to your customer so let's go ahead and see how we can create that now the key to creating these type of forms is all before you do any of the formatting do all the nice colors and font changes and formulas is to set your text in the appropriate location so what I've done is basically I've just copy all this all this and put it into another sheet and it just doesn't have any formatting at all and what what this does is it gives you a a space to place it where you want to place it before you do a lot of the fancy formatting because once you do the formatting uh you might go back and change the the way that the text is and you always want to put your text in the place where it's most appropriate before you do any formatting because you'll find that once you do some formatting you might think that uh the placement of the text is not in the correct location and you have to adjust it and do formatting again so it's always best to do your text placement before you do any formatting so what we're going to do is now since we just have our text here we are going to format it so this is just all plain text right now there's no formulas or anything here so what I'm going to do is first we want to know how we can get that nice white background instead of having these grid lines so what we want what we do first is we want to select the areas where we want to provide that nice white background so if we looked at our previous example here pretty much the whole thing that whole Space was all this nice white background and so what we want to do is we want to change the gridline colors so I'm going to select the area that this form is going to Encompass and and do a press control1 to bring up the format cells and when the style doesn't really matter I'm just going to stay with the default style the color is what is key we want to change that to a white color change that to a white color and we want to have all the cells change from Gray to White so I'm just going to click go ahead and click inside and outline you can see that now the the Border boxes are selected that just means that the it is selected for those lines now if we click another color let's say for example black and we select the outline you'll see that it shows it up here but since we selected white it seems like it's not selected but it is because basically it's white so once I click okay you'll see that all the gridline colors are gone so now this now we can start to do some of the formatting that we wanted so we can select here that invoice that was blue and we can go under cell Styles or you can just paint it whatever color you want I kind of like the cell Styles here I'm just going to give it an accent here and what we notice also since we had originally selected white for our border colors the borders in between here have turned white so I'm going to turn those off I'm going to select that and go and press control1 to bring back the format cells let's select our color maybe it it was this color blue accent one yes blue accent one and just select that now it's changed now you can see the color is different I can probably increase the font size here a little bit I think I had at 18 double click it to increase the the size here see how this is cut off here I probably double click here to increase that size so this part was all white so I didn't have to worry about that this part there was a gray background so I'm going to select that and I'm going to give it the there's none here so I'm just going to go select it from here and I'm going to choose the white background one there and then get also get rid of these lines here whoops I unselected that control one here and let's go back to the gray here we're going to select that gray and select the middle part and so it's going to get rid of those lines so now those are gone so now I also want to have the the dark lines for here the Border Lines for here so I'm going to select that area and since the default is black lines I can just go ahead and go under here the borders and just select all borders and I can probably do the same here for all borders and this one all borders and see how the the text is cut off here I can probably just increase the tech increase the column size there and let's see I think I wanted this one I just wanted the Border all around it I don't want it in between I just want it all around so I'm going to select these two and I'm just going to select the outside borders all right so it's got that and I wanted that to be a little bit of shade of red so I'm going to go and not that red maybe if I go to cell Styles this the accent maybe I'll give it just an orange accent accent six and now I want to get rid of that line there right so I select that control one and try to find that orange color I think it was this color orange accent and six and then just get just tick that now that that color shows up there so that color should be that border should blend in and now this one I want to change that to red and also Center it so I don't want to do is Select my selection first all right and then I press control1 in the alignment tab go horizontal Center across selection and click okay so what it does is it centers it across the colelection that you had if I did a merge and I'll show you what happens with merge right now if I did a merge what it's going to do it's going to merge all these cells into one so for example in the previous I had this all you see this is all CDE but you don't see the lines you don't see the lines but if you go here you see oops if you go here you see the lines right here right if I go over here I'll still see the Border here for each cell now if I do merg in Center if I select this and I do merge and center you can see it's all in one cell now in a way so that's what merge and center does and that's the difference between centering across the selection it just centers it across it but keeps the cells intact whereas merge and center merges all the cells together into one so I'm go ahead and do the same here merge and center merge and center and now I have pretty much my my format for it and this invoice date this is the way the Excel sees dates it sees it as numbers and the between the period is on the left of it is the days and on the right of it is the seconds in a day so for example this one represent this number actually represents September 3rd 2012 so what we want to do is probably just change that to a date format right here you can enter in the invoice number whatever invoice number you like to provide so I want to do is also so kind of copy the formatting here to these other cells here so what I can actually do is I can just select this row and click on the format painter and just apply it here and you can see it just copied all the formatting so you don't have to keep going each down each line to to do the merge and center it does it all for you and I actually don't need this cell this portion here so I'm going to go ahead and take it off I'm going to do control one and I'm going to go back into the border here go to White and what I want to do is I want to get rid of the I want to get I want to turn the borders in between white and then the ones at the bottom and also the ones to the right that one I mean left white so if I click okay pretty much it should have taken care of that you can see now all those are really just white borderline cells so where do I get that drop down now that drop down is going to come from a named range so basically what it means is I would just select a range of celles and give it a name so for example I selected this cell and has a prod name one let's go ahead and show you how to do that let me just go and delete this name range first and I go under the name manager and I'll just go ahead and delete prod name from there yes click close there's a couple ways you can do it but the easiest way you can probably create a name range is just select your range of cells and up here go I'm just going to Ty prod name so now basically it has selected this selection of cells is called prod name so if I go back to the name manager and I select prod name you can see that it refers to cell A2 to A10 and that is how you select or create a named range so how do we get that dropdown capability so since I copied this over it's called widget One widget 2 I'm going to delete that because that was from the old that was from this one actually for our new one we called it P name 1 P name 2 p name 3 So based off of the named range that I created I can actually go back and do a data validation for that name range the data validation feature is in under the data tab under data validation you click data validation here and I want to create a data validation based on a list so that list the source is going to come from that named range I created prod prod name I can either if I remember I can just type equal prod name or I can just press the F3 key and that's going to bring up the paste name function and I want the prod name click okay click okay and then I can select it it'll be product name one two or three p name one two and three p name two I can just do it P name one p name two and then maybe just pame three you can see the the selections available here too now how do I get the price to come over automatically well that's going to be a v lookup so what I want to do is create a v lookup for that product name if it sees that product name product name one bring back $10 product name two three bring back the respective prices so it's a v lookup so I'm going go ahead and type equals V lookup and just tab to complete that look up this name under this table the table array which is here and comma and the column index number is which column do we want to bring back I want to bring back the second column one two second column and I wanted to have a exact match so false is zero you can either type false or the number zero so we'll type false Here and Now it's 10 2030 right so 10 product name two if we go ahead and just click on the fill handle here and bring it down you'll see 10 20 30 oh but now we have this na but that's kind of kind of irritating so what we can do is we can say here if there's an error there bring back blank if not bring back the price so what we can do is type an if statement so I type if if this lookup comes back with an error and that you can use the is error function if that results in an error then two double quotes basically give me a blank if that's if this is true give me a blank if not then I want you to do the vlookup Excel so I'm just going to go ahead and copy that contrl c and then contrl V to paste and then close parentheses and press enter whoops what did we do oh we needed another parenthesis here all right then enter Excel also said we needed another parentheses at the end so it was smart enough to figure that one out click yes and I can go ahead and just drag that and copy it down and now it's blank so it doesn't when it sees that when it sees that there's there's a result it doesn't find anything the results an error it will be a blank and what about here where it automatically calculates the price well that's basically a formula so I can just say since it gave this amount since I copied it over I can just say here equals the quantity that's is shift 8 to get that multiplication symbol times the price and then press enter now if I try to copy this down you see I have also the same thing here now I also want to create kind of an if statement to say okay if if it's blank over here then then make it blank don't don't do the formula if there's no number here then go ahead with the formula so basically I'll start off here actually what I can do is this little neat little trick if I can select this first cell and drag it down so I just made the whole selection but this one's the active one so here what I can do I I can create an if statement if this cell is blank okay so there's a is is blank statement is blank if this cell is blank and I'll click that cell close parenthesis if that cell is blank then two quotations blank otherwise then multiply B17 the quantity times the price and go ahead and press control enter and when you press control enter it will create the it will go ahead and put the formula in there now in cell I77 and also in i18 i19 everything but if you just press enter it's just going to put it in that cell so I press control enter and it will activate this formula for all the cells we found an error in the formula so what it did what I did was I forgot to put the closing parentheses there but Excel figured it out click yes and boom there we have it so now I can just say okay now it's pretty much done now I can just kind of verify right oh wait we still have our subtotal so this subtotal formula is basically equals the sum of this area here right oops press excape it's going to be the sum of this area I select I17 i22 close parenthesis press enter and the tax is going to equal the sum this subtotal here shift F shift 8 that for the multiplication times the sales tax here so that and then I we can just press enter Because actually excel's smart enough to figure that part out and then this is the sum of these two so go ahead and equal sum and then just select those two cells close parentheses press enter and there you go and for this I can actually just do some formatting maybe I want to have that centered and uh now I can just go ahead and test my other selections here if I select P1 and since it's let's see what maybe I'll say one press enter h value didn't show up so I can see what happen so what happens is this is actually a very common error this is actually a very common error so look look at the vlookup here so the V lookup is looking at A2 B4 which is A2 to B4 but when I copied it down here it moved it up to A3 B5 so it went to A3 B5 so what happens that when you copy it down when you copy this formula down the cells shift relative to the next cell and this is very common in Excel what you want to do is you want to make them a absolute cell reference so you so when it copies down it keeps A2 to B4 constantly instead of Shifting at A2 to B5 and then maybe go A3 to B6 so what I need to do is when I click here press the F4 keys that have the dollar signs in front of the letter and and the number you can see that I want to have it for both instances do the same thing here do the same thing here press control enter to stay in the same cell and once I copy that down you'll see now when I look at the next cell that's the same that's the same and that's the same so the thing same thing is probably happening here where actually this in here I don't really need to worry about it because when I copy it down I want it to reflect B17 * 887 I want to reflect that row I want to reflect that row B18 * h18 and and the same for this row and this row but on this row since I'm doing a v lookup I want that lookup table to stay the same I want it always to look up that table right so that's what the V lookup is and I I'll have links to my other videos that show how to use vlookup in this video so you can see how the vlookup function works but this is how you can create an voice and you can see in the print preview it's we're missing our invoice up here but that's probably a formatting issue with Excel we can see it up here oh what's happening and now it's it's moved it off to the side here so we see that dot line that means it's going to print on the next page we can indicate this is the print area select that and go to file and go to print and let's see what we can do is adjust some the margins so sometimes we have to do a little bit of formatting with the margins maybe we want narrow margins if we have narrow margins then we show it up a little bit better click on home and see now the dott line it indicates where the margins are for the printing and so now we can look at it and adjust it and see okay here we are here's our invoice company name so to Etc and that gives us our invoice so this was a little bit longer than I expected but I hope this helps thanks for watching