Create a Proforma Invoice in Excel for Retail Trade Effortlessly

Proforma invoice in excel for Retail Trade

hello and welcome to another mr spreadsheet video in this short tutorial i will help you to create an invoice in excel the spreadsheet uses excel features and functions that are available in 2021 the completed template is very easy to use and can be adapted and expanded to suit your business needs as usual later on in this video i will show you how to obtain a copy of the completed template so let's have a look at the various functions commands and features that i will use as we build our invoice template we will employ many excel functions we will import and resize your company logo we will show you how to name and use table ranges we examine excel's new x lookup command and compare it with the popular vlookup command employ a simple if statement and make use of the excel round command we use conditional formatting and finally save the file for pdfs and printing open up a new workbook and rename sheet1 to read invoice add a new sheet and rename this tables navigate back to the invoice worksheet and select cell d2 and enter in your business name navigate to d3 and enter in your business address and then in d4 it's your telephone and email contact details change the font colors of these three cells to green and make bold and with d2 selected change the font size to 24. select the range k2 through to l4 and click the merge and center tool enter the word invoice and then right click your mouse and select the format cell tool choose the alignment tab and set both the horizontal and vertical text alignments to center and then click ok change the font color and increase the font size let's put our company logo onto the invoice so from the insert ribbon choose the illustrations tool and from this select the pictures option and navigate to your logo and insert this onto the worksheet read size and relocate the logo to the top left of the invoice now let's enter our customer details select cell c8 and using the merge and center tool from the home ribbon extend the range through to f8 and align this to the left with this range still selected simply drag down for a further four rows now this range will contain our customer details which we will need to create in a named range on the tables worksheet let's move to the tables worksheet and populate the first line of our customers database so in cell b3 enter abc limited and in cells c3 through to f3 simply copy the details on screen add the details shown on row 4 and then highlight the range b3 through to f21 with this range highlighted select the formulas ribbon and then choose the define name command in the name field enter customers and then click ok now select the range b3 through to b21 and using the define name tool again enter a name of customer name with no spaces and click ok let's return to the invoice worksheet and navigate to cell c8 we want this cell to record the customer's name which we will retrieve by using a drop down box selector so from the data ribbon choose the data validation tool select list from the allow field and then enter equals customer name in the source field and then click ok from the drop down box in cell c8 select our customer abc limited now navigate to k8 and type in invoice number in k9 type in invoice date in k10 its terms and in k11 enter our contact with k8 2 k11 highlighted change the font color to green and make bold k8 to k11 still active right click your mouse and select the format cells tool on the borders tab select the heavy style and change the color to green and then change the border pattern such that only the right hand border is active and now click ok let's quickly adjust column k's width by double clicking the intersection of column k and column l and from the page layout ribbon deselect the view gridlines box so that we have a clear neutral background give this invoice a number by entering a thousand and one in cell l8 and in cell l9 we're going to use excel's today command to return today's date the syntax is equals today open brackets close brackets and return select the range b14 through to i14 and from the home ribbon select the merging center tool enter the word description increase the font size change the alignment to left indent this by one and change the font color to green and the fill color to light green now select cell j14 and enter the word price in k14 its quantity and in l14 its value with j142l14 selected change the alignment to right and then use the same formatting colors and font sizes as in b14 okay so that's the basic design of the invoice header and now we have an area to populate with the products or services that we are selling together with the price the quantity and the value in the next section we will create two further tables so that we can automatically retrieve product details and prices customer terms and contact references as well as populating the customer's address details go to the tables worksheet and copy the details for the three tables as shown please do pause the video whilst you complete this the customers database has five fields and we need to give each one of these field ranges a name so select the range c5 to c8 and from the formulas ribbon choose the define name command and in the new name dialog box that opens enter add underscore 1 in the name field and then click ok the range d5 to d8 becomes add underscore 2 e5 to e8 becomes add underscore 3 and f5 through to f8 becomes add underscore 4. similarly in the products database give the range h5 through to h8 the name product and the range i5 through to i8 give it the name price and finally in the terms and discounts database k5 through to k8 becomes contact l5 through to l8 becomes terms skip over the customer range and then n5 through to n8 name this as discount for demonstration purposes my database has been restricted to four lines of data your databases will doubtless require many more lines you can easily extend the name ranges by adjusting the coordinates using the name manager tool in fact we need to use this tool now remember when we created the customer name field it only contained two lines the range b5 through to b6 so select the name manager tool and from the name manager dialog box highlight customer name and in the refers to field at the foot of the dialog box change the coordinates to extend the range to dollar b dollar eight click close and then yes to save the changes finally in this section we want to give the entire data area of the products database a name so highlight the range h5 through to i8 and from the define name tool give this range the name products table with no spaces okay that's a lot of naming of ranges done but in the following section we will see that this effort is very useful and saves a lot of time we can now return to the invoice worksheet and use a combination of excel's new x lookup function and also combine this with the vlookup command so select cell c9 and enter in the command equals x lookup open brackets point to c8 then insert a comma then customer name comma and then add underscore 1 and close the brackets this formula says examine the contents of cell c8 this equals abc limited then find abc limited in the customer name range and from this table choose the corresponding value in the add 1 range as you can see this correctly populates cell c9 let's enter the x lookup command for c10 c11 and c12 are remaining address fields the formulas are exactly the same with the exception that the address number is different we can use the x lookup command to populate the terms and the r contact fields we will still use c8 as our anchor reference but now we will link to the terms and discount table so select cell l10 and enter in the formula equal x lookup open brackets point to c8 then a comma and then highlight the range m5 through to m8 in the terms and discounts database on the tables worksheet enter a comma and then type in the word contact and close the brackets press enter and you return to the invoice worksheet excel correctly retrieves the name harry booth who is the elected contact point for our customer abc limited use the same x lookup command to retrieve the terms for abc limited the formula in cell l11 on the invoice worksheet reads equals x lookup open brackets c8 comma then the range m5 to m8 a comma and then the word terms close the brackets and press return abc limited's terms the 45 days is correctly populated now select the range l10 to l11 and align these to the right select cell c8 and increase the font size and make bold we are now ready to automate data entries for our invoice products we will create our first invoice line by selecting the range b16 to i16 use the merge and center tool to combine these cells and then align the contents to the left and indent by one we will now create a drop down box which allows us to select any of the products listed in the products database so with b16 active click the data ribbon and select the data validation tool choose list from the allow field and enter the range name product preceded by an equal sign in the source field from the drop down box that now appears to the right of cell b16 select the second product widgets gold 17 millimeters we can retrieve the price for this product by using the vlookup command to select the value in the second column of the products database select cell j16 on the invoice worksheet and type in the formula equals vlookup open brackets point to cell b16 enter a comma then select products table from the list another comma then two then a comma and then a zero and finally close the brackets the price of 17 correctly populates in cell j16 now format j16 to two decimal places and enter a quantity of 10 in the cell k16 and format this to zero decimals finally cursor to l16 and enter in the formula plus j16 multiplied by k16 and then format this to two decimal places okay this product line works just fine and for most businesses this is all you need however i'm going to make some adjustments to the price in cell j16 firstly let's take a look at the terms and discounts database in the tables worksheet locate the customer abc limited and we see that abc limited are entitled to a seven and a half percent discount we want to incorporate this seven and a half percent discount into our price in j16 on the invoice worksheet we want the price less the discount so our formula has to work with the correct discount price for each individual customer we can once again use the x lookup command to isolate the discount value for our customer in cell c8 move your cursor to cell n16 and enter the formula equals x lookup open brackets point to our anchor cell of c8 enter a comma point to the range m5 through to m8 on the tables worksheet enter a comma and then enter the name discount and now close the brackets the value of 0.075 is correctly retrieved now move to cell o16 and enter in the formula plus one minus n16 to give us the net percentage value which we can then apply to the price held in j16 so select cell j16 and in the formulas bar add multiplied by o16 and press return the price changes to reflect the discounted value okay so that's the first product line nearly done before we can copy our formulas in b16 through to o16 we need to make a couple of minor changes delete the contents of cell b16 and now we get error messages in i16 and l16 this is because these formulas are dependent on there being a correct value in the description field b16 we want a simple if statement such that if b16 had no contents then j16 would return a zero select cell j16 and in the command bar enter after the equal sign if open brackets b16 equals inverted comma inverted comma followed by a comma zero followed by a comma and then navigate to the end of the formula and enter in the final closing bracket the price field in j16 now returns a zero and as the formula in l16 depends on the contents of j16 this cell also returns a zero with b16 active reselect the second product from the drop down box now if we multiply the price in j16 by the quantity in k16 we should get 157.30 you can see that we have a rounding error to correct highlight j16 and once again we will adjust this formula to include the round command to eliminate this problem return to the formulas bar and after the equal sign enter round followed by an opening bracket now navigate to the end of the formula and enter in a comma and then a 2 and then a final closing brackets press return and the value in cell l16 now shows correctly as 157.30 finally navigate to cell n16 we want to lock the coordinates c8 and m5 through to m18 so go once again to the formulas bar and highlight cell c8 and now hit the f4 function key on your keyboard c8 now becomes dollar c dollar eight highlight m5 through to m18 and hit f4 once again to replace the dollar sign before each of the coordinates that's great we can now move to the next section where we'll copy this invoice line such that we have a total of 20 product lines delete the contents in cell b16 and also delete the quantity in cell k16 with the blank cell b16 highlighted drag and copy down this formula to the spreadsheet row 36 now select the range j16 through 2016 and drag and copy down again to row 36 we now have 20 invoice lines let's quickly test out the first few lines to ensure that our formulas are all working correctly enter the values shown on screen and hopefully you will not return any errors or discrepancies that's fine it all seems to be working correctly now let's tidy up this section highlight the price range j16 through to j36 and from the home ribbon choose the conditional formatting tool select the highlight cells rule option and then the equal to option enter a zero in the value field and from the drop-down box select custom format from the format cells dialog box that opens select white from the color palette click ok and then ok again now with j16 to j36 still highlighted click on the format painter tool on the home ribbon and then highlight the value range l16 through to l36 to apply the same formatting to this range finally highlight columns n and o and choose white from the font color palette such that all the values in these columns are not visible and that completes our work on the invoice lines section we can now create our invoice totals and add a tax calculation and some essential company information select cell k38 and enter in subtotal in k40 enter tax and in k42 enter total highlight this section k38 to k42 and apply a font color of green make bold increase the font size and align to the left in cell b38 enter bank and credit payments 2. b39 becomes bank b40 is payee b41 is bank code and b42 is bank account highlight the range b39 to b42 change the font color make bold and increase the font size now enter your bank details in the cells d39 through to d42 and make bold highlight the range b44 through to l44 and merge and center this range and type in your company's strap line or other preferred information change the font color to green make bold and increase the font size select cell l38 and enter in the sum command to add up the values in the range l16 through to l37 now highlight the range n40 through to o40 and change the font color back to black in cell n40 enter in 15 this is our tax rate go to cell l40 and enter the formula plus l38 times n40 press enter and then change the decimals to two decimal places by clicking the decrease decimal tool on the home ribbon okay now that's fine if your tax calculation is as straightforward as that however maybe you will need a different calculation or maybe even a fixed value we need to allow for this eventuality so in cell 0 40 enter in the value 85.62 or indeed a formula to match your own specific requirements such that if there is a value in cell 040 then our value in the tax total in cell l40 will choose this total rather than applying the percentage tax of 15 in n40 we now need another if statement to select which of the tax values we are to use so if there is a value in cell oh 40 we need to choose that and if there is no value then we need to apply the percentage as shown in n40 so select l40 and in the formula bar change the formula to read equals if open brackets o 40 equals inverted comma inverted comma followed by a comma and then l 38 times l 40 a comma then o 40 and close the brackets once you press enter the tax value changes to 85.62 delete the 8562 from o2 and the value reverts to 15 finally we need our invoice total so in cell l42 enter the formula plus l38 plus l40 and that's our basic invoice done in the final section we will make a few cosmetic changes and then format the entire invoice for printing select the range b14 through to l14 and from the borders tool on the home ribbon choose outside borders select the range b15 through to l36 and click once again on the outside borders tool place outside borders on cells l38 l40 l42 n40 and o40 and then increase the font size of l42 and make bold adjust row 43's height so that the strap line moves further down the invoice now return to the top of the invoice and in cell b7 type in invoice2 colon reduce the font size change the text color to green and make bold and then finally reduce the size of the logo graphic to better fit the invoices dimensions finally we need to adjust the preset printing coordinates such that the entire invoice is formatted correctly for printing so highlight the range b1 right down to l45 at the foot of the invoice and from the view ribbon choose the page break preview tool drag the vertical blue line to the far right of column l navigate downwards to ensure that the entire invoice is selected and then click the normal view tool your invoice is now ready for printing and for saving as a pdf file select the file option at the top left of excel and then from the left hand menu select print the print options and the print view page are shown hopefully all is okay and you can now click the print button to complete this process you may also want to take a pdf copy of the invoice to either send to your customer via email or to retain a copy for your records simply select the save as option on the left hand menu enter invoice invoice1001 in the name field of your preferred folder and from the file type drop-down box select pdf and that's it you now have a fully functional dynamic invoicing system we do hope that you enjoyed our spreadsheet video and that there was lots of content that you found both useful and informative now if you would like us to send you a copy of the completed spreadsheet then all we ask is that you subscribe to one of our channels and leave your contact details you can like on facebook or follow on either instagram or twitter and please click the subscribe button below if you enjoyed watching this video then why not view our accounting spreadsheet video where we will show you how to create a bookkeeping system for your business alternatively take a look at our popular how to keep inventory in excel video many thanks once again for your attention and oh yes please do subscribe to our channel