Create an Efficient MS Excel Bill Format for Customer Support

Ms excel bill format for Customer Support

if you're often generating invoices in Excel what you're about to learn is going to save you hours of time we're going to build an automated invoicing system where all of the customer details get filled in using a dropdown all of the totals and taxes get calculated automatically and you can export the whole invoice as a PDF in just one click even better it automatically gets saved with the invoice number and the customer name and you can save it in the file path that you want best part is you automate this once and you can forget about how it's done entirely so let's get into it the first step for us is to create an invoice template which is what we have over here under this invoice Tab and to the side I have a second tab called customers which is where we would list out all of our customers with their respective details so in this invoice tab you can see I already have some of the formatting done so if you want to follow along you can download this exact same file the video description for free right now we've only worked on the formatting so we need to make this Dynamic for example under Bill 2 here we want to have a drop down that if we select a certain company we're going to get their details and if we change it we're going to get different details for this we'll first make the drop- down list by going to data and clicking on this button over here for data validation we're going to want a list and for the list Source it's going to be all our customers that said we wanted to continue all the way to the bottom right so what we'll do instead is not just select these three but with control shift down select down all the way to the very bottom that's fine by us this way we'll make sure we don't miss anyone and click on okay so we have this list right now we have three people and right below it we' like to see their details so their address and so forth and we'll do this using an X lookup hit the top key there what are we looking for well the company right in mic Inc in this case we'll put the F4 key to put the dollar signs so when we drag this down it's going to remain fixed comma the lookup array is basically where can we find my Inc well we can find it in column B comma and then the return array is what do we want as the answer well we want their address number one that's going to be the first thing we want we can then close that and hit enter so you can see we have their address and now we just want to drag this down so it's going to be the address number two and the email but if we on the second one right now you can see it's working well here but instead of customer C we just want to move that to a that's the address to and then same thing with the bottom one we're just going to switch that to an E which is going to be their email now if we move this around from mik to ABC company you can see we get their details same thing with some over here and if I were to add someone else let me just do this quickly you can see here I filled in some details about this test ink and we go over to the invoice we can now see test ink in here and see their details down below as well the build to area is now Dynamic so let's work on the invoicing dates so the invoice number is one we'll have to fill in manually but for the date well we're invoicing today right so we'll just use the today function close the parenthesis and hit enter this is going to change as we open up this Excel file on a different day and the due date well we just have to select it today and we can do something like plus 14 or plus 30 or whatever it is that you want to get paid by now let's work on the pricing and the taxes for it so you can see right now that I have product a as a sample have a quantity of one and the unit price the taxes let's suppose these are 20% so it's going to be the unit price multiplied by the quantity multiplied by 0.2 and hit enter there so if the quantity changes to two then the tax should move as it is the case and then for the total excluding taxes it's going to be the price multiplied by the quantity and hit enter so if this goes to one the unit price should be the same as the total there that's all looking good and that means that we can now drag this area down just by clicking over here that said we get all of these zeros which don't really look that nice so let's actually remove them by adding an if statement so we'll say something like if up here in the front the quantity is equals to zero then what do we want well we want it empty and we'll do that with two quotations there no spaces between them comma and if it's false meaning that there is a quantity we can leave this as is so we'll just go to the very end of the formula and just close that parenthesis and hit enter now I can drag this part down and you can see we no longer get those zeros but as soon as I put a quantity in there it starts to show up that's fine because if there's a quantity there needs to be a price for it as well right then we'll do the same thing with the second part so we'll put an if statement if hit the top key the quantity is equals to Z comma then we just want it empty and if it's not equals to zero we want this answer that we already have close up parenthesis hit enter and now we can just drag that down now that's looking good I can change this to product BC let's put a quantity of three at a price of $10 buck and you can see how that automatically gets filled in we want to do the same thing with the totals here so the subtotal USD is going to be the sum of all of these in here and hit enter taxes is the sum of all of the taxes so these right over here to the side and then the total is going to be equals to the subtotal plus any taxes included and you can see what that looks like there if I zoom out here to see what we have below we we have some of the payment info and a comment these are things that are usually not going to change for us at all right now we've added two products but you can obviously customize this so you can remove this area if you wanted or add more even now we just need to make this printable and for this what we'll do is select the whole area we want printed so it's from the top all the way to let's say around here let's give it a bit of margin on the bottom there now we just need to go over to page layout and click on print area set print area so this is our print area we can test that by going to contrl P that's basically the print format you can see there what that looks like in the preview so it's looking quite nice we can escape out of that and here's what it looks like on Excel next up we want to create a button over to the side that's going to say export as PDF and so when we click it it's going to export this whole file as a PDF in one click but for this we're going to need to save it as a macro enabled workbook we can do that by going to file save as and then here as the save as type we want to go for Excel macro enabled workbook and feel free to save it wherever you like once that's done we want to head over to the developer tab if you can't see it just go to any other tab right click and go to customize the ribbon once in here on the right hand side if you scroll down you'll find the developer just go ahead and tick on that if it's unticked for for you and click on okay now you should be able to see it to the side to create this export as PDF button we'll need to write some very simple code and don't worry if you've never written code before as you can just follow along go over to Visual Basic under the developer tab just click on that this is going to open up this window I'm just going to split screen it so we can see both so here we are and again you don't need to know anything about this you just need to follow along as we go over over to insert and click on module then we want to create what's known as a sub or sub procedure and let's name this PDF we open and close the parenthesis and hit enter this sub also known as a sub procedure is basically like an action or a set of steps that we want to tell Excel to do within it we need to declare our variables which is essentially telling Excel what kind of data type we want like an integer a string of text and so forth so we'll put dim first we'll have the invoice number and that's going to be as long that's going to be the data type there then we'll also do another dim for the name or the customer name so this is going to be as a string hit enter there then we'll want to specify both the file path as well as the file name so let's put both themm the file uncore path as a string as well and then then them the file name as a string you don't have to name them the exact same thing as me that's really just optional now that we've declared these variables we want to tell Excel exactly where they're located so first we have the invoice number so we'll type invoice uncore number and it's going to be equals to a range and in parenthesis and in quotations where is the invoice number well it's in G4 so we'll just put G4 in there close the quotations and close the parenthesis and hit enter same thing goes with the name so it's basically the customer name which is right in the center there so we'll put name equals to range and same thing here in quotations it's going to be 5 D so D5 there close the quotations there and close the parenthesis hit enter and next up we have the file path which is going to be equals to in quotations there wherever you want it Loc located so in my case you can see here I'm in desktop under invoices and suppose I want to save it in here in my finder I can just click on this stop part and copy that once that's copied I can just paste it inside of these quotations and make sure we put this Dash in the end or forward Dash like so finally we're just missing the file name which is going to be a combination of the invoice number and the company name so let's go ahead and put file uncore name is is going to be equals to the invoice underscore number and let's put a space erson space and in quotations we'll put an underscore this is going to separate the invoice number from the name so they're not glued together basically and we'll put the name in here just hit enter there so we've now told Excel VBA where things are located and the next step is to tell it what to actually do which is to export as a PDF so what we'll do is type active sheet do export as fixed format type is going to be in semicolon equals to XL type PDF comma and we don't want the entire sheet rather only the print area so we'll go to ignore print areas with an s in the end is going to be semicolon equals to false comma again and the file name is equals to the path so the file uncore path then we'll put an ampersand and file underscore name that should be all if we scroll over to the side it should say N Sub at the end here I'm getting an error there and that's because I forgot to put the semicolon so let me just put that in and now we're ready to go we can run it by pressing on this button over here I've just run that it's called PDF that's fine we can now run it if it's all worked correctly when I head over to my finder under desktop invoices you can see I have this PDF which has the name of the invoice and the name of the company after it it's saved in the right place and you can see what that looks like it's looking nice and clean next up so we don't have to write this code again we'll just close out of that and create a button instead inside of excel that we can just press once to export but first if you want to learn to automate more of these tedious tasks in Excel you can check out our VBA and macros for business automation course if you're looking to automate data analysis tasks financial reporting and spreadsheet formatting learning VBA is going to be a game changer for your office productivity in the course you learn the fundamentals such as object properties methods and variables once you get a feel for the basics we'll introduce more Dynamic features such as conditional statements looping function and data arrays with this knowledge you'll be able to perform a whole string of common spreadsheet tasks such as autogenerating pivot tables formatting charts building interactive input boxes and more then you'll have two extensive case studies to apply the concepts you've learned the first one will focus on automating a billing summary report for pwcs Consulting team and the second case study will focus on building an automated pnl statement for Mercedes-Benz so if you want to save hours of tedious office tasks check out the link in the description below to get started with VBA and macros so we've seen how this code is working correctly now and instead of having to go inside of VBA every time we just want to be able to access it here on Excel we can do that by going over to the insert under the developer tab and clicking on this button we'll just created over here to the side and we want to link this to our PDF macro that we just made click on okay and we want to rename this so we can right click edit text and let's say export as PDF cuz that's what we want it to do and now we're ready to give this a try let's go ahead and change the name to let's say ABC Inc and let's say we change the quantity to five and 10 just to see if all the changes happen same thing with the invoice let's say we go for 1,50 and click on export as PDF we've just done that now if we head over to finder you can see that we have the second invoice generated it has the new invoice number with the new company name we can open it up and you can see it looks all fully updated with the same exact format now whenever we receive a new invoice the steps are very clear and they're going to take us very little time to complete in just a few minutes we've managed to make this huge time saer and the same thing is going to happen if you watch this video over here on merging multiple Excel files into one or by taking our macros and VBA course over here hit the like and the Subscribe and I'll catch you in the next one