This guide will explain how you can create a table to keep track of inventory in Excel.
Tracking inventory in Excel is an inexpensive alternative to using professional inventory management software. You also have the added advantage of sharing the document with your team.
Let’s take a look at an example where you might need to keep track of inventory.
Suppose you have a small business selling arts and crafts supplies. A common issue that occurs during the business is encountering a shortage in supply of a particular item.
As a business owner, you also want to know which items sell the least and sell the most. Knowing this beforehand can help you prepare how many units of each item to stock for the week.
With Excel, you can easily create your own inventory tracker to monitor your stock. Being able to plan ahead of shortages can help increase profits and help plan out future purchases.
This use case is just one way we use an inventory tracker in Excel. Now that we know when to use Excel for tracking your business’s inventory let’s dive into how to use it and work on an actual sample spreadsheet.
A Real Example of an Excel Sheet that Keeps Track of Inventory
Let’s take a look at a real example of an Excel spreadsheet being used to keep track of inventory.
In the spreadsheet below, we can use a table to keep track of several items and their current quantity.
To get the values in Column E, we just need to use the following formula:
=C2 * D2
Since the table is not directly tied to our list of historical and upcoming orders, it must still be manually edited. We can improve on this sheet later on by adding more advanced formulas to keep track of stock.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try filling in your own inventory tracker in Excel, head over to the next section and follow our step-by-step guide.
How to Keep Track of Inventory in Excel
This section will guide you through each step needed to start keeping track of inventory in Excel. You’ll also learn how to add formatting for currency, filters, and sorting to your inventory tracker.
Follow these steps to start using an inventory in Excel:
- First, place the headers for the main inventory table. We’ll be keeping track of the item number, item name, cost, quantity, and net value. Users can add more fields as needed. For example, we can add another column for product categories, descriptions, or stock location.
- Next, you must fill in the details for each item in your inventory. For this example, we have an inventory with items A to T.
- Next, fill in the item cost and quantity for each item. The cost refers to the unit cost of the specified item. The quantity field will indicate how many units of that item is in stock.
- To get the Net Value, simply multiply the item unit cost by the quantity available in the inventory.
- Hit the Enter key to return the actual net value of the item.
- Drag down the formula to fill in the net value for all other items in your inventory.
- We can add additional formatting to fields that include currency, such as columns C and E. To apply currency formatting, first select these columns. On the Home tab, click on the Currency icon found in the Number group.
- We can also add filters to quickly sort and filter out the types of items we want to look at. To add a filter to the table, first, select the entire table, then click on the Filter icon on the Data tab.
- Each header should now have a dropdown menu showing various sorting and filtering options.
- In this example, we’ve sorted each item in ascending order by Net Value.
Frequently Asked Questions (FAQ)
- What are the drawbacks of using Excel to track inventory?
While Excel is a powerful tool that can keep track of large amounts of data, it can be difficult to update an Excel tracker as your business scales constantly. Excel lacks any real-time reporting and automation features available in professional inventory management software.
Also, since the data is entered manually, there is a higher chance of errors occurring during manual input.
That’s all you need to remember to start using a spreadsheet to keep track of inventory in Excel. This step-by-step guide shows how easy it is to determine the net value of each item in your inventory. We’ve also shown how users can sort and filter through the inventory by a specific field.
You can now use the inventory sheet provided in Excel together with the various other Excel formulas available to create great worksheets that work for you.
Are you interested in learning more about what Excel can do? Stay notified of new guides like this by subscribing to our newsletter!
