Excel is a spreadsheet program offered by Microsoft and comes standard in every MS Office package. This data capturing software is fairly easy to use and usually made for keeping track of inventory, assigning values to items in invoices, and creating financial documents like income and expenditure spreadsheets. The main feature of Excel is the ability to automate it, meaning that once you have created your Excel spreadsheet, all you need to do is fill in the new details and the prices or totals will be added up thanks to the specific formula you have inserted.
With an Excel inventory template, the major benefit is being able to categorize everything into a working format that is easy to update. This is perfect for recording home contents inventories, stock inventories for retail stores, kitchen supplies and ingredients for restaurants, and many other things. So, how do you make this template? Here are the steps you need to follow:
How to Create an Excel Inventory Template
Step 1: The first thing you need to do is open your Excel document. Click “File” and then click “New” for it to open a window that gives you options of various pre-made templates for you to use. If you click on “installed templates” you will see previews for billing statements, sales reports, timecards, monthly budget and more. There is also an option to visit Microsoft Online to get other templates like forms, invoices, receipts, purchase orders, calendars and of course inventories. You can choose any of the inventories that are shown here to download, but it is actually very simple to learn how to create Excel inventory template documents which will then be exactly what you need. Another solution if you want something more complicated and professional is to download something like the Abacus Excel inventory template and tracker which is free.
Step 2: To create your own template, what you first need to do is select the “black workbook” button in the “File” “New” menu. This will give you a blank new grid that you will now fill in. Start by creating a title or heading for your spreadsheet. You can do this easily by clicking on “Insert” and then “Header and Footer”. This will centralize the header and let you fill in anything you like, such as Household Contents. You can also fill in the Footer, but this is not necessary, unless you have a long inventory and wish to include page numbers, which you will find under the “Header & Footer Design Tools” tab. You can create any kind of page numbering system you like, and can also include the time and date. Of course, for company inventories it looks more professional to include your logo at the top of the spreadsheet, so to do this, click “picture” in the Header & Footer Design Tools tab. Select your logo from your files and it will be inserted for you. Once you have completed editing the Header and Footer, you will move on to filling out the rest of the spreadsheet.
Step 3: You will probably want to start by creating your row and column headings. So, along the top in A row, fill in name types for each of your columns. For instance, you can make A1 Room, you can make A2, Type, and A3 can be Number. This would be sufficient for a Home Contents Inventory, however for a retail store you might want to have something a bit more comprehensive such as A1 (Description), A2 (Model No.), A3 (Serial No.), A4 (No. In Stock), A5 (No. Sold), A6 (Price). Remember that when you assign these columns, you will need to adjust the width of the columns so that you have enough space for certain items, like Description, and narrower columns for things like the Number of Items in Stock. Once you have done this, it is a good idea to block off this row and perhaps bold the names so that they stand out a bit. You can highlight the entire row 1, and change the font, the color, and the size. If you go to the little grid icon just underneath the font size and type sections, you will see that many different border options come up when you click it. For these main names, you could choose the grid that indicates “all borders” and then also click the “thick box border” which will outline it.
Step 4: Now that you have your Main Row sorted out, you can go about creating you’re a column. Sometimes you don’t need this, but for a Home Inventory, it makes it neater to organize your inventory by room, so in your A column, you will write each room in your home leaving a few rows between each one, so start with your Lounge, Dining Room, Kitchen, Main Bedroom, Main Bathroom, Second Bedroom, Study, Garage, etc. Once you have done this, you can format it the same was as your main row, by changing the font size and type and also creating your borders the way you like them, so that when the spreadsheet is printed out, everything is separated nicely and neatly.
Step 5: Now that you have made the Excel spreadsheet inventory template, you can go ahead and save them to your computer. Name it something simple like Blank Inventory, and then you will know that this is the empty document that you will use to create all your inventories from. When you want to create your first inventory, all you need to do is open this document.
Step 6: So, now that your Excel inventory template is saved, you can make your first inventory, let’s say for your March stock. Open the template and click in the top header where you have your title, so you can edit this to say March next to the title. You can then also edit the Footer so that you have the correct date and time. The next part is the most important! Click on “Save As” and rename your document “Inventory March 2013” or something similar, so that you do not save over the blank template. If you just click “Save” or “CTRL S” you will override your blank template with the newly edited inventory.
Step 7: Now that you have your “March” Excel for inventory management ready, you can start filling in all the fields with your stock or contents.
Final Tip: If you have created a main A column, with the names of rooms in your home for instance, and you run out of space in the “Lounge” section, all you have to do to add in more rows is to right-click on row number just before the next room and go down to “insert”. This will insert a whole new row for you above that number so you can keep adding in more until you have completed the inventory for that particular room.