Create a Commission Bill Format in Excel for Shipping Effortlessly

Commission bill format in excel for Shipping

welcome to excellent cat my name is AJ Anna in this video I will explain how to create a dynamic invoice template using Microsoft Excel see the column for products in this template the ninja item is selected the cells for unit price and serial number are automatically populated just type in the quantities and here we have the total amount now to print this invoice go to the file tab click on print once again print type in the file name save and here is the PDF copy of our invoice now let's see how to create an invoice template bankís we will start with the details of the seller select the cell b5 typing sold by once again select the cell b5 along with the adjacent cells will change the cell background color to dark blue phone color to white apply bold format now the company name street address city coma state pin code phone number then the main lady so these are the details of the seller now for the details of the customer select the range of cells from b-52d 11 right click copy select the cell F file right click paste the copied cells sold by should be modified to billing at us change the phone number and the manor ID now the invoice number invoice date will adjust this column width for some room select the cells g13 and h13 merge and center now copy this format to the cell below I will use this cell for the invoice number ABC forward slash 0 0 1 now the invoice date gives today function for the current date apply border for these cells bold format now for the list of items their prices quantity and amount select the cell b7 t first column header will be serial number item in the next cell select the cell c-17 and the two adjacent cells margin center unit price in f17 quantity in the next cell and finally amounting 872 a length this column address to the center of the cells select the cells in the group for alignment click on center will make it bold will change the background color to dark blue font color to white once again adjust the column bits now the column for items to create a drop-down list containing the available items create a new sheet for the list of items item unit price product one product to you say Excel fill handle total the remaining cells now enter the unit price for each item in the second column five hundred seven fifty fifty seventy five eighty six hundred eight fifty two forty five three fifty and 650 now back to the first sheet select the cells from C 18 to 80 merge and center to create a drop-down list in this merged cell go to the data tab data validation click on this down arrow select list click on the up arrow from the second sheet select the cells containing products click okay now when I click on this down arrow all those products listed in the second sheet are shown here using the Excel fill handle we can copy the drop down list into the cells below I will copy this format up to row number 37 so that we can list up to 20 items in this invoice now to feed a product name into a cell select the cell click on the down arrow and select the item product to product 7 next comes the unit price of each item whenever I select an item from the rock down list the corresponding unit price should appear in the adjacent cell for that equal vlookup function self containing the product name is the lookup value comma second argument table array the table containing the list of items and the corresponding unit prices is the table array plus the function here for to load the references of the table array comma third argument column index number unit prices are in the second column so two is the column index number comma folds for an exact match close parentheses plus the Enter key and the formula returned the unit price of product for let us check the source table 75 is the price against product for copy the formula into the downward cells we have the unit price of every selected product now if I copy this formula into the cells below the formula will return it not available error the reason being the cells containing lookup values are empty so to track this error we will use if error function select the first cell containing formula in the formula bar before we look up function use if error function coma type in double quotes two times close parenthesis and press the Enter key once again copy the formula into the downward cells now select a product using the drop-down list and the corresponding unit price will appear in the adjacent cell next one is the amount part amount is the product of quantity and unit price select the cell h80 equal F 18 in dude-g 18 I will enter the quantity of each item here copy the formula for mo into the downward cells once again when I copy this formula into the cell below we're adjacent cell is having an R value formula will return a value error to track this error we will again use if error function if error function coma double quotes double quotes close paranthesis press the Enter key copy the formula into the downward cells now comes the serial number part for serial number in this forum to update automatically equal if function select the adjacent cell note equal double quotes double quotes comma rows function select the cell see 80 full : once again select SLC 80 close paranthesis make the first cell reference inside ROS function absolute that is an taller symbol in front of the row and column indexes coma double quotes double quotes close parentheses and press the Enter key copy the formula into the downward cells now when I select a product the serial number and unit price will update automatically enter the quantity can be how the amount let's align this text to the center of the cells a plain number formatting for the columns containing unit prices same for the column containing amounts apply outside border for each column now the most important part total amount for the sum of the figures in the column for amounts select the seulji 39 typing total for the sum of figures in the column for amounts equal sum function select the cells form 18 teen 2h 37 for the tax amount select the cell g---forty tax percentage will be fifteen select an adjacent cell equal age 39 into 15 percentage for grand total select the cell g41 type in grand total in the adjacent cell equal sum function select the cells age 39 and age 40 and press the Enter key will apply bottom border for the cells g4t and age 40 all borders bold format now the space for signature or seal select the cells G 44 and age 44 apply to a border margin Center type in signature OC to improve any kind of instructions or nodes at the end of the bill select the cells margin Santa back to the top of the sheet select the cells from f2 to h3 noise and sender we will use this cell to display the name of this document it can be involve in of supply or whatever you want to call it keep the phone size around 16 so that this text will stand out select the cells from b2 to h3 apply bottom double border to view this format without gridlines go to the View tab and mark the checkbox for the fans now the printing part go to the page Layout tab click on size select a4 click on this button for page break preview the portion inside this blue border is the print area I will add just this border for some room on right side once again adjust the width of column a 25.8 same for the column I for the Print Preview go to the file tab print and here is the print preview of our invoice for more adjustments click on page setup margins we'll modify the width of my leads to zero mungus networks to position the data has only okay you can see that we are not utilizing the full size of this fo page so once again page setter adjusts the size to 100% okay once again 110 percent I think it looks much better now except this note at bottom which is aligned away from center so let's align it back to the worksheet select the merge cell and that distance alone right side hope that click on merge and center two times again file tab page setup and just the size to 115 percentage now to print this file as PDF click on print enter the file name save I hope you will find this video useful for feedback and discussion please make use of the comment section and those who always think the Sun for the first time subscribing first apply lichen so that he wants anything thanks for watching have a great day you