Maximize Efficiency with Billing Software in Excel for Product Quality

Billing software in excel for Product quality

[Music] hi guys today we're going to build our own billing management system using Excel vlookup function Excel vlookup function is a useful tool that allows you to look up a value in a table or range of cells and return a corresponding value from another column in the same row the v stands for vertical as the function looks up the value vertically this is how our system will look like where we will be able to generate invoices with automatic price lookup so we have our inventory data in Excel as follows we have product names product IDs available quantity and price per unit for the product foreign [Music] we want to build a billing system in this table save time I have already formatted this table [Music] in the First Column we want to be able to select a product in the second column we have the quantity needed in the third column we calculate the price per unit for the selected value in column n and in the last column we have the total per selected product now to be able to select items and column in we will follow these steps select cell and two go to data validation select list and select column range from cell 3 to cell at 20. now we can select a specific product copy this formula to all cells in column in foreign [Music] next we want to take the sum of all quantities so we use the formula sum to make the sum of the cell contents in column p in the cell Pete well we want to have the number of all the items purchased by the customer now we want to find the unit price of the selected item in cell and two to do this we use the of air and vlookup functions if error is needed if there is no value in the inventory data so we set 0 as the default value next we want to use the function vlookup to find the price of the product vlookup has the first argument as the lookup value the value we want to look for the next argument is the data range or table from which we want to find the price value the next argument is the column index where our price exists in our case the current price is in column 4 and the last argument is zero for the exact match if we want the closest match we enter one here but for this example we want the exact match vlookup is done and to complete RF error function we put 0 as the default value now copy this formula into the cells of column Q [Music] in the last column we now want to multiply the quantity and the unit price of the product [Music] and in the cellar well we want the total amount that the customer has to pay or the total invoice amount [Music] our invoicing system is now ready for use let us try it select a product enter the desired quantity our amount per item will be calculated automatically also we can add more items [Music] foreign [Music] new item in our inventory that item will be available for selection in our Billing System for example we added genes in our inventory and it is available for us in the product selection option [Music] foreign ly if we delete something from our inventory it will no longer be available for selection in our system [Music] so by using vlookup and if air we have now built our own Billing System thanks for watching please like share and subscribe for more such tutorials [Music]