Your Ultimate Jewellery Invoice Format for NPOs

Jewellery invoice format for NPOs

hello and welcome back to the exclusive video today in this session we will show you how to create an invoice template using Microsoft Excel we will create this invoice stamp it without using VBA and then use the basic Excel functions and features which we have launched in previous be used 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 starts 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 description and it come back to invoice template branches and then we move us closer to b1 we first insert the company logo here so we go to insert tab and then we click on pictures and we insert the company logo this is too big so let's make it smaller size of this image 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 at for your own choice then we go to i1 and we type in voice 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 size as well so we will pause color and we look for those two nice font color which matches here oh this is new exchange 5 so we'll keep it us in 5 for this invoice on to it and now we type the company details that are selling the products at the top of the invoice so we go to b4 and we type containing in the packets for now we will have the simple specs instead of taking actual details but you can change it as for your own convenience so variables we are inserting the text inside the package you can replace it with the actual value then in v5 we will have cheap address then city k-6 and basic phone detail in b7 then fix to tail and b8 alright 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 we'll have the customer name so we type name here inside the bracket then company name here in B 13 sheet address in B 14 city stays a in be 15 and and be 16 and then we will have email address in b-17 all right every invoice must have you 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 in voice gate invoice number and came in to buy in columns at 6 X 7 and 8 respectively we will have these details in corresponding cells from i-5 to I a so we select I 5 - i 8 range and we go to Home tab and under phone's category will select all bottles and come back then we select h5 to H a and we press control 1 to open the format cells dialog box there we go to alignment tab and we select right indent and 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 we make these headings as bold and come back okay 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 HIV enters TT e 2 so 1 3 4 then in i6 we will have the current date and for that we can use today's function as every time we'll be making invoice we must need the current date and could a function will return the current date for here in case you want any other J's you can supply it manually instead of painting the function in the steps in I 7th we have to enter the invoice number so for now enter any random number so we enter X Y Z 1 2 3 4 V and then in payment you buys we'll add 20 days into the invoice date and come back all right now if we look at column ID inch the text is going left whereas dates are side which does not look nice no problem we'll change the alignment of this cell 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 mark and ear format and then we apply it same here by pressing f4 key which is used to repeat the last section good now let's 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 print color and we will fill this with containing dots okay this looks nicer this is costviewer this yes this is w so we select dark blue here and then white for the font color and we'll make it cold after making bold we go to border area and we select outside border for the selected area and come back now we 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 X 19 and we will merge across the cell 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 those options as you can repeat the last command using f4 key by highlighting the required areas 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 and then click interesting anyway we have merged cells in 30 second row and we have enough tools now to have the products for this invoice campaign but in case if you want more rows you can add it here now we come back to row 19 and Gogi 19 and with tight quantity as heading as this column will contain the quantity for these items then we need unit price for these items in h 19 and then we will have amount in i-90 all right things we have included all the categories so now will form at this table and we'll make the entries first we select and highlight B 19 to I 32 which and then we open format cells dialog box maybe jump to border tab and we select line style first and then we click on outline border to move it we click on outline borders you can see that book 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 successful but before were to apply the outline border for the whole 19th 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 will ask you and then apply wipe own color to the headings of this table and then will make them fold after that will make the changes in the alignment as per the input pack for text will keep it unchanged as left and four numbers entry we change it to the center alignment his item code and quantity will be supplied as numbers 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 will change to the sensor alignment and then we'll open the format I love pork and we will number and we use the thousands separator into zero decimal place since we have formatted our table of descriptions will now enter the code X so we 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 description part so we select 320 to see 32 and then we first open the data validation dialog box we select list and allow category then in source we equal to product it'll sheet tab and here we select a two to a tenth that contains the item name here since we have already made the list of products but in case it is going to add or delete or make any changes in the product you can make it here then ingly you can expect the beta validation listings anyway we click on OK and now we can select any item and product name from the drop-down list then we will sell v20 and there we have to pick the item polls based on the items selected himself 3:20 so to click that we will use vlookup function here so we type we lookup function here so you use column C stores the value which we want to look up so we select 320 as the lookup value and then we go to product detail sheet tab to select the table area we select the table array range for it to to see 10 and then we press f4 key to make it absolute reference since the function would be moving in column B to downside and when we copy in space the function to another location from the old school's 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 game so if you do not want cell reference to change use absolute reference you save the time and as we have to pick the second columns that contain 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 match now hit enter the moment we hit enter function returns item code for the selected item EC 20 anyway we copy and paste the formula only kill B 32 however this term will be pasted the formula in the red cells we get any error but why that because there is nothing in the corresponding cells to look up the value hence function returned any error in the red cell so to avoid returning L will nest we lookup function inside its else function and will tell in get any error occurs it should return empty pegs now we come to 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 add more of this cells be 20 and with copies of entire formula and then we come back to H 20 and we're going to 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 she said so we make the necessary changes here and then we hit enter we get the unit size 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 those products which we had selected there in the invoice template sheet and here you go you can see that the price of the per unit for this product is two thousand four hundred fifty and then you come back you get the same price here also and with coping and fills down 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 F error function to return n t-take in case any occurs by the calculation alright now we please chill I fatigue now we go to H 34 and here we will type subtotal then we'll have the tax amount fill in H 35 then discount category in s 36 and total in h 37 then we select I 34 to i7 and apply all borders and then we will form itself to select the Indian currency rupee English India with zero decimal places so 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 ing 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 steps so we come to the bottle 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 bolt now we go to cell i 4 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 sum function once our and we take the sum of I 34 and I 35 then we subtract discount value from the sum return by I 34 and 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 the stamp it we go to be 34 and we type officials note and suction and then we select B 34 to 38 and we'll apply the outline products and then once again we select B 34 to F 34 and we applied outline bottom pinch as well and then we apply the formatting fill the color will fill the console and then we make the nice boy here you can include the special notes in instructions like payment terms are 20 days from date of invoice please include the invoice numbers on your checks will format them as italics 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 post 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 notice one thing that text is starting from the center alignment of both the cells that's because of the course across selection styles of the alignment we had selected and the format cells dialog box isn't it interesting you don't need to worry to make it centralized manual anymore or to merge cells for such requirements now we'll ask you more lines before we finalize the template to you so we go to be 14 line and then we select be forty nine to five fourteen nine and once again we go to format cells and we'll change the alignment to send to the post selection and click on OK we'll repeat this action for the next four lines is fifty third those but instead of repeating we copy the entire row and we paste it till fifty third row so no need to go to dialog box again and again to do the same thing okay now we are back in be 49 so we type equal to double quotes make all juice checks be able to then we add in % and we give a cell reference to be four 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 okay now we come back and here in the next line in B 50 we say in case of any query concerning his voice please email us at why your email ID and then in the next line with a thank you for your business and hit enter to make it appear better we convert these lines into italic format and then we reduce the font size the first tool I 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 with the final amount which customer has to pay so interested there's any discounts you can mention it here the discounts 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 campus is automated except few places like customer information and vendor information then customer ID invoice numbers product will go down and quantity then we have discounts which are supposed to be filled out manually and this is not you know 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 tests if you want to save as PDF go to file save as and solves the foldaway want to save the file and let's save on save here and then in save as type we select PDF and then we click on Save button the PDF files will be generated in the past selected and it will be open automatically here as well so you can have the idea that how your Excel files will look like when you will change into the PDF online so we go back and this is how you can create automatic invoice templates 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 video friends as well that's all for now we hope you found this question informative stay tuned and keep learning thank you and have a nice