Discover the Perfect Bill Book Format in Excel for Purchasing

Bill book format in excel for Purchasing

welcome back here in this new video tutorial today we are going to learn how to make a template to track the stock in out and balance as you see here in the screen the balance doc will auto update as you keep on tracking the details of in and out of the stock let's start with the process of making this in A3 cell type stock in select these three cells then merge below it right column header of date item name and in in quantity adjust the column size in the D3 cell type stock out select three cells and merge below it copy and paste these column headers change in to out here adjust column widths in G3 cell write stock balance select these two cells and merge them below this right item name and balance quantity now select it all and select till row number 50 this you can increase ing to your requirement then apply the borders now highlight stocking out and balance headers with different colors now here in item name name of stock balance enter the items or products that you are selling for example I am entering here some electronic items now make drop-down selection menu here in the item name column of stock in section for this select from B5 to b50 then click on data tab then data validation here in this drop-down choose list in the source of list click and select the item name range of the balance stock column while selecting take some more rows because if some item is added in future it will appear in the drop-down too then click on okay now you see here the item selection dropdown is created now copy the first dropdown then select item name range of stockout section rightclick on Mouse click on paste special select the validation option then click on okay now the drop-down list for stockout Section also created now let's add few transaction here in this stock in and out section now here in this balance doc we have to subtract the out quantity total from the in Quant total for each items for this we have to use here some IF function here in the first cell press equals then type some if open a bracket in the range select the item name range of stock in section lock this range by pressing F4 key in the keyboard give a comma in the criteria select the item name of stock balance section give a comma in the sum range select the stock in quantity range then lock this range by pressing F4 now close the sum IF function with a bracket now put minus symbol again write sum IF function Open Bracket in range select the item name range of stockout section lock it by pressing F4 give a comma in criteria select item name here in Balance section Ty type comma in some range select the quantity out range then lock this range by pressing F4 key close the bracket and press enter here we subtracted the stock out from stock in to get the balance now when we update the records of stock in and out transaction balance stock column will automatically update simply use the fil handle to drag the formula down so this is how we can make a simple template to track the inout and balance of inventory or stock in Microsoft Excel if you want to practice this download the workbook file from the video description below consider subscribing this channel if you have not see you in next video tutorial