Create Invoice in Excel for Public Relations with Ease

Create invoice in excel for Public Relations

welcome to Excel 2010 business math video number 61 if you want to download this workbook Excel 2010 business math chapter 7 click on the link below the video in this video we want to see how to create a finished invoice like this so we can just select an item here whatever it is and it calculates the total subtotal etc so everyone has to see how to create that invoice now I've already done a little typing here put the word invoice at the top here's the company name here's the ship to invoice number and date now here's a list of all the things we'll just do them one by one first I'm going to highlight and center this cross selection it and increase the font so I'm going to use the keyboard shortcut control 1 I'm going to go to alignment and instead of merge and center I'm going to use horizontal center cross selection go over to border and say outline fill I'll do a dark color font something like 16 and maybe some nice font I'm not so good with font but something that's kind of fancy I don't know it that's not very fancy at all gives you a preview here and I'm going to select that one oh I got the wrong font color so I'm going to go to white alright now all of the headers in essence or the labels I'm going to color dark blue and white so I'm going to hold my control key and select no cells that are not next to each other called non contiguous cells I'm going to click that drop down font color white and maybe a border now for an invoice we're going to need a few different things we need item purchased and then we're going to need quantity and then we're going to need price each remember are the items in these columns are going to be somewhat automatic and then line-item total I'm going to highlight all of those hit the f7 key since I'm such a bad speller and then I'm going to add some formatting some fill like that all right now items purchased I'm going to highlight maybe this mini you can highlight as few or as many as you want and add a border and now down below here I have and in the last couple videos we saw how to calculate wholesale cost and I went ahead and had my list my trade discount and calculated wholesale cost but we would like all of these names we don't want to have to type them in or even worry how to which ones we have we want to drop down in this cell so we can click the drop down and it knows to select from this list here then over/under price each we want to use and we've seen this one other time in this class the vlookup function to look up the product and tell us what the price is all right so the first thing is we want to create a drop-down list in each cell that gives us a list of the product names it's really quite easy in Excel you go up to data data validation now validation means it has to be true right I'm going to click data validation data validation and what has to be true is whatever I go in the cell has to be from down in this list so instead of allowing any value I'm going to allow a list and then the source totally awesome I can come down here and highlight the cells is that totally cool so that source now look at this it says in-cell drop-down I'm going to click OK and now lo and behold not only that but it's dynamic if I come down here go right and then I come up here it now has changed now I'm going to undo that so now it's this quad there alright so we'll select those two now quantity there's nothing special that we'll have to type that in but price and now I'm going to add some green here on the invoice you wouldn't do that but that's just for this class to indicate that it is a formula here all right so let's put quantity five and then about a balance we've bought ten so price each well let's think about this and we saw this earlier in the class maybe I'll zoom in a little bit here and let's just think about how we do this by hand I'm going to have I want to ultimately have a formula here I'm going to look here I'm going to put the quad in my brain now I'm scrolling down I know where the table is right so then I look the table oh I find the quad I jump over and let me zoom in a little bit I find the quad I jump over to the second column I remember that it's 1724 I come back over here and I type 17 point 24 now if I had a formula I would have to say hey formula please remember that it's belen we're looking up so I'm putting that in my brain I'm coming over here I find a match for belen I jump over to the second column I take 1389 and I bring it back and I put it in the cell so I don't want to have to do that every time especially since I don't want to be able to just change this to whatever and have it automatically update and ultimately actually in the working world you know there's lots of programs QuickBooks Oracle that do invoicing automatically right but a lot of people do their invoicing in Excel and this is how they do it they love this data validation drop-down I'm going to go back to this COIs and they love the vlookup function so here we go let me see if I can zoom in again that may not work here let me just do it this way so the function we use is vlookup and it's called V because the table that we're looking up things up in is orientated vertically and look up because we're going to look something up lookup value I need to tell the vlookup what I want it to go look up it needs to go and find the quad down there right comma just as we did it by hand we need to tell the vlookup where the table is first has the thing that it will try and match right second is the number that we need to throw back in the cell remember what the vlookups job is to throw something into the cell right what the price now I need to lock this so I'm going to hit my f4 key because this is a formula and as I copy down the table needs to be locked but the blue one needs to always move okay so table comma two you got to tell if you look at which of the columns has the thing you want to throw back in the cell - that means vlookup knows to get the number from the second column and throw it back into the cell now comma and early on the class we did vlookup but we didn't do it exhibits called an exact match that means we're looking up a word we want to find exactly quad now you can either put false just double click it and that will work fine or you can put a zero for those of you that are learning to pick just double click this list and then when you want an exact match you put false all right now let's see if that works ctrl enter I'm going to copy it just down to here now I'm going to change this Carlo to doublers so I go down here no way Clos - doubles that is just amazing right so now we've totally automated our invoice choose anything from this drop-down which is looking down at the table and it pulls the price which is looking down at the table maybe I'll add some currency control shift for now before we worry about what to do down here let's see if we can finish this all right Oh once we know the quantity and the price it's easy we do round this times this come on - now that is just totally awesome not only will our drop-down list I'll select them sunshine 10 ply 5 10 25 each and there's the line total all right now let's go ahead and copy this down and see what happens not available well let's look at this formula not available is handy it tells us tells us what vlookup is trying to look up nothing there's an empty cell it can't find that down in the table so we're going to do just like we did back with our checkbook if this cell is empty I would like to throw a blank in the cell otherwise if it's not empty please show the formula vlookup right I'm going to come up here and I'm going to edit this I'm going to click right before the equal sign and remember this whole column gets one of two things it either gets the vlookup or if there's nothing over here it needs a blank so every cell is getting one of two things vlookup or nothing which is a blank right so you're right let's do this anytime there's one of two things that go into the cell the if function is perfect and our language kind of helps out right if this is empty then something otherwise something so that means we're going to use the if function logical test that's something that comes out true or false so we asked the question is that cell relative cell reference is it equal to and blank we saw this with our checkbook register early in the class blank is double quote double quote this is a logical test on to actually let me type a comma logical test right here either comes out true or false is it equal to blank or is it not right right now it of course will say false but when we copy the formula down this will be blank what do we want there blank so the value if true meaning the thing is blank double-quote double-quote comma and then the value of false that means there's something there the vlookup come to the end closed parenthesis now that is an awesome formula for a name voice control enter and double double click and send it down now let's just try this okay so I'm going to go all the way down and select a icarus MTA oh that is amazing now instead of a blank there there's a vlookup looking up the price for Icarus and I type like to here right now what about this let's copy this one this formula here let's copy it all the way down value error means it's taking blank and multiplying it by zero in essence that just means there's nothing there right because we put that double quote and that this this formula doesn't like that double quote no problem we'll do the same thing remember if that cell is equal to blank then blank otherwise that so let's try edit mode in and what's our logical test if no one has entered an item which means that cell relative cell reference equal to double quote if there's nothing there that's the logical test if it's true what do we want nothing or a blank double quote double quote comma otherwise what do we want the value false oh just do the round calculation the multiplying now we come to the end of closed parentheses control enter and double click and send it down now let's try it this is the magic of Excel and doing invoicing right I mean we're going to buy some majestic boots people like the long-distance ones and we're buying fifteen of them just like that so I select I don't even have to type the product I just select type in the quantity and everything is calculated all right now we have a few other things that we need shipping terms terms now I'm going to and then over here we'll come back to that in just a moment then over here we're going to have sub total shipping and you could have some other items here you can have a this is going to be a wholesale invoice only but absolutely we saw it in the class I you could have a tax calculation right for invoicing but so you can have other lines here but we're just going to have subtotal shipping and then total and I'm going to put invoice total I'm going to highlight those hold control and highlight these and then add some similar formatting by the way I have no line so in here the way I did that is I went up to view and uncheck that all right so subtotal alt equals shipping that's just going to be a cell I'm going to ctrl shift for there I'm going to add some lines there I'm going to add some borders there shipping could be something like 15 bucks and then invoice total will be alt equals which doesn't work and we go there later in this chat out here so there you go we got that one maybe one last little thing we could put something like or you could write speak clever and say many happy returns or something like that because this is a boomerang company right I'm going to highlight that in control one or come up to alignment and say center across selection bold control B and then I'm going to highlight below it and all the way up to the top and I'm going to add outside border like thick now if I control P it's got all sorts of stuff on it and I only want when I control P to highlight to have the invoice print out so I'm going to go to page layout print area and set print area now when I control P it's just that just one page of one now I think I probably accidentally already did some page setup here page setup here here's the dialog box or you can use the key bar let's do that yeah I already increased it a little bit to 135 - so it would fit many other things I think I want but so that's the one thing I would change here so it's fits on the page ctrl P and then there you have your invoice now if you actually were using this in the business you'd actually want to let's see let me do this and this one maybe I'll do an outline outside borders right like that you want to be careful of this and to increment that properly but you could copy the sheet over so when you're finished with here you want to keep it as a record and you could copy this now you could right-click move or copy create a copy and then you have to save after which sheet and I'm going to say complete invoice here let me show you something else here before I do that I clicked escape I'm going to click invoice and then in parenthesis I'm going to type 12:55 and as now I'm going to copy it right-click copy move our copy create a copy I'm going to say after I'm sitting sorry this is before which sheet I'm going to click on the one after the one I want it before so it's going to be before this one and I'm going to click OK oh notice that it increments the number that doesn't change here so you have to be sure and change it there alright so that's a little bit about creating an invoice using data validation list and vlookup and if even a little bit of if and round alright we'll see you next video that is the end of section seven point once and I can do the homework and next stuff video we'll start section seven point two