How to Keep Track of Inventory in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

If you want to know how to keep track of inventory in Excel, this article is for you. Here, we will walk you through some easy steps to do the task effortlessly. You will get to know the basics of inventory tracking, learn the steps to track inventories of different items and find answers to some frequently asked questions about inventory tracking. So without any delay, let’s dive in.

This is an overview image of inventory tracking.

How to keep track of inventory in excel


What Is Inventory Tracking?

Inventory tracking involves monitoring and managing a company’s inventory levels, movements, and status. The primary purpose of inventory tracking is to ensure that the right products are available at the right time to meet customer demand while minimizing costs associated with overstocking and stockouts. The common parameters of inventory tracking are,

  • Opening Stock
  • New Purchases
  • Sales
  • And, Hand-in-stocks.

These parameters will be explained subsequently. To effectively track inventory using these parameters, businesses can use a perpetual inventory system. This system involves recording and updating inventory levels to ensure that businesses have accurate and up-to-date information about their inventory levels.


How to Keep Track of Inventory in Excel: With Easy Steps

In this section, I will explain how to keep track of inventory in Excel. I have divided the entire task into some easy steps for everyone’s convenience. This is the sample dataset.

Dataset for inventory tracker

There are multiple sheets (Item, Stock In, Stock Out) in this Excel file. I will extract the required values from respective sheets and track inventories in the “Tracker” sheet.


Step 1: Measure Opening Stock

Opening Stock refers to the quantity of a particular product a business has in stock at the beginning of an accounting period. It is the starting point for tracking inventory levels throughout the period.

  • This is a list of the items and their opening stocks.

Opening stock sheet

  • Select the entire table.
  • Then, give it a name. I am going to call it “Item”.

Naming opening stock table

  • Now, I will use the VLOOKUP function to get the opening stock in the “Tracker” sheet. The formula in D5 is,
=VLOOKUP(B5,Item,3,FALSE)
  • Use Fill Handle to AutoFill up to D12.

Transfer opening stock to inventory tracker

Here, the lookup_value is B5. Excel will look for B5 in the array “Item” and return the corresponding value from the 3rd column of the array. The FALSE argument corresponds to an EXACT Match.


Step 2: Calculate New Purchases

New purchase refers to the quantity of a particular product that a business acquires from suppliers during the accounting period.

  • This is the sheet for New Purchases. I am going to call the table “New_Purchase”.

Stock-in sheet

  • Then, write down the following formula in E5 of the “Tracker” sheet.
=VLOOKUP(B5,New_Purchase,3,FALSE)
  • AutoFill up to E12.

Transfer new purchases to inventory tracker

The lookup_value is B5. Excel will look for B5 in the array “New_Purchase” and return the corresponding value from the 3rd column of the array. The FALSE argument corresponds to an EXACT Match.


Step 3: Make Note of Sales

Next, you should record the number of sales for the accounting period. This refers to the quantity of a particular product a business sells to customers during the accounting period.

  • This is the table that contains the information on the sales. I am going to call it “Sales”.

Sales Sheet

  • Write down the following formula in F5 of the “Tracker” sheet >> AutoFill up to F12.
=VLOOKUP(B5,Sales,3,FALSE)

Transfer sales information to inventory tracker

The lookup_value is B5. Excel will look for B5 in the array “Sales” and return the corresponding value from the 3rd column of the array. The FALSE argument corresponds to an EXACT Match.


Step 4: Track Inventory for Different Items

Now, I will show how to track the inventory for different items. This is the quantity of a particular product that a business has in stock at the end of an accounting period.

It is calculated by adding the opening stock to the number of new purchases and subtracting the number of new sales. The resulting number represents the number of products that are available for sale at the end of the period.

  • To calculate it, go to G5 and write down the following formula.
=D5+E5-F5
  • Then, AutoFill up to G12.

Calculate hand-in stock


Step 5: Calculate Inventory Amount

Finally, I will calculate the inventory amount. To do so,

  • I need to list the cost per unit of each item.

Unit cost on inventory items

  • Then, I will simply multiply the values by the Hand-In-Stock value to get the amount. The formula in I5 will be
=G5*H5
  • Finally, I will AutoFill up to I12.

Calculation of inventory amount

Read More: How to Maintain Store Inventory in Excel


Frequently Asked Questions

1. What is Inventory?

Answer: Inventory refers to the stock of goods or materials that a business holds for the purpose of eventual resale or use in production. Inventory can include finished goods, work-in-progress (partially completed goods), and raw materials.

Inventory plays a critical role in the operation of businesses as it enables them to fulfill customer demand, avoid stockouts, and maintain a continuous flow of production. However, inventory also represents a significant investment of capital, and businesses must balance the cost of carrying inventory with the benefits of having it available.

2. What are the differences between the inventories of a manufacturing industry and a merchandising industry?

Answer: The key difference between inventory in manufacturing and merchandising industries is in the nature of their inventory.

In the manufacturing industry, inventory refers to the raw materials, work-in-progress (WIP), and finished goods that are used in the production process. Raw materials are the inputs that are used to create the final product, WIP refers to partially completed products that are still in the manufacturing process, and finished goods are the final products that are ready for sale.

In contrast, in the merchandising industry, inventory refers to the finished goods that are purchased from suppliers and resold to customers. Merchandising businesses do not engage in the production process, and their inventory is limited to the products they purchase and sell.


Download Practice Workbook

This is a free sample of how to keep track of inventory in Excel. You can download it from here.


Conclusion

In this article, I have explained how to keep track of Inventory in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.


Related Articles


<< Go Back to Inventory Management in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

4 Comments
  1. Hi. This is an excellent reference page, thank you. However, in the “Current status” page section, it only totals the number of sales. It does not show how much stock is on hand, which is what I thought it would do.

    Is there a way in the current status page to show the current stock levels of various items, based on what has been purchased and what has been sold in the other sections? I was hoping to be able to have a row/column for each item that shows that information.

    • Hi STEWART SPEEDIE
      Thank you for your insightful comment. We have just updated this article. The inventory tracker now includes the hand-in stocks. I hope this satisfies you.
      Have a good day!!

  2. I can’t thank you enough for this tutorial. It took me three days to figure it all out (my lack of Excel knowledge) – but it’s done!!! I can’t wait to start populating the fields with real information!!!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo