Effortless Bill Making in Excel for Operations that Streamlines Your Workflow

Bill making in excel for Operations

welcome to excel in business math video number 39 and in this video we got to see how to create an invoice in Excel using the amazing data validation drop-down list feature the vlookup function and the if function now we'll create this entire invoice from scratch learning many cool tricks including this feature where we're allowed to validate the product name so only the correct name goes into our invoice will see these cool formulas inside our invoice and at the end of the video there'll be an extra bonus we'll see these functions that will allow us to automatically increment the invoice number when we copy this sheet over now we want to start off by actually going and looking at the completed invoice so I'm gonna go look at one two five five answer sheet here's the completed invoice and this is what we want to be able to do we want to be able to select from a drop-down a particular product we're going to enter the quantity 10 and when we hit tab we want the price to automatically appear from our price list below we want the total to automatically calculate and then of course the invoice totals at the bottom now let's go over to the sheet complete invoice 1 2 5 5 now I've already entered the text in and the numbers for quantity we're gonna format this but we're also gonna see all of these amazing steps for creating an invoice number 1 let's Center the word invoice at the top so I highlight the range a 1 to d1 control 1 to open up format cells and on the alignment tab we're going to use the alternative to merge and center horizontal center across selection click OK now I'm going to right click and use the mini toolbar to add dark-blue fill and white font I could also use the drop-down I'm going to slide sixteen font size and bold now let's select company name address 800 and website and the b c and d column control one and all we want to do is use alignment horizontal center across selection click OK now we want to insert the company logo and a boomerang picture insert ribbon tab illustrations pictures because these pictures are on our computer i have them on my desktop i'm going to select Belem boomerang i'm gonna hold a ctrl key and select JAL boomerang logo then I'm gonna click insert or simply enter and both pictures are inserted click off to the side click back on just the bell and boomerang drag it off to the side will point to the bottom corner and click and drag to resize it still too big resize something like that will do click on the JAL boomerangs logo click in the lower corner click and drag to resize and we can click and drag our next step is actually to add the same color fill in font from up here to a number of different areas now you can do this one at a time select the cell right click mini toolbar fill color font color and then all borders or let me show you a trick I'm going to select the first two cells those are gonna have the same fill font and border then I'm going to use the ctrl key to select a range not next to this range I just selected also called non-contiguous so I'm holding ctrl and I'm gonna click and drag now I'm letting go of control move my mouse down to shipping terms now before I select I'm gonna hold ctrl click and drag letting go of control come over before I select subtotal hold ctrl click and drag now let go now I can simply with all of the different ranges selected right like mini toolbar fill font color and drop-down for all borders now I want to come up to selling to that's the customer highlight that range right-clicking on the mini toolbar I can go to borders and let's try outside border now I'm going to highlight invoice in date range and my goal is to add all borders but I want to add all borders to other sections of the invoice also so I'm going to use my control trick but this time I'm just going to hold control the whole time as long as you don't roll the wheel on your mouse you can simply select the first range hold control now watch it's clicked in drag I'm still holding control click and drag click and drag holding control the whole time now I let go of control since I want all borders right click mini toolbar all borders now let's add a border around the entire invoice select the top four cells all the way down to thank you for your business and one row below right-click and on the mini toolbar I'm gonna go down to thick outside borders now one last task select thank you for your business and the four cells to the right control one we want to Center a cross selection click OK control B to add some bold now if I click off to the side that is looking good now our next task is to calculate the price for each item so when I enter quad here I want the price to automatically show up well that sounds like the perfect job for the vlookup function now before we can do vlookup here and our data validation drop-down list we have to scroll down and word the company JAL boomerang so we issue this list price in the trade discount we learned about this two videos ago but when we're filling out the invoice to sin to kite flight we have to ourselves use wholesale cost now here's the name the boomerangs we're gonna calculate wholesale cost using the formula we learned two videos ago list price times not the 43 pennies of discount but one minus the 43 pennies which will give us 57 pennies for every one dollar of list that's how we get our wholesale cost now if I were to simply say equals list price times in parentheses one minus our trade discount close parentheses control enter I might be tricked for a second because there's currency number formatting but all of these wholesale costs are going to be used in lots of calculation so of course you immediately have to think about whether you need to round yes we do need to round but don't be tricked let's just apply general to see that yes in fact we have lots of extraneous decimals so I would like to round the wholesale cost properly down here in the vlookup table then when we get up to our invoice we're gonna have integer times a properly rounded number so we won't have to use round alright so you're ready we're just gonna use our round like we've done what 50 a hundred times so far in this class there's the calculation in the number argument comma two because we're rounding to the penny closed parenthesis and copy it down go to the last cell and hit f2 alright so we have the first column of our lookup table that's the product name the second column has our wholesale cost that we need to go and look up now we're gonna do two things here we're gonna create our vlookup but before we do that I don't want the person using this invoice to have to type sunshine five ply I want there to be a drop down so they can simply select the correct product not only that but anytime you're doing vlookup we're actually going to use vlookup to look up the quad it will go down to the table get the correct wholesale cost and bring it back but anytime you're doing vlookup and you have the V lookup table actually on your seat you might as well validate these cells that will just enable us to tell each cell to only accept items from the first column of the lookup table so you're ready this is the first time we've seen this select the cells go up to the data ribbon tab over in data tools we're going to use something called data validation so I'm gonna click and it opens up the data validation dialog box now what does validation means it means we're gonna validate the data that goes into the cell I do not want someone to accidentally misspelled quat or even worse type qu ad with a space so by using data validation will validate the data that the person puts into the cell now for doubt a validation we can allow by default any value but there's a drop-down here and you can do all sorts of different rules for validating what kind of data goes into the cell but for us with vlookup we select list what that means is we click in source and scroll down and when I highlight the product names remember that's the first column of the lookup table from now on we will not be able to enter anything into that cell up there unless it's in this list and look at that it'll provide automatically an in cell drop-down now when you do down a validation do not highlight the whole lookup table because we're not doing lookup here all we're doing is saying those cells up there can only have one of these items now when I click OK and come up here I come down here that is absolutely amazing because as soon as I select an item like sunset that is guaranteed to be valid data that the V function will understand now we're gonna create formulas and these columns over here and so for the time being I'm gonna highlight using my control key and add our green that means we're putting a formula here for the final version you probably would not want that because you're printing this out and giving it to the customer all right so let's try this I'm gonna click in the top cell equals VL tab the lookup value that's going to be whatever they select from that drop-down for this first row it's quod comma now I need to tell vehicle where the lookup table is so I scroll down first column has the names where vlookup will try and find a match to determine which row to get the price from the second column has the item we're gonna look up now I definitely need to hit the f4 key to lock that because as I copy it down the table needs to be locked comma column index number that means which column in the lookup table has the item we want to go and get and bring back to the cell 1 2 so I put a 2 now comma vlookup can do one of two different types of lookup now most of the lookups we've done are approximate match in this class but we definitely have an exact match lookup we're not looking up some number or Commission rate or income we're looking up the characters qu ad so we need to select the second option please do exact match lookup I'm gonna double click to put that false in now that false is just telling vlookup please from the two types of vlookups I need you to do exact match look up close parentheses control enter and let's copy it down oh no we have na errors we'll fix that in just a moment because if someone hasn't entered a product here from the drop-down I do not want that na but first let's go to the last and hit f2 were verified that that lookup table is locked and the blue lookup value is a relative cell reference now there's a couple ways we can deal with this n/a but let's come over here and I'm going to select a another product I'm gonna select Carlota now notice instantly when I selected the product the price came up and why is that well of course because vlookup is looking there and as soon as we put a product there vlookup can do its thing now for this entire column I want one of two things in the cell I either want the vlookup function to run or down here I want to show nothing now anytime we have a cell that gets one of two things we know that's the perfect job for the if function now back in the video about checkbook registers we saw a very similar formula we either need to run the formula or show nothing so in the top cell I'm going to hit f2 now we know that we can use the if function but I also want to notice something here or take note if error and if n/a functions can solve this problem also and although they're easier to create when you're creating the formula they're not as efficient as using the if function and a logical test now I'm going to hit tab now our logical test well we already saw exactly what our logical test can be we entered the Carlota the formula work the cell is empty the formula doesn't work so for the logical test as we saw back in our checkbook register video we actually have a number of different logical tests that will work I'm going to choose since these are text items and they'll always be text items I'm going to use the function is text this is the first time we've seen is text earlier in the class we saw is number and is blank but no problem when I select that relative cell reference close parenthesis and comma that logical when it sees the text is enter it will give a true down here when the cell does not contain text it will give a false so that means value if true that means it found text that's or view lookup otherwise comma the value of false how do we show nothing double quote double quote that is a zero length text string that we saw for the first time in our checkbook register video all right I'm gonna close parentheses control enter and copy it down now in this class if I hit f2 right here I've been teaching you how to use the if function figure out a logical test and then put one of two things just a moment ago I talked about if there and if na now those functions will work here but the problem with those functions is that you actually have to run the formula every time and in larger workbooks like our workbook we're gonna have many invoices and many vlookups running it's not efficient to use if error or if n/a because with if an illogical test as soon as is text determines true or false that's it it just dumps this zero link text string in the cell it never has to run the vlookup function alright so we have our formula now let's come and calculate line item total equals I'm going to take quantity which is an integer times properly rounded price each or our wholesale cost control enter and copy it down uh-oh we have another error value error here comes from the fact that we're multiplying an empty cell which is really zero and that double quote double quote right here which is text and you're not allowed to multiply numbers and text now even though they both look empty to us they're not and you don't really need to know that as much as how do we fix the error well guess what - we can use the if function again I Aftab again there are many different possible logical tests that will work since I always have a number here or double-quote double-quote I'm going to check to see if there is is a number so for price I'm going to say is there a number close parentheses comma now logical test number that means value of true is the formula to calculate come to the end comma what do we want if it's false double quote double quote that will show nothing close parentheses control enter and copy it down now let's test our invoice come over to the drop-down I'm going to select a five-ply sunshine look at that that is amazing now I'm going to type we want 15 and enter and there we go now let's come down to the subtotal alt equals is the keyboard for the sum function it gets right and enter Oh Oh shipping is a number that we type in I don't want green there kind of show you a great trick right click mini toolbar there is the option for all these colors but don't forget about no fill that just removes all of the color you might have applied now for that cell I'm going to type 25 and enter invoice total alt equals as the keyboard for some it gets wrong highlight redirect it and enter now let's add currency I'm gonna select the first range hold ctrl select this range here control 1 and under number I'm going to select currency with a symbol click OK now I'm going to select this range here control 1 and let's help the invoice be less cluttered I'm gonna select symbol none click OK and now our invoice is looking good if I delete everything like we're going to start over that is amazing we can select quad kite flight bot 25 from the drop-down I'm going to select Balan 25 majestic Butte they bought 12 and there you go our invoice is totally working now we want to print this out if I use control P notice that is not good I want to isolate and just print out the invoice each time so I'm gonna click escape we need to go up to page layout page setup I'm gonna click the dialog launcher to get to page setup dialog box now I'm not sure how big I want it but I'm gonna start off let's say 125 we can come back and change that later portrait we definitely want portrait because it's taller than it is wide margins I'm gonna select horizontal header and footer I definitely want a footer cuz guess what I want the sheet tab name with completed invoice and the invoice number to show up so I'm gonna come up to custom footer click in the middle and there's a bunch of amazing icons that put code in for you and there it is insert sheet name that's the code that will always take whatever I type into the sheet tab and put it in the footer click OK now I come over to sheet and print area I want to only print out whatever's in a 1 to D 28 click OK now when I control P that is beautiful escape now we have two more cool tricks for invoicing the first one is I would like to copy this sheet over and change the number to one two five six now before we do that I want to show you a mouse trick now I've selected that cell that cursor right there is the move cursor if I click on the edge with my move cursor and drag and then let go it moved it control-z now watch what happens when I hover my move cursor right on the edge now I'm gonna hold the ctrl key and click and drag when I drag I'm not moving it I'm copying it now the trick is when I do this mouse trick is you let go of the mouse but not the control key and just like that I've copied ctrl Z I showed you in the cells here because we're gonna use the same mouse trick for our sheets now let's just try to move this if I click on the sheep click and drag up notice two things one there's a piece of paper that means I'm moving the sheet but there's also a black downward pointing arrow that means I'm gonna drop it there so now when I let go of my click it moves the sheet let's try it again I'm moving it notice the piece of paper and the black arrow click and drop now let's try this again click and drag up it's still got the piece of paper but now hold the control key there's the plus when you see the plus it means I am copying and I see my black downward pointing arrow remember the trick is I'm holding both control and the mouse I let go of the mouse not control and instantly the sheet is copied and there's another added bonus anytime you have a number in parenthesis it automatically increments and that's exactly what we want here there's invoice number one two five six now I would clear this to use it again and the 25 change the invoice number to 56 and then clear whoever the customer was now we can fill out our invoice whatever the new customer is into the wind is going to buy some boomerangs and they are gonna buy majestic view ten of them I love that instantly it fills out and they wanted five ply Sun shines and they needed 50 of those now we have our invoice we have our number control P and look at that down in the footer it's got the new invoice number now I'm going to click escape now when we copy the sheet over the one two five six automatically incremented but the number up here did not increment now that's fine we can just come up like we have to enter the data here and enter the correct number but wouldn't it be nice if that also incremented now I'm going to show you a super cool trick to get a formula to read the number down in the sheet tab this formula is not required for this class it's not going to be on a test or anything like that but this is a cool little trick now the first thing we need to look at is this strange function called cell now cell can do things like get the address where the formula is get the column number the color the contents and a bunch of other things but what we want to look at is file name so I'm simply gonna double-click file name now you have to anchor the cell formula with some reference in the spreadsheet so I'm going to put a 1 if you don't anchor it with the cell reference on this sheet when you delete things it gives you an error but when you hit enter and I'm going to change the column width here it gives you the entire file path these are all of the drives and folders in square brackets is the workbook name there's the sheet name and there's the number if we could only get at that number then we could automatically increment our number in the invoice well imagine if we had a function called replace that could go from character number one all the way to character 129 that means I counted on my fingers every single character including the spaces but imagine if a place could go from one to 129 and replace all of that with nothing well replace we'll do that but the first thing is we need to always be able to find where the open parentheses is counting on our fingers 1 2 3 4 5 6 all the way over to whatever position it is that's where the search function comes in search function you give it some text and I'm gonna give it text as always in double quotes open parentheses comma and I say hey search go look for that open parentheses within that and tell me how many characters from the left it is when I enter sure enough 129 now I can use the replace function and the replace function takes some old text there it is comma you give it a starting number we're gonna start at character 1 so I simply type 1 in comma you tell replace how many characters from the left it should go in I'm gonna click on the cell with 129 comma then you say what is the new text and as we've seen many times in this class double quote double quote is the formulas syntax for lots of things including nothing we want to tell replace to go for one to a hundred and twenty-nine and please replace it with nothing when I hit enter oh that's almost what we want now we need to use the left function and count 1 2 3 4 from the left so equals left there's the text comma number of characters I'm going to type before cuz for our example we're never gonna have an invoice with more than four numbers and that will work when I hit enter there's our 1256 now I actually want it to show up as a number we know by now that the default alignment to the left means Excel thinks that's text like we saw last video where we took trues and falses and converted them to ones and zeros with +0 I'm going to use the same trick here when you have a text number and you need to convert it to a number you simply zero and because we're doing a math operation Excel will know to convert it back to a number now I can come over here and watch this I'm gonna click in that cell equals there's the sheet name as a number and when I hit enter 1256 now I come down hold ctrl click drag up I see my plus I see the black downward pointing arrow I let go of my mouse it says one two five seven there and it says one two five seven up there wow that was a lot of cool tips about invoicing including some ridiculous formula that's kind of cool at the end to get the automatic number we saw how to copy sheets over and have the number increment we saw how to do some basic subtotals we saw how to use if is number and multiplication to be sure and calculate the quantity times price for our invoice we saw how to use if is text and vlookup to look up the right price but show nothing down below and we saw how to use in the data ribbon tab this awesome data validation list feature alright if you liked that video be sure to click that thumbs up leave a comment and some because there's always lots more videos to come from excel is fun including next video video number forty we're gonna see the calculation necessary when someone doesn't pay the whole invoice but just part of the invoice and expects to get a cash discount all right we'll see you next video