How to Create an Inventory List in Microsoft Excel: Step-by-Step Guide

PDF download Download Article

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

PDF download Download Article

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.

Best Ways to Make an Inventory List in Excel

  • 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.
Method 1
Method 1 of 2:

Using a Template

PDF download Download Article
  1. It's an option in the left menu. There are plenty of great template options for keeping inventory and controlling inventory for your business. [1]
    • On Mac, first click File in the upper-left corner, then click New from Template… in the drop-down menu.
  2. It's at the top of the Excel New tab, under the Blank workbook button. It will say "Search for online templates." [2]
    Advertisement
  3. To do so:
    • Type inventory into the search bar at the top of the page.
    • Press ↵ Enter . This will bring up a list of templates for inventory management.
  4. Click the inventory template that best suits your needs. Its preview page will open. [3]
    • Each inventory list template offers different features. If you don't like the template you've selected, press Esc to return to the templates page.
  5. It's to the right of the template's preview window. [4]
  6. This may take a few seconds. Once the template is open, you can proceed.
  7. 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. 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. To do so: [5]
    • 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 .
    10. Advertisement
Method 2
Method 2 of 2:

Creating from Scratch

PDF download Download Article
  1. 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! [6]
  2. In the following cells, enter the following headers:
    • A1 - Item Number
    • B1 - Item Name
    • C1 - Item Cost
    • D1 - Number of Items
    • E1 - Net Value
    • F1 - Image (optional)
  3. 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. To do so:
    • Click cell A2 .
    • Type in your item's inventory number (e.g, 123456 ).
    • Press ↵ Enter .
  5. Click cell B2 , and then enter the item's official name (e.g., Cable ties ).
  6. Click cell C2 , then enter the item's individual cost (e.g., 4.99 ).
  7. 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 80 ).
  8. To do so:
    • Click cell E2 .
    • Type =C2*D2 into the cell.
    • Press ↵ Enter . 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. 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. 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. To do so: [7]
    • 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 .
    12. Advertisement

Expert Q&A

Search
Add New Question
  • Question
    How do i make labels of inventory items?
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
    Kyle Smith
    wikiHow Technology Writer
    Expert Answer
    To make labels for your items, you'll need to either contact a 3rd-party labeling company or make your labels in-house. A 3rd-party labeling company is a good option if you have medium to high product volume and require professional labels. Making the labels manually is good for low volume production.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 1 Helpful 3
  • Question
    which will be the formula to know quantity limit to reorder and restock?
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
    Kyle Smith
    wikiHow Technology Writer
    Expert Answer
    You can use the IF function in a new column to see when you need to reorder products. For example, IF([quantity] < [stock limit], "reorder", "don't reorder yet").
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 3 Helpful 13
  • Question
    Can I add a photo, video and map for each item?
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
    Kyle Smith
    wikiHow Technology Writer
    Expert Answer
    You can add a photo and map using Insert > Illustration > Pictures > From Device. To add a video, you can add a link to an external video for reference.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 1 Helpful 5
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Move Columns in Excel: Rearrange with 2 Easy Methods
      How to
      Create a Graph in Excel
      How to
      Prepare Amortization Schedule in Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Link Sheets in Excel
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/insert-or-delete-a-worksheet-19d3d21e-a3b3-4e13-a422-d1f43f1faaf2
      2. ↑ https://support.microsoft.com/en-us/office/video-start-with-an-accessible-excel-template-918db751-1585-41c5-9ddf-5d7a0915afaf
      3. ↑ https://create.microsoft.com/en-us/templates/inventories
      4. ↑ https://create.microsoft.com/en-us/templates/inventories
      5. ↑ https://support.microsoft.com/en-us/office/save-your-workbook-92e4aae0-452d-497f-a470-570610ff720a
      6. ↑ https://support.microsoft.com/en-us/office/create-a-new-workbook-ae99f19b-cecb-4aa0-92c8-7126d6212a83
      7. ↑ https://support.microsoft.com/en-us/office/save-your-workbook-92e4aae0-452d-497f-a470-570610ff720a

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 923,656 times.

      Reader Success Stories

      • Rovelyn Pating

        May 25, 2017

        "Guides me to know the basic steps in creating inventory system using Excel."
        Rated this article:
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement