Track product inventory - Microsoft Support (2024)

Before you start to create your own inventory in Excel, it's a great idea to check out some of the existing templates that you can download and use. These templates can be changed to work however you need.

1. Open Excel or if Excel is already open, select File > New.

2. In the search box, type "inventory" or something similar.

   See Also

   What is Stock Market Data? Explore Datasets & Data SourcesReal-Time Stock Market Data: Definition, Benefits, Types & Uses | IntrinioPosition Trading Strategy: How To Use ItCloud based evaluation of databases for stock market data

3. Browse the results. If you click on one of the options, you'll see a larger screenshot and a description of the template.

   

4. Once you've found the one you like, select Create.

5. The template will open in Excel. Start to use it as-is or make updates to the template for whatever you need.

Maybe the most important part of an inventory list to help you track everything is choosing the right column headers. It's impossible to know what your specific business needs are, but there are some common column headers that we recommend using. Here's a list with some descriptions of each:

• Inventory ID - use this to create a unique ID for your items.

• Item name - the name of the items. Inventory IDs help distinguish between similar items or replacement items when an item is discontinued and a new version replaces it.

• Item category - category for the item, such as "Accessory".

• Quantity in stock - quantity currently in stock for that specific item.

• Cost per item - this could be used as your business cost per item or it could be the current price of an item. You might want to add an additional column to track both if that's needed.

• Total inventory value - quantity in stock multiplied by the cost per item. It's useful to use a simple multiplication formula so that Excel automatically calculates this as your quantity changes or the cost per item changes.

• Reorder limit - use this to set a limit for when you should reorder an item before it becomes out of stock.

• Last Reorder date - keep track of when the last time you ordered an item to refill stock. It can be useful to know, especially if you're frequently reordering certain items, which can lead to you increase your Reorder limit.

• Discontinued - it can be helpful to track when you no longer carry an item in your inventory.

• Notes - add any extra information you want to keep track of or someone should know, like if it's challenging to reorder certain items from suppliers.

Excel is a powerful tool that can transform your work and save you time and money. But it also can be overwhelming to figure out all the features you might want to use. Here are a few suggestions that can be useful with links to articles explaining how you use the feature:

• Create a drop-down list in your categories column to limit the options someone can choose. This limits potential mistakes or typos.

• Use Excel as your calculator so things like inventory value and more are automatically calculated.

• Freeze panes to lock rows and columns so it's easier to see your column headers even when you're scrolling far down your inventory list.

• Use conditional formatting to highlight information, like when you need to reorder an item because it's below your reorder limit.

• Filter data in your inventory list to help you focus on specific categories or items.