How to Create an Inventory List in Excel

A tutorial for creating an inventory list from scratch or a template in Microsoft Excel

Trying to keep track of your inventory using Excel? There are a couple easy ways to go about this task — you can use a pre-formatted inventory list template to do this, or you can manually create your sheet. This wikiHow guide shows you how to manage your business' inventory using an Excel spreadsheet on a Windows or Mac computer.

[Edit]Things You Should Know

• To use a template, click the "New" tab in Excel and search for "inventory" in the search bar.
• To create your own template, start a new spreadsheet and add column headers.
• Columns can include the SKU, item name, quantity, and unit price for items.

[Edit]Steps

[Edit]Using a Template

1. Click the tab. It's an option in the left menu. There are plenty of great template options for keeping inventory and controlling inventory for your business.
   
   • On Mac, first click File in the upper-left corner, then click New from Template… in the drop-down menu.
2. Click the search bar. It's at the top of the Excel New tab, under the Blank workbook button. It will say "Search for online templates."
   
3. Search for inventory list templates. To do so:
   
   • Type into the search bar at the top of the page.
   • Press . This will bring up a list of templates for inventory management.
4. Select a template. Click the inventory template that best suits your needs. Its preview page will open.
   
   • Each inventory list template offers different features. If you don't like the template you've selected, press to return to the templates page.
5. Click . It's to the right of the template's preview window.
   
6. Wait for your template to load. This may take a few seconds. Once the template is open, you can proceed.
   
7. Enter your inventory information. To change a pre-filled cell, double-click it, delete the number or word there, and enter your item's information. While your selected template may have slightly different options, every inventory list should include the following options:
   
   • Item Number - An item's inventory (SKU) number.
   • Item Name - An item's descriptive name.
   • Item Cost - The cost of one item.
   • Number in Stock - The quantity of an item in your inventory.
   • Net Value - The total value of an item's stock.
8. Add an image for each item. You can add a picture of each inventory item. These images will go in a new "Images" column. To add images:
   
   • Go to the Insert tab and select Illustrations > Pictures > This Device.
   • Click the image file and then click Insert.
   • Right-click (Windows) or ctrl-click (Mac) the image and select Format Picture.
   • Click the Size & Properties tab button > click the Properties drop-down > select Move and size with cells.
   • Drag the size anchors on the image to adjust its size to what you need for your inventory.
   • Adjust the row and column width for your image column to fit the image.
9. Save your work. To do so:
   
   • Windows:
     • Click File and then click Save As.
     • Navigate to the location where you want to save the file.
     • Type a name for the document (e.g., "Inventory List") into the "File name" text box.
     • Click Save.
   • Mac:
     • Click File and then click Save As.
     • Type a name for the document (e.g., "Inventory List") into the "Save As" field.
     • Select a save location by clicking the Where box and clicking a folder.
     • Click Save.

[Edit]Creating from Scratch

1. Click . This box is in the upper-left side of the Home tab in Excel. If you're trying to maintain inventory accuracy, a custom-made spreadsheet can go a long way!
   
2. Create your inventory list headers. In the following cells, enter the following headers:
   
   • A1 -
   • B1 -
   • C1 -
   • D1 -
   • E1 -
   • F1 - (optional)
3. Adjust the column widths. Click the space between two column letters (e.g., A and B) at the top of the sheet, then drag the mouse to the right to widen the column.
   
4. Enter an item's inventory number. To do so:
   
   • Click cell A2.
   • Type in your item's inventory number (e.g, ).
   • Press .
5. Add an item's name. Click cell B2, and then enter the item's official name (e.g., ).
   
6. Enter the item's cost per unit. Click cell C2, then enter the item's individual cost (e.g., ).
   
7. Add the total number of that item that you have on-hand. Click cell D2, then enter the number of items that you have in stock (for example, if you have 80 cable ties on-hand, you'd enter ).
   
8. Enter the net value formula. To do so:
   
   • Click cell E2.
   • Type into the cell.
   • Press . You should immediately see the calculated net value appear in the cell.
   • You can repeat this general formula for every cell in the "Net Value" column—just make sure that you replace C2 and D2 with the correct cells (for example, if you're multiplying values in cells C10 and D10, you'd use those cells instead of C2 and D2).
9. Add the rest of your items to the inventory list. Repeat the above process for each item in your inventory. You'll assign one item to each row until your list is full. You're off to a great start developing an inventory system for your company.
   
10. Add an image for each item. You can add a picture of each inventory item. These images will go in a new "Images" column. To add images:
    
    • Go to the Insert tab and select Illustrations > Pictures > This Device.
    • Click the image file and then click Insert.
    • Right-click (Windows) or ctrl-click (Mac) the image and select Format Picture.
    • Click the Size & Properties tab button > click the Properties drop-down > select Move and size with cells.
    • Drag the size anchors on the image to adjust its size to what you need for your inventory.
    • Adjust the row and column width for your image column to fit the image.
11. Save your work. To do so:
    
    • Windows:
      • Click File and then click Save As.
      • Navigate to the location where you want to save the file.
      • Type a name for the document (e.g., "Inventory List") into the "File name" text box.
      • Click Save.
    • Mac:
      • Click File and then click Save As.
      • Type a name for the document (e.g., "Inventory List") into the "Save As" field.
      • Select a save location by clicking the Where box and clicking a folder.
      • Click Save.

[Edit]Tips

• You can add another sheet to your workbook by clicking ＋ in the lower-left side of the page.
• Next, check out how to easily calculate inventory turnover.

[Edit]Related wikiHows

• Move Columns in Excel
• Add a Filter in Excel 2007
• Create a Graph in Excel
• Prepare Amortization Schedule in Excel