Sign Invoice for Goods (Standard Format) Online

How to eSign a document: Time Sheet Time Sheet - Edway Labour Hire Form

hello and welcome back to the exciting videos today in this session we will show you how to create an invoice template using Microsoft Excel we will create this invoice template without using VBA and we'll use the basic Excel functions and features which we have launched in previous videos to have the maximum calculation simplified and automatic this template is perfect for start-up businesses and individuals alike it can be used to perform the simple invoicing tasks so without wasting much time let's see how we can create an invoice template with the help of basic functions in Excel as you can see we have a blank sheet here and another sheet that contains product details we'll use this product details in the template to fill out the descriptions anyway we come back to invoice template just blank sheet and then we move our cursor to b1 we first insert the company logo here so we go to insert tab and then we click on pictures and insert the company logo this is too big so let's make it smaller so we reduce size of this image this looks okay okay we have placed the company logo on the left side of the page however in case if you want to place it anywhere on the page you can place it as per your own choice then we go to i1 and we type invoice and let us change the font size of this text from 11 to 24 and then we will make it bold and now we'll change the font player as well so we go to font color and we look for the code nice font color which matches here you go this is blue X inch 5 so we'll keep it blue X in 5 for this invoice font color and now we'll type the company details that are selling the products at the top of the invoice so we go to b4 and we type company name in the packets for now we will have the simple text instead of taking actual details but you can change it as by own convenience so wherever we are inserting the text inside the packets you can replace it with the actual values then in b5 we will have street address then city state zip and b6 phone detail and b7 then fix to tail and b8 all right this is enough for the company information and now we will add the customer details like name company name address phone and emails etc so we go to be 11 and first we type here b2 then we go to b12 and there will have the customer name so we type name here inside the bracket then company name here in B 13 street address in B 14 city state zip in be 15 and and B 16 and then we will have email address in b-17 all right every invoice must have few standard details like customer ID date invoice number etc so in order to add them we go to cell h5 and we type customer ID then we type invoice date invoice number and payment do buy in columns at 6 X 7 and h8 respectable we will have these details in corresponding cells from i-5 to I a so we select I 5 - I ate range and we go to Home tab and under font category will select all borders and come back then we select H 5 - h 8 and we press control 1 to open the format cells dialog box there we go to alignment tab and we select right indent in the horizontal text alignment category and we increase the indent from 0 to 2 and then we click on OK the moment we click on OK we can see that the texts are indented and looking much better than they were looking earlier to enhance the visual appeal will make these headings as bold and come back ok now we can enter the required details in the required cells to start with customer ID we can put any vend me ID for now and let's say we enter TT see 2 4 1 3 4 then in i6 we will have the current date and for that we can use today function as every time we'll be making invoice we must need the current date and today function will return the current date for us ok in case you want any other gauge you can supply it manually instead of entering the function in the set in i7 we have to enter the invoice number so for now enter any random number so we enter X Y Z 1 2 3 4 z and then in payment you buy will add 20 days into the invoice date and come back all right now if we look at column I change the text is going left whereas dates are right which does not look nice no problem will change the alignment of sells to the center and then we'll change the date format also to make it look nice so you go to number and here in date we select this days month and year format and then we apply the same here by pressing f4 key which is used to repeat the last action good now let us have the formatting for Bill to field as well so we go to be 11 and highlight the range from b11 to f11 then we go to fill color and we will fill this with something dark okay this looks nicer this is stark view I guess yes this is dog loo so we select dark blue Hill and then white for the font color and we'll make it cold after making it bold we go to border area and we select outside border for the selected area and come back now we'll move on to the description part of the invoice will add description and other details regarding the items which we are selling to the customer so we go to be 19 and we type item code then item description in C 19 as item description could be lengthy so we highlight C 19 to EV 19 and we will merge across the cells will merge across more cells under the item descriptions column to have the description of items but you don't need to go to Home tab every time and click on merge clothes options as you can repeat the last command using f4 key by highlighting the required area or you can also copy the merged area and paste it from the cell till where you want it by doing this you can save lots of time isn't it interesting anyway we have merged cells till 30 second row and we have enough rows now to have the products for this invoice template but in case if you want more rows you can add it here now we come back to row 9 and we go to g19 and with type quantity as heading as this column will contain the quantity for these items then we need unit price for this item in h19 and then we will have amount in i- alright since we have included all the categories so now we'll form at this table and we'll make the entrance first we select and highlight b19 to a 32-inch and then we open format cells dialog box they we jump to border tab and we select line style first and then we click on outline body the moment we click on outline boarder you can see that box displays a preview that how and where it will be applied after that we want to highlight each column so we select the necessary border line and then we click on OK and as soon as we click on OK we can see that the border has been applied to the table successfully but before were to apply the outline border for the whole 19 so we select the row 19 and we press control shift 7 which is a shortcut key to apply the outline border once we are done with border formatting will fill the color with dark few and then apply white font color to the headings of this table and then will make them port after that we make the changes in the alignment as per the input type for text will keep it unchanged as left and four numbers entry we change it to the center alignment since the item code and quantity will be supplied as number so we select item for change and quantity and then we change it to the center alignment to center the content the item descriptions will be in text format so we'll leave it as it is then in last unit price and amount we'll change it to the center alignment and then we open the format cells dialog box and we go to number and we'll use the thousand separator with the zero decimal place since we have formatted our table of descriptions will now enter the products so we'll allow users to only select the product and the rest column should automatically be updated so in order to start with it we will have the item descriptions pass so we select C 22 C 32 and then we first open the data validation dialog box we select list on the allow category then in source we go to product retail sheet tab and here we select a two to a 10 that contains the item name here since we have already made the list of products but in case if you want to add or delete or make any changes in the product you can make it here then accordingly you can expand the data validation list range anyway we click on OK and now we can select any item and product name from the drop-down list then we go to cell be 20 and there we have to pick the item code based on the item selected in itself C 20 so to pick that we will use vlookup function here so we type you lookup function here sorry since column C stores the value which we want to look up so we select C 20 as the lookup value and then we go to product detail sheet tab to select the table area we select the table array range from a2 to C 10 and then we press f4 key to make it absolute reference since our function would be moving in column B to down side and when we copy and paste the function to another location from the original point with the relative reference the cell references also gets changed with that and then there are chances that the function returns any error because it will not be able to find the lookup value in the table array so if you do not want cell reference to change use absolute reference to save the time and as we have to pick the second column the item code from the table array we enter 2 in the 3rd argument then in the last argument we'll enter 0 to return the exact math now hit enter the moment we hit enter function returns item 4 for the selected item and see 20 anyway we copy and paste the formula only till B 32 however this then when we paste the formula in the rest cells we get any error but why that's because there is nothing in the corresponding cell to look up the value hence function returned any error in the rest self so to avoid returning L will nest we look a function inside if-else function and we'll tell in case any occurs it should return empty text now we obtain the formula okay now we will pick the unit price for the selected item and we'll use the previous method only so instead of repeating the same action we go into the added more of this cell v20 and we copy the entire formula and then we come back to h20 and we go into the edit mode once again and now we paste it here then as we have to pick the third column that contains unit price in the product detail sheet tab so we make the necessary changes here and then we hit enter to get the unit price of the particular selected item but in case if you want to check you can go to the product detail sheet and you can look for the product which we had selected there in the invoice template chip and here you go you can see that the price of the per unit for this product is two thousand four hundred fifty and when you come back you get the same price here also anyway we copy and paste on the function till H 32 now we come to I 20 where we will multiply the unit value by quantity to return the amount so we enter the calculation in error function to turn an empty text in case honey occurs by the calculations alright now we pace till I 32 now we go to H 34 and here we will type subtotal then we'll have the text amount fill in H 35 then discount category in s 36 and total in h 37 then we select I 34 to 37 and apply all borders and then we go to format cells to select the Indian currency rupee English India with zero decimal places so you look here for English India currency it is and then we'll reduce the decimal places to zero and then we click on OK you can choose any currency format according to the requirement and now we change these cells to the centre alignment then we go and select H 37 in I 37 and once again we go to format cells to change the border style of these two sets so we jump to the border tab and there with selectable lines to apply on the top border and we'll let the single lightweight line for right border and click on OK and then we make them as bold now we go to cell I 34 and enter some function and select I 22 I 32 as some range and then we hit enter in cell I 35 will get the text amount which will be applicable on the subtotal value so we multiply the subtotal by the text rate whatever is applicable and then we'll leave the discount cell as empty as it will be entered manually if there will be any discount and now we come to I 37 and there we type some function once and we take the sum of a 34 and I 35 then we subtract discount value from the sum written by I 34 and I 35 now we are almost ready with the template and we add special notes and instructions and other things to give a final touch to this template we go to be 34 and we type special nodes and options and then we select B 34 to 38 and we'll apply the outline border and then once again we select B 34 to F 34 and we apply the outline ball up this stage as well and then we apply the formatting fill the color will fill the point fill and then we make them as here you can include the special notes in instructions like payment terms are 20 days from date of invoice please include the invoice number on your cheques will format them as italic and will reduce the font size to 10 okay now we'll add the signature area so we go down and we select H 43 and I 43 and we apply the top border and we go to a line man and there we select Center Pro selections in the text alignment horizontal category and then we click on OK then we come back to H 43 and there we type signature have you noticed one thing that the text is starting from the center alignment of both the cells that's because of the cross across selection styles of the alignment we had selected in the format cells dialog box isn't interesting you don't need to worry to make it centralized manual anymore or to merge cells for such requirements now we'll add few more lines before we finalize the template to you so we go to be 14 9 and then we select be 49 to I 14 9 and once again we go to format cells and we'll change the alignment to Saint Lucas selection and click on OK we'll repeat this action for the next 4 lines till 53rd row but instead of repeating we copy the entire row and we paste it till 53rd row so no need to go to dialogue box again and again to do the same thing ok now we are back in be 49 so we type equal to double quotes make all your checks payable to then we add ampersand sign and we give a cell reference to be for that contains the company name and as soon as we hit enter instead of a cell reference company name is appearing along with the text which we had supplied manually so this is working as formula and what we did is we have combined text and a cell reference together so whenever you make any changes to the company name the check in favor of instructions will be updated automatically ok now we come back and here in the next line in B 50 we say in case of any inquiry concerning this invoice please email us at Y your email id and then in the next line we say thank you for your business and hit to make it appear better we convert these lines into italic format and then we reduce the font size of first two lines to ten and then we make last line as both we are ready with the invoice template now we go to product and select few products in different cells through drop-down option in column C and then we enter the quantity all this and as soon as we update the quantity you can see all formulas are getting updated automatically and all the amounts have been updated so we are ready the final amount which customer has to pay so in case if there is any discount you can mention it here the discount will be subtracted from the total value and you will be ready with the total value which customer has to pay so almost the entire template is automated except few places like customer information and vendor information then customer ID invoice number product will go down and quantity then we have discount which are supposed to be filled out manually and this is not enough when you go and check the Print Preview you do not need to set the page area as it perfectly fits on one page so you can simply give the Print command and get the printouts without spending more time on page setup or in case if you want to save as PDF go to file save as and browse the folder way want to save the file and that is going to save here and then in save as type we select PDF and then we click on save button the PDF file will be generated in the path selected and it will be opened automatically here as well so you can have the idea that how your excel file will look like when you will change into the PDF format so we go back and this is how you can create automatic invoice template using simple and basic Excel functions in Excel also we would recommend you to send invoice as PDF only to your customers always if you liked this video don't forget to click on subscribe button to get regular updates and feel free to share this video tutorial with your friends as well that's all for now we hope you found this session informative stay tuned and keep learning with us thank you and have a nice day