Maximize Efficiency with Our Excel Pro Forma Template for Inventory

Excel pro forma template for Inventory

hi everyone I'm Sharon Smith and in this Excel tutorial I'm going to show you how to create a simple yet effective inventory list in Excel whether you need to manage inventory for your small business or you just want to organize your personal items I'm going to show you all of The Columns of data that you're going to want to capture and then I'm also going to show you how to set up some conditional formatting so that you can highlight things that you need to reorder and then also we'll take a quick look at pivot tables so that you can view and analyze your inventory data more easily I'm going to show you how to create this from scratch step by step in this video but if you are interested in purchasing the template that we use I'll include a link in the description below the video so that you can save time and get you jump started let's take a look okay first let's take a quick look at the inventory list that we're going to create so we're going to have a header up here that includes our total inventory value that's going to include the sum of our inventory value column over here and then we're going to have all of the different Columns of data that we want to include and then we also have a couple of special conditional formattings that are alerting us to let us know when we need to reorder items so for example we see that if our quantity in stock is less than our reorder level it's going to highlight that reorder level so for example if I come up here and put this below 10 and hit enter notice that our 10 highlights over here to alert us and let us know that we need to reorder that item then we also have another conditional formatting set up over here where we have a drop down for continued we have a yes or no drop down and we have a data validation table I'll show you how to set that up and so if we put yes here it will automatically gray out and strike through that item as a discontinued item then we can use our filters up at the top if you want to and you could exclude looking at the discontinued items on your list and so you're easily able to manage your inventory database then the other nice thing about the way that we have this formatted is if I come up to print preview I'm going to show you how to set up the page layout so that if you do need to print this list it'll show up all on one page and then we're also going to make sure that the rows up here will repeat on each page if the inventory list exceeds one page which it might if you have a large list of inventory so I'm going to show you how to set all of that up and then we're also going to take a quick look at how to insert pivot tables so we can look at our data so for example I have a secondary tab over here where we have our inventory list at multiple different locations and so we've got a way that we can set up our pivot tables to filter by location so maybe different shop location if we come up here we can toggle to our different shops and select that and click OK and then it'll will show us what inventory items in the quantity that we have at those locations and then we also have a pivot table for the different items if we want to filter by item list we can do that we can select an item and click OK and then we can see how much we have at each location so I'm going to show you how to set all that up first let's go ahead and create a new blank sheet I'm going to come up to the top and just click new okay so on our new blank document first thing we're going to do is create our header so I'm going to expand the first row here and just type inventory list I'm going to go ahead and make that bigger so that we can see it and then we'll finish formatting that in a little bit once we get all of our headers on to the spreadsheet I'm going to actually start my headers on row three just so I can use Row 2 as kind of a buffer and so what we'll do is start with all of our items that we want to include in our inventory database so I'm going to start with our inventory ID our item category so this is optional whether you have categories or not you can decide whether you want to include that but I'm going to include an item category the next is the item name and an item description then we want to have a column for our quantity in stock then the price and then the total inventory value now on the template we have a column for SKU but you may not use that so you don't have to include any columns that you don't actually need to capture so the next one I'm going to do is just a location and supplier and then I'm going to include a reorder level so the limit that you want to make sure you don't if you go below that limit you're going to want to reorder your items at that point so we'll call this the reorder limit and then we can include a column for the date that we reordered those items and we can include a column if we've discontinued an item and then an optional column for any notes that we might want to include on our database now that we have all of our Columns of data I'm just going to take a few minutes to format the size the width of the columns so that everything will fit onto one page okay now that I have my column widths I am going to select my row and I'm going to Center everything and then for those that I need to wrap text I'm going to go ahead and select those and click on wrap text all right now I'm going to select all of my headers and I'm going to come up to the alignment on the Home tab and Center those in the middle of the cell and then I can actually bold those I can change the fill color so if I want to make this a dark blue and then I can make the font a light color so I can pick White and it's starting to look more like our database template that we want to create I'm going to go ahead and use this second row here as just a buffer for our header so I'm going to go ahead and choose a little bit lighter color to go on that and we're just going to squeeze the height down just a little bit and now let's go ahead and Center our inventory list we can select a couple of cells here and then we can say merge and center and that will leave the inventory list up here at the top and we can select this header area and update the fill color and there we have our inventory list set up so now what we want to do is start filling in a little bit of our information so I'm going to put in some dummy data for you guys and then you can actually use this to start filling in your data then the next thing we're going to do is show you how to set up the data validation and also for the drop down list for this discontinued and then we're also going to look at conditional formatting all right so let me just fill this in with a little bit of information so we can move forward with the rest of the tutorial all right now that we have a little bit of data filled in on our inventory list let's take a look at some of the formatting that we need to do so first we want to format the column with the price as currency so I'm going to select that column and on your Home tab up here just come up to number and select the dollar sign for currency that will format that as currency to two decimals and then we also want the total inventory value to be formatted as a dollar as well in currency so we're going to select that and then we're all we're going to want to put our formula in here so that this will automatically calculate based on our quantity in stock so we have a set price times the quantity that's going to be our total value so all we have to do is say equals and then we're going to say Quantity times price and hit enter and then all we have to do is Select that and then double click to copy the formula down and that will give us our total inventory value now in the header we wanted to have the sum of our total inventory at the top so it would be easy to see and so we can do that by just coming up here in our header area and just we can put total inventory value and we can wrap that text and we can Center it and we can make it bold and maybe we want to right justify it and then in the next column all we have to do is say equals sum open parentheses and then select all of our items over here and then close parentheses and hit enter and that's going to give us a total inventory value now the next thing we want to do is set up a quick data validation of a yes no drop down in this column over here for discontinued so to do that we have to have yes no as a list item and so I'm going to create a new tab down here at the bottom we can hide this tab later but I'm just going to Simply type yes and then no these are my choices for my drop down list on the other sheet I'm going to go back to sheet 1 and then over here in the discontinued column I'm just going to select that cell and then come up to your data tab and then under here under data tools there's a drop down click on that and select data validation and under this here we're going to select the drop down and select list and now for the source of the list we'll place our cursor here and then come over to sheet2 and select our drop down list items and then click ok so now over here we have an automatic drop down list for yes or no and we can simply select that Excel hit Ctrl C to copy and then paste that down and hit enter and so now each cell has the drop down list for yes or no and if we want to we can hide that second sheet so that it's not visible we can go over select the sheet and right click on it and select hide it's a little bit easier to manage when we don't have as many sheets open and if you ever do need to update that or if you want to put another data validation table in your spreadsheet and you want to unhide it and take a look at it all you have to do is come down to your sheet one right click click on unhide select that sheet 2 and click OK and it will unhide that sheet so that you can edit it and do whatever you need to do so I'm going to go ahead and right click and hide it back so we just have our first sheet that we're working on all right now let's set up our conditional formatting with some rules that will highlight the items that we need to reorder so we'll do that first so what we want to do is highlight any items that we have a quantity in stock that is less than the reorder limit so what we want to do is Select our entire inventory list over here and this is the the table of data that we're going to to take a look at now let's go to the Home tab and click on conditional formatting select new rule and the last option down here which is use a formula to determine which cells to format we're going to type our formula in here and what we want to say is this column e so E4 which is the location where we have our quantity in stock so we're going to say equals E4 if that is less than what we have in j4 for the reorder limit then we're going to want to highlight that so we're going to say j4 and we're going to click on the format and select our fill Tab and just select a color and then click ok now we're going to click ok now this will highlight the inventory number over here and if we want it to just highlight the reorder limit all we have to do is come back up to conditional formatting go to manage rules and we're going to apply this to the column J so I'm going to just replace a with j and click apply and we're going to fix our formula over here and click ok we'll click apply and then click ok all right so now we have our highlights alerting us of the items that we need to reorder because our quantity in stock is less than our reorder limit now the next thing we want to do is conditionally format any items that we've discontinued to be highlighted in Gray and have a strikethrough so let's go ahead and fill in yes or no if an item is discontinued we'll start and fill all them in as no first and then we'll copy that down hit enter and then let's go back up to or first let's go ahead and select our entire list again and then come back up to conditional formatting and we're going to set up a new rule and also this is going to use the formula and this time what we're going to say is equals and then if we want column l so if we want to say l and we'll say L4 equals quotes yes then what we want to do is format that as a strikethrough so we want that to be grayed out and under the font area select strike through and click ok and if we want we'll click OK here now we want that to apply to the whole row so let's go up to conditional formatting we'll click on manage rules and let's look and we see we see that it is going to apply to A4 through M9 which is good let's go ahead and double click on our formula here and add a dollar sign right before the L and then click ok and click apply and then click ok now let's change one of our items here to yes and we should see that the whole row gets grayed out and has a strikethrough so that is how you would set up your conditional formatting to alert you on various things on your spreadsheet for your inventory database now let's take a look at how to quickly insert a couple of pivot tables so that we can filter and analyze our data and see it in different views so that it's easier to work with one of the things that we definitely want to do on our inventory list if you're creating this from scratch is go ahead and select the first cell of your list here that you've created and then hit control T because we want to turn this into a table we're going to create a table and it's going to say yes my table has headers so It'll recognize that first row row 3 as the headers then we're going to click ok so sometimes when you create a table there's this automatic formatting that occurs on your table and all you have to do is come up to your table design up here at the top that appears once you've created a table and then you can go up and select no formatting and so that way your conditional formatting will stay the same now the reason you want to convert this to a table is so that you can easily add to it so if I start typing my next item and hit tab or enter this automatically adds the item to my table it will automatically have the value included in the sum up here and it will automatically apply the conditional formatting that we have set up here so that's why we want to create this as a table once we get those things set up then you can easily add to your database you can manage it if you delete rows all of your items that you've set up here for formatting will automatically apply to your table so the next thing we want to do is just select inside of our table and then we want to click insert and we're going to click pivot table we're going to say new worksheet and click ok and over here we can select different things so let's say on this one that we want to be able to filter by location so let's include our inventory ID as a row and let's include our quantity in stock and then what we want to do is have our location as a filter we select that it might go down to rows all we have to do is click and drag that up to filters up at the top so now we have a filter up here at the top where we can say shop one or shop two and select okay and this will show us what inventory we have at shop one and then if we change that to shop two we can select that click ok and it will update and show us what we have in shop 2. let's go back to our inventory database now let's say that we want to look at this and see by item where do we have our different items we can do that as well so if we want to we can go ahead and insert another pivot table click on insert pivot table and we're going to have a new worksheet and click ok and this time we want to have inventory ID maybe we want to see the quantity in stock and we want to see the location and but this time what we want to do is filter by the inventory ID so then all we have to do is move that up here to our filters and then this way we can select our different inventory items click OK and then we can see where they're located so if we have different inventory items we can just filter and look at that and have a quick view of just that item and where it's located in our quantity in stock all right now let's go back to our main database over here and let's go ahead and set this up so that it's a correct page layout in case you need to print this so what we want to do is come up here to our page layout tab we're going to click on orientation and I know since I have quite a few columns I want this to be landscape so I'm going to click that and then under margins I'm going to go ahead and click on custom margins and I'm going to click on the page tab and I'm going to say fit to one page wide and then I'm going to do multiple Pages tall just in case we have a long list of of inventory then what I want to do is come and click on the sheet Tab and at each page if if my data flows onto multiple pages I want to have rows one two and three as my header on top of each printed page so to do that I'm going to just come up here to rows to repeat at the top and I'm going to type dollar sign one colon dollar sign three and that will capture rows one through three as the header on each page and then I'm going to click on print preview and then we'll see that all of our columns are showing up on one page and if it did flow onto a second page our first three rows will repeat at the top so I'm going to go back and now we have our inventory list set up as a table that you can add new items to you can delete or change or update it's very important that since this is in Excel you do have to keep your spreadsheet manually updated it's important to do that as soon as you get anything new in stock comment update your inventory list and as soon as you sell things come and update your inventory list so that you can keep things accurate and up to date so as often as you need to do that go ahead and do that and then when you do change the data so if something does change if you had an order come in maybe we could have a hundred in stock now we hit enter we no longer have to reorder that it's not highlighted anymore and we can come back over to our pivot tables and so if we have our um let's say we have our pivot table where we were looking at inventory one and click OK what we want to all always make sure that we do is come over to our data tab and we want to click refresh so we want to refresh our data and it will refresh automatically with anything that you have updated on your master sheet so those are just some quick tips and things that you're going to want to keep in mind if you're using the pivot tables and if you're using the template that we set up in this video if you found this video helpful be sure and give it a thumbs up to like it and share it with your friends you can subscribe to my channel and click the Bell to receive a notification every time I post a new video be sure and visit my website sharonsmithhr.com thank you so much for watching and I'll see you next time