Unlock Efficiency with Our Google Sheets Billing Template for Procurement

Google sheets billing template for Procurement

are you ready to streamline your invoice or quote generation in today's video I'm going to show you how to take a list of items and prices and turn it into a simple invoice template where you can simply select an item enter the quantity and have each line item populate as well as the total and then from there you can simply download as a PDF or you can print and then invoice creation will be done so let's go ahead and Jump Right In from scratch so we'll just do sheets. new and create a brand new spreadsheet and we'll call this invoice and then let's just jump right in so first of all let's get our list of items we'll just have two columns in here and we'll call Item and price and then I'm going to just paste my list in here get rid of these extra columns real quick and then optionally we also format these really quickly just to make them look nice and tidy and then if you like you can also freeze that top row if you want to scroll down without losing that there so that is optional as well and so let's go ahead and start on our invoice template so we're going to leave a row here just to be a little buffer you don't have to leave it if you want tighter margins and so let's go ahead ahead and make this a little narrower here this will be kind of our left margin and then I'm just going to leave this your logo here and then we can make this perhaps a little bit taller maybe something like 100 pixels just to give you room for that and then we'll merge these and Center this so it's ready to go and then you can either Center left Al line whatever you like there and then over here we'll just call this invoice merge these and then I'll right align this and then let's pick this font and maybe make about this size and then we'll go ahead and resize our right hand or right margin column there get rid of the rest of these all right and so that's our width here and then we can build the rest of our template down below and so let's go ahead and just put in some quick facts at the top of our template so we could do something like client name job name you can adjust this to whatever is needed in your case and then we'll do invoice date over here and then also optional invoice number if you use that and so on this invoice date we can make it so you can just simply double click if you go right click drop down and then here we're going to change this to is valid date and then you can also do reject input just make sure nothing else accidentally gets in there and then at this point you can double click and select a date another thing you can do if you always want this to be today's date you can use a simple formula called Equals today and then this will always show today's date and so we'll go ahead and put invoice number here and then we can just put in our sample client here and then we need to build ourselves a little table and so we'll start with item name and then we're going to give ourselves a little bit room so maybe three column widths and then let's do quantity price and total and so you can use whatever color you want on this for the background you can align it to some of your logo colors and then I'm just going to pick this font here you can also pick a different font if you like to match your logo as well and so you can go white here if you like you can keep this black or you can pick a different color whatever floats your boat itbe it'll go a little lighter just like that something like that would be great and then you can decide how many light items that you would like and so maybe we do about 20 or so and then what we'll do here is do a alternating colors and then what I'm going to do is uncheck this header cuz I'm going to going to start here and then we'll go ahead and close that out so that goes down to here and so then we can just put in our total over here for example and then we'll use this sum formula so at this point we have kind of the very Essentials in there what we need to do though is have a way to select our item so what we're going to do is go ahead and merge these across and so if we do this click on this merge one we want to click on this little drop arrow and then merge horizontally and that merges each row and not the whole thing and then what we're going to do is do right click and drop down and then instead of manually typing in those options we're going to do drop down from AR range and then click on this and then in here and now we can simply select our data range here and so you can just go down to here or if you want the whole thing in case you add more get rid of those extra numbers then end and so this would be A2 which is a start range here to a and don't put it in range on there and we'll click okay you can see all those products there and then under Advanced options you can determine if you like the chip or if you prefer the arrow now you can use a plain text but for some people it's confusing because they don't recognize that is a drop down however if you double click it will allow you so I typically I pick arrow and again this won't show up in the printable so it's not a concern in that regard so at this point now we can select a product and enter a quantity however we don't have price or total in so what we need here is a simple V lookup or X lookup formula and we want to look up this item in our list here get column two and then faults for exact match and there we go there's our price now one thing we may want to do because if we drag this down now it has an NA error so what we may want to do is before this VI look up do if B8 equals to blank and then we're going to do a double comma and so what saying is if B8 equals blank it would do this and so often what you'd see is maybe like this and so you can leave like that if you like or you can just leave nothing between the commas um like Microsoft Excel does not allow you to do that but in Google Sheets you can so that would be one way you could do that there and then if we drag this down you can see that now there's nothing in those but if we select a product the price shows up and it doesn't matter where we are we can do that and so that great now we have quantity and price but we need a total and so again let's start off with a quick check to see if this is blank and if so we'll do nothing otherwise we'll do quantity times price and then we'll drag this down and now we can see that this picked up those totals there and has our total here so so let's go ahead and wrap up the the bottom of our template here so I'm just going to select these columns here and this time I'm going to do merge all I'm not going to do merge horizontally I could do merge horizontally but in this case I can just do merge all and save myself a click and then what I'm going to do here is our little text here so and then let's go ahead and make this a little smaller and then I'll Center that and then let's go ahead and put a line above that and we could do different widths here so you can see what you like there we'll just do probably a single one there and then optionally you can also add a thank you for your business or any other message that you like and then I'll Center this make this a little bold just like that and then what we'll do is we'll just end here and so I'm going to click on here and then I could either click and drag or I'm going to do is just do this scroll down to the bottom I'm going to hold down shift and then click on this last row right click and delete rows and then that gets us back up to here so what I'm going to do now is I'm actually going to get rid of these kind of weird grid lines here so I'm going to do view show and grid lines and So currently it's checked so I click again it's going to uncheck and so grid lines are going to disappear and so immediately this looks very clean now and so we can just kind of do a couple little cleanups and then we'll be good to go so let's go ahead and start here real quick let's do bold make this a little bigger and then let's add a little bar above this and then we can also do something we could add a color here if you have a color printer or if you want to keep it just something gray like that so like that and then also if you want to give this a little more space you can just drag this down or right click and do resize row and termine if you want this to be a little taller and at the same point now it's kind of down towards the bottom so you can also deter if you want this to be towards the top in the middle or the bottom and so you can kind of play around with that along with that row height and so let scroll up to the top here and look what we can do here so we got these here um perhaps you might have some longer names here and so we may want to merge these give ourselves a little more room and then this is a little confusing cuz there's no lines and so you may like this look or if you want two we can add some different lines here so we could do something really faint like this with a single width and do something like that and so I'm just doing a horizontal between and then this bottom border so it shows up underneath so that's a very faint line if you want something a little darker we could select something a little darker and go like that so you can kind of do whatever floats your boat there we can do the same thing here and then on optionally here we can add some lines if you like so we could come down here and then we could maybe add some white and then just do it just between like this so it's a very faint like that and so this is basically done here so you can kind of play around with it some more if you like with the colors we could do things like make this taller if you like and then once you're satisfied with that we can proceed to what it looks like to download or print and so if we go to download and specifically PDF you can see at first glance it looks like it's all screwed up and so what we can do here is there's a few settings we need to set once and then it should remember it from then on out so if we click portrait and that's pretty much done so at this point you can see I have a little extra room here so one thing you could do is add some more rows in here for the line items or we could add a little buffer space wherever we want and so at this point you could just be done hit export and save as a PDF if you want to print directly from here you can do file and print and then it looks very similar to the PDF except this one will take you to the print so if you want to add more light items So currently we have 20 I believe if you want to add more what I would suggest is adding some in the middle so if we had to add let's say eight more now the one thing to keep in mind is is if we do that you can see this kind of looks goofy now so all we have to do is Select those and then come over here and do merge horizontal and those are fine the other thing you'll notice if we come in here and select a product that price isn't showing up so we click on the price or the total you can see there's nothing in there if we go up here to the top you can see oh there is the formula there so what happens we inserted new rows the formula is just missing so we can just come in here drag this down and now you can see that popping up and so if we add a quantity now it's correctly calculating so there's one more thing we may want to do here so personally for me I like to Center these quantities and so we want to do that we can select this column and then just do this horizontal alignment and Center them and then another thing as well if you have decimal points on these prices you may need to increase your decimal points so that way we can see those cents if you have that in your prices all right so that's it for today's video if this was helpful for you make sure to check out my other videos on both Google Sheets and appscript and I also have a link to this template in the description below in case you want to download it directly and start playing around with it as always thanks for watching see you run soon