Streamline Your Workflow with Our Invoice Tracking Spreadsheet for Procurement

Invoice tracking spreadsheet for Procurement

in this video we are going to see how to create a purchasing control in Excel a step by step from scratch with suppliers products and purchases and by the way if you notice here in the header we have a navigation menu that can help us navigate throughout the sprad sheet much easier and faster just clicking the button and automatically you can see that you can change the sheets and also the buttons are going to be automatically highlight based on the activate sheet that you are currently at but let me go here to the purchases and let's see what we are going to learn in this video we are going to create this template right here where we can basically control everything that we are purchasing we also going to create some automations using some lists in Excel to help us with the data entry instead of manually input the name of the product that you need to buy you go here to the list you click in the sell to activate the list and then you select anything that you need click and that's it simple as that as the supplier we have the same thing a list to select and also as the status that can automatically change the colors the status is based on the delivery date and it can be overdue delivery or on time delivery or receive it or pending purchase or you can also customize any other status that you need to use we're going to see step by step from scratch and with we go to the products we have a list for all the products and here in the suppliers we have the list of our suppliers and something very special about this list is I can see the quantity purchase and also the total purchase for each one of those suppliers using as criteria a start and ending date I want to see only the things that I purchase in January I can change the start and the ending point I already have as the start January 1st so let me go to January 31st of 2025 if I hit enter the values underneath are going to retrieve the addition based on the criteria of the dates the range so let me hit enter okay as we can notice the values were upate for us so that way it's a excellent way to make analysis throughout these suppliers lists that we have and I hope you guys enjoy this video on how to create this complete purchasing control in Excel step by step from scratch so let's go let's get it started with the navigation menu so let me click over the first row and right click and go to row hide maybe we can double it so instead of 15 I can use 30 and then okay and as this first row is a white selected I can go to the Home tab and change the background color to a darker blue such this one right here now let me click in insert in order to create the buttons and go to chapes here I can stick with rectangle but of course you can use any other shapes that you like let me click hold and drag this rectangle right here it's very important to make sure the rectangle is up and all the way to the left okay just here into the corner of the spreadsheet now something that we need to be mindful is if I change for some reason the width of any column that I have let's say column column B click hold and drag to to the right as we can notice the area of the button is all also going to change so I don't want to allow the button to change the area let me right click and then go to format shape or uh size and properties doesn't matter let's me click here format shape now to the right I can go to the last option size and properties properties and then don't move and then okay now even though I change the width of the cols as we can see that shape is going to stay in the same position and with the same area as before it's going to remain with the same area now let me double click want to to be able to input some text within the chape the first one can be purchases okay let me select everything I want to align in the middle or vertically and horizontally increase the font size maybe to 18 put everything in bold and shape for mat I can choose a different style such as this bluish right here in order to create the other buttons that we need uh the products and the supplier I don't want to do it manually all over again so let me click in this button and then contrl C and contrl V to cope and paste I can drag to the right contrl c contrl v again and drag to the right okay the one in the middle is going to be the products and the last one is going to be the supplier or suppliers okay now what we can do is we can make sure because now we have all those three buttons that we are going to use we also can create the other two sheet so however before we do it let me change something right here now we are in the first template or in the first sheet purchases so let me go here to the bottom of the the footer of the Excel and then right click on the the sheet itself and then I can go to rename this one is going to be purchases enter now because the purchases is the current sh I can click in this button and go to shape format and change the Highlight to a yellowish one to actually highlight this button and that way we're going to be able to know what is the sheet that we are curing to add and the font color maybe we can change it to a darker one maybe blue and all the other buttons we can decrease the size of the font maybe to 10 and okay now whenever you click in any button the Highlight is going to change depends on the sheet that you are currently at and in order to create all the or in order to create now this same header or the same navigation menu to all the other sheets let's duplicate this one so press and hold down the control key okay press and hold down the control key now go here to the purchase sheet click hold and drag to the to the right one more time okay now this one right here in the middle is going to be the product and the last one is going to be the suppliers enter now as we can notice I have the navigation menu to all the different sheets that I have now with we go to the second one products let's change the Highlight because now we are at the products she so let me click here in the purchases and I want to cope this format to the side to the products so click just on to select and go to the format painter double click one two now go to the products now press Escape select the suppliers double click in the format painter one two and go back to the purchases okay now as we can notice if I go to the purchase sheet or if I go back to to the products uh the highlight of the btom is going to change following the activated Fe that we are at let's say now let's go to the suppliers and do the same thing let me click here on the purchase button once two in the format painter and click on the suppliers ask and then products one two purchases okay now everything is working as we can notice if I change the sh we can see that the buttons are going to highlight automatically depends on the she I'm currently at but we are not done yet because if we click into the buttons on the buttons we can see that nothing is changing and how can I dynamically set the buttons to change throughout the sheets that I have so let's say now I'm in the purchases if I click in the products I want to go to the products immediately so how can I do it let me CLI with the right click into the produ into the purchases buttons and then I can go to link and within this popup right here I can choose products in in the place in this document okay let me click okay now I want to do the same thing for the suppliers right click link and suppliers okay now if I click into the products look what's going to happen now it's working let me go back to the purchases and click on suppliers it's working uh but basically we need to do the same thing again into the the products sheet right click in the purchases link and then purchases suppliers right click link and suppliers okay now let me go to the suppliers and again purchases right click link purchases products right click link and then products okay now everything is working as we can see okay because we done we can move on to the next step that is to create the templates itself and as the first template we can start with the purchases however before we get started let's decrease the width of the First Column let me click in between one column and another click holding dag to the left maybe we can use two as the width okay now let me go to the cell bit tree and here I can start with the headers names so has the date and the product name also I can have ID or SKU or so forth quantity unit cost total cost uh maybe we can have here the delivery date and the supplier name also we can have the freight cost and because in some situations we can have the freight let's say uh the unit cost that we paid in a specific product is $10 however the freight is equal to $50 so the unit cost is not actually $10 because we need to include the freight into this cost and if you want to do this calculation you can create two more columns one to retrieve what is the unit cost with the rate so let me just uh use here a shorter R and I can also have the total cost with the freight and as the last CH I have a information that is very important the status so let's go back here to the first CH the date let's say I purchase a it 10 on January 1st of 2025 okay and what is the product that I purchased so the product can be many different products that I have however I don't want to manually input the name of the product all over again every day every other day every time that I need to purchase this specif product so the way that we can automate this task is creating a list in Excel whenever I click in this cell where I have the product I can open or it's going to opens up a list where I can choose the product that I need and in order to create that we can go here to the products and create the list of products so let me again in the column a decrease the width to make it uh smaller maybe we can use two again okay and I want to start again in the cell bit tree with the headers and as the first information I can have the update date so that way I know what is the last time that I update a product within my list so let's go now to the second column that can be the product name and is the product name this ch right here is going to be responsible to create this product list that we going to have so product and then ID or SKU or any other type of identification that you need to use to your product I can also have the brand name and maybe the Shelf location okay let's start with the update date January 1st of 2025 the first product that I can have maybe it's a mouse let's also input here another one let's use the same date the second one maybe can be a keyboard the third one can be a monitor or a four 4k monitor and then the another one can be a 8K monitor let's stick with those options let me increase the width of the products column also the column B and as the ID I can create my own ID or I can use a ID identification that CES with the product but let's say I want to create my my customized ID for each one of those products I want to start with 8,000 if I bring this number down I can create a sequence let me click here in this little tag out of F and feel serious okay but let's say for some reason you have many different roles and that way you're going to have many different IDs and you just use again the same ID that you already use it before let's say a th and one a,1 a th001 the same ID for two different products maybe it's not a good idea and maybe it cannot happen so why you can do is you can select this entire this entire range and whenever you have some repetitive values you can highlight them with a red color or Excel is going to automatically highlight for you so let's go here to the Home tab and conditional formatting highlight sales rules and I want to highlight whenever I have a duplicate value okay just click okay and we done uh Excel is not going to prevent you to input a repetitive value it's not going to avoid however it's going to show hey there is a value that you already used before so it's going to automatically highlight with the this red wish color okay I think it's important now the brand let's say 1 2 3 ABC 1 2 3 ABC and so forth and the Shelf location A1 A2 and okay that's it uh something that is very important in order to make this list of the products always automatically update for us whenever we input new items into this list the list that we have right here is going to automatically update with the new items we need to transform this data set into a table in Excel so select everything and then go to insert and then table my table has headers and then okay let's use a different design for this table table design I want to stick with a orange one but you can use any other style that you like now let's go here to Home tab and Ally vertically and horizontally everything uh accept the products because we can select this list and allly to the left okay much better right now but how can we create a list based on this table that we have something that we can do is go to any cell that you have any blank cell and then equal sign we are going to take a reference right here so equal sign and then select the list of products don't need to select the header because you don't want to include the header the name of the header into the list but just the products right so select the product product okay you're going to have something maybe like this the table name and also the reference of the CH what you need to do now is Select everything but the equal sign don't need to select the equal sign right click and then code okay now press ask to to go out to the sell now you can go back to purchases click here in underneath the product header and go to data data validation data validation we can create a list so instead of using allow any value I want to allow a list and within this list I want to use a function or a formula equal sign inir open parenthesis open quotations right click paste close cotations close reneses and then you can hit enter that's it now our list is already working mouse keyboard 4k monitor and let's say the last option that I have is 8k monitor one two three four different items if I go back to the products and I add here a new value such as January 1st of 2025 and new item enter 1 2 3 1 2 3 one 2 3 okay if I go back to the purchases and click here in the list this I can see that this new item is already update in the list and if I go back to the product now let's say we already learned how can we add new item and you can apply the same method for all the different sheets that you're going to have purchases products and suppliers if you want to remove get rid of a row you can click in the number here to the left corresonding to the row right click and then go delete that's it so this is how you can add or get rid of a product or a br in Excel doesn't matter what sheet you are let me change this ID right here okay now let's go back to the purchases I already have here that new item that is the item that I got rid of however um if I click here in the list this product is not here anymore because it's going to automatically upate for us now if we move on to the ID the ID is is going to be based on the name of the product so maybe we can use a v lookup function to retrieve the ID based on the product that is our criteria so let's go here to the ID equal sign B lookup function double click one two the lookup value is going to be the product name comma the table array is right here into the products and be mindful go to the foot and change the sheets through the the tables that you have right here okay through the the tabs right so products and I want to select let's say column C and D comma I want to retrieve the first or the second column the second one because the second one is where I have the values that I want to return so number two this is the column comma and I want to use a exact match one two and then enter okay now whenever I input a product right here as we can notice the ID is going to automatically change okay it's working the quantity needs to be manually input so let's say I bought 10 unities of a keyboard and the unit cost is going to be $997 and because we are using here currency maybe we can select both of those Sals unit cost and total cost and also maybe okay both of those cells home time and go to the dollar sign right here to change the format into a currency one okay what I can what I can do here with the total cost the total cost maybe can be a simple calculation that is equal sign unit cost times the quantity and then enter or vice versa because let's say you have the value the total value of the invoice and it's 111 or $115 so how much is the unit cost let me increase the width of the count G okay the unit cost is going to be equal sign total divides by the quantity and then enter simple was that so vice versa but let me stick with the option that I did before okay now we can move on to the delivery and the delivery is the delivery date so let's say I bought the keyboard in January 1st and the supplier said me that it's going to be delivery uh 3 days after the purchase so equal sign the date add to three days and then enter so this is the date that is supposed going to be delivery my my purchase now the supplier how can we do with the supplier the supplier again can be a list because instead of manually every time input the name of the supplier I can use the supplier she to help me with that so we can do the same thing that I did to the products and by the way here in the products I want to increase this list I want to make this data set larger so what I'm going to do here is I'm going to pause the video and I want to and I'm going to input new informations right here okay however be free to use the informations that you need that you actually need to use as the products this is just a example right okay I'm done with the new data and as I said before doesn't matter what type of data set you are using okay because it's just a example you can use your own data with your own product now let me go back to the suppliers and I want to decrease the width of the first col to two Okay and now I want to start with the B6 but why is that why should I start with the six row instead of the third one because I want to do some different calculations right here I want to create a different analysis and that going to help us see how much I purchase for each one of the suppliers that I have so let's get started with the six row and with the headers so date and the next column can be the supplier name and then the maybe the email address a phone number also a address and maybe we can have here now the calculations quantity purchased and also the total purchase okay let me increase the width of those L CH and maybe we can increase the width of those three right here too the con see a lot and the column b a little okay as the date I can start with January 1st of 2025 just an example and as the suppliers names maybe I can have General Store of course this is just a sample but you can use the actual name that you going to have uh El electronics and then we can also have warehouse and let's say super market it as the email I want to just use here some Rando address or some Rando 123 at okay as the phone open parenthesis 0 00 0 hyphen 0 0 okay I think it's okay as the address lunch tree grove Grove is stream let me bring it down okay let me increase the width and as the quantity purchase in the total purchase we can use the sun IF function however we're going to do it later but anyway let me go here to the cells above and we can create already the the ranges or the thresholds the start and the ending point because let's see if we add up what is the quantity purchase in the total purchase using the sun function we're going to have as result the total the ground total you are using this spry let's let's say 3 years from now so you're going to have a lot of data you can't summarize or you can't adap everything so what you can do is you can use a threshold a start and ending point to make sure you can add up the values based on those criteria and the start Point can be months weeks days and so forth or a whole year let's say start with January 1st of 2025 and go to the last day of January January 31st of 2025 okay but we're going to finish those those calculations here later uh anyway let me select here everything and then I can go to uh the Home tab and Ally everyone everything horizontally and vertically click insert table my table has headers okay and in the table design I can change the layout to a different one maybe yellowish okay let me select the supplier's name and Alli to the left okay now we can use the same method as before to create a list so to a blank cell equal sign select the supplier's name let me select everything don't need to select the equal sign right click Cod now press es go to the purchases and oh we have a problem here oh okay I already know why I have this problem because keyboard does not exist in the list anymore because I change my list so let me take here a different product um I can use this one right here wireless keyboard okay let me increase the width yeah now let me go here to the supplier I want to create a list so again a data validation here is needed let me go to data and data validation I want to allow a list and as the list as the source equal sign inir open parenthesis open quotations right click paste close quotations close prenis hit enter we done if I click in the supplier I can choose all those different names that we that we didn't use in our list okay now it's working the freight the unit cost and the total cost is going to be a currency come time and click here into this dollar sign now let me increase the width of those two columns right here and let's say if you have a let's start with the total cost your previous total cost was $997 however you have this Freight right here so you need to addapt the freight to the total cost and that way you're going to have the final or the grand total cost equal sign the Freight add to your previous total cost and then enter and as the unit cost you're going to have equal sign total cost the final one divides by the quantity and then enter so instead of using 9.97 as the unit cost now you have 14 and 97 uh two different numbers right so I think it's important to have this criteria right here and of course if you want to include more colums to add up into the total cost maybe some fees maybe some taxes you can do so however I'm only I just going to keep up here with the rate right and as the status let me increase those width and we also can have here a list in Excel and this status is going to base on the delivery date we can have as the status overdue delivery or ontime delivery or receive it or also a pending purchase so let's go directly to data data validation and we can create all the status or all the options that we need allow any value you need to change through allow a list and as the source I can use over overdue deliver dely comma the second option can be ontime delivery the third one can be receive it the fourth one pending purchase okay let me hit enter now I can see all the different status that can occur here but let me before we move on to other roles to see the status working let's select everything that we have here because we done with the first row and I want to Ally vertically and horizontally and the product I want to Ally to the left and also the supplier allly to the left okay now I can select again everything and go to insert table and then I can click okay my table has headers okay and change the design to this one right here okay why is important to use a table in Excel in this case right here because we have some formulas like this one right here like this one right here we also have some lists in Excel and all those calculations and automations are going to be cop and paste down every time we input a new row so let's try let me inut here January 2nd of 2025 enter as we can clearly see if I select here a product I already have the list waiting for me and if I choose Cho the any item in the list the ID is going to be retrieved automatically and all the other formulas is going to work the status and so forth now let me put two more one two okay let me go here to a status overdue delivery on time we can also have receive it and pending purchase depends on the delivery date right so what we can do here is to improve the visual is highlight each one of those cells differently so let me select everything that I have in this First Column and then go to Home tab conditional formatting highlight celles rules and equal to every time I have a reed item I want to highlight with a greenish color okay but every time I have a cell that is equal to overdo delivery I want to highlight with this redwish color and every time I have a cell that is equal to on time delivery I want to have a yellowish highlight and the last one is pending purchase a item that I didn't purchase yet so equal to pending purchase I can use here pending purchase let me change it right here this one maybe I can use let's say a different color such as custom format and as the font I can use a blue color and as the f I can use or I can use a blue VI as the background and as the color I can use a orange one okay but I don't know why this isn't working okay because I Mis expending the status now I understand but let's click okay let me make sure my status is right so let me go again to the data to edit this item right here data validation and as we can see the last one is not correct purchase okay now let's click okay and yeah we done let me change yeah now we have all the three different status that we need overd on time receip and pending purchase and whenever we change the status the color is going to automatically change for us so that's it basically the purchases are is already working now we can move on to products suppliers and get done with this calculation right here let me select everything and change the design to a Ally everything into vertically and horizontally and also click on borders all borders put everything in bold change the background color to a gray one okay now let's say I want to add up the values first of all quantity purchase for each one of the suppliers so how can we do it using the Su IF function let's say we have all those four different suppliers and the Su function can help us with a situation that looks like this let's say we have different items or different suppliers and each one of those items and each one of those suppliers has a different quantity like I'm doing right here if I want to add up only the values that corresponds to grae and I use the equal sign sum function double click one two and select the range where I want to add up the values and hit enter I'm going to have as result the total sum of this range however I just want to add up the values that correspondence to grade how can I do it equal sign some ifs function double click one two and then the sum range is going to be the values comma the criteria is where I have the items name or the supplier's name comma and my criteria is what I want to add up grape and then enter okay 10 is the result let's see if it's correct so grade is equal to 10 yeah it's correct and if I change to another item such as banana I'm going to have for as result 10 + 10 is 20 + 20 is 40 yes it's correct so this is how we can use the sum ifs function to ad up values in Excel based on a criteria here we going to have three different criterias the supplier name and two different thresholds the start date and the ending date so equal sign sum ifs function double click one two the sum range that I want to use is just here in the purchases and be mindful use the footer to navigate through the sprad sheet right so purchase um the sum range is going to be the quantity let me select everything of course I have some blank cells but doesn't matter it's going to work I can fill in this data later on uh comma Now what we need to do is uh select the crater range that is going to be the supplier name trauma now let's go back here using the the tabs the go back to the suppliers and select the first supplier that I have okay now let's hit enter I'm going to have as result the total quantity purchase for this specific uh supplier now let's go back here to the purchase and let me finish to fill in the data that I have okay let's say 20 15 five gaming mouse can be $10 this one can be 20 and this one can be500 this one can be again General Store electronics store and super market rate zero 100 and 20 okay now if we go back here to the suppliers using the buttons of course because it's easier we can see that the content purchase is going to be automatically upate for us however let's say I also need to use the dates as criteria because I don't want to know the total quantity purchase for each one of the suppliers I only want to know using this range right here January let's say from January 1st up to the last day of January month January 31st of 2025 because in many situations we can have something that looks like this uh let's say we have the same supplier just to make sure it's working Electronics or Warehouse let's use warehouse for all the roles and then I have one item in that I purchased in January 1st January 2nd and maybe I can have it here February 1st of 2025 and January or we can go to yeah we can go to February again okay the same date so two in the January two in the February I don't want to know everything I don't want to add up all the values but I just want to keep up with this specific date so fifth is the result if we consider all the dates as we can clearly see right here 10 with 20 is 30 with 20 is 50 right so 50 is the total result but again I want to separate into different ranges based on the dates so how can we do it let me double click again in the first cell one two and I want to get rid of the last renes trauma now we can continue to input some criteria such as let's go to the criteria range into the purchases and it's going to be the date comma and my criteria is going to be the data that I have but before I select the date is very important as we can notice this supplier's name that stands for the name of the ship for some reason automatically appear here within the function so get rid of this name and now we only have the comma that we did input before so comma and open quotations I want to add up everything that is greater than or equal to close quotations greater than or equal to to Upper s aerc my starting point and it's very important right here to avoid this reference be moved down with because whenever we click hold and drag down a formula in Excel to all the rows that we have the references are going to follow this drag down and also be moved down so I don't want to allow this to happen I want to the start date always be in this specific cell so let me press F4 to input the dollar sign before the column and before the number to lock this reference let me hit enter okay nothing changed right here because all the data set that I have in the purchases list are starting in this date right here okay but if I change this date to January 15 of 2025 I'm going to hit enter and look what's going to happen with that results enter okay instead of 15 now we have 20 because it's working now let's do another another change right here let me go back to January 1st enter and I want to add add the second threshold double click one two get rid of the parenthesis comma again go to the purchases and select the date as the range comma and our criteria is going to be in the supplier get rid of this name right here supplier stick with just the comma and I want to add up now using the ending point so open quotations everything that is less than or equal to close quotations upper sand my ending point f for key to lock the cell enter now we done this is how we can do it and 30 is the result uh that I purchase the quantity purchase in January but if I change the range using the entire year so December 31st of 2025 enter I'm going to have the total result and to do the same thing to the total total purchase is much easier because the only thing that is going to change is this reference right here instead of using the quantity to add up those values we can use either this total cost or the total cost that we did after right so we just need to change change one specific criteria quantity to total cost but again you can use this total cost right here or this total cost right here because the the second one is using the freight into the calculation right but let's go back here to the supplier and let me double click here in the cell one two select everything that we did including the equal sign right click Cope Let let me press ask go to the cell to the right one two right click and then paste okay however before I press enter instead of using Table Three the quantity let me select quantity and I want to get rid of I want to use here the total total cost here the second option double click one two okay hit enter yeah that's it and because we are talking about here the total purchase we can change the format to a currency one Home tab dollar sign and that's it so we done this is how we can do it and some last steps that are maybe is important to learn is how can we use the filters that we have into the tables to help us better analyze the data and also how can I input a new data and also how can I get rid of a data that I not going to use anymore so let's get started with the filters let's say you want to see all the costs that are greater than a $200 you can click in this little square right here if you don't have this filter you can go here to the table design and the filter button you can activate or deactivate through this check boox and that way you can see that the filter tool is going to appear uh in the headers for you click here and that way you can choose only the informations that you want to see and if you click okay the data set is going to be automatically filtered for you in order to show just what you need and you can apply these filters and you can also clear the filters just click in the filter and then go to clear filter from total cost you can do the same process to all the different columns let's say sometime you want to see the status so you can click in the status and filter just the ones that you want to see you can do the same thing also to the products the dates the delivery dates suppliers and so forth and to add a new item you can go immediately to the first blank row and start with in the date and then if you hit enter a new row is going to appear with all the informations all the calculations that we have in the previous rows such as the lists such as the vup function and so forth and if you want to get rid of a row you can select the specific row right click and then go to delete and you can apply the same process to all the different sheets that we have okay purchases products and also suppliers I hope you guys enjoy this video on how to create a purchasing control in Excel where we can control everything that we purchase everything that we have as the products and also as the suppliers and also create here this calculations to help us analyze what is is happening into our purchases if you have any questions or any suggestions to the next videos let me know comment down below and I see you tomorrow cuz every day has a new video