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

What Is Inventory Tracking?

Inventory tracking involves monitoring and managing a company’s inventory levels, movements, and status. The primary goals of inventory tracking are:

• Ensure Availability: To make sure the right products are available when needed to meet customer demand.
• Cost Optimization: Minimize costs associated with overstocking (holding excess inventory) and stockouts (running out of stock).

The common parameters used in inventory tracking include:

• Opening Stock: The initial inventory level at the beginning of a specific period (e.g., month, quarter, year).
• New Purchases: The quantity of items purchased during the period.
• Sales: The quantity of items sold during the period.
• Stock-in-Hand: The remaining inventory at the end of the period.

Here is an overview image of inventory tracking in Excel.

Dataset Overview

We’ll use the below sample dataset.

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 that a business has in stock at the beginning of an accounting period. It serves as the starting point for tracking inventory levels throughout that period.

• In your Excel workbook, create a table listing all the items you want to track. Include a column for the opening stock quantity.

• Select the entire table.
• Give this table a name (e.g., Item).

• In the Tracker sheet, where you want to track inventory, enter the following formula in cell D5 (assuming the item code is in cell B5):

• • The lookup_value (B5) corresponds to the item code. Excel will search for this code in the “Item” table and return the corresponding opening stock value from the 3rd column of the table. The FALSE argument ensures an exact match.

• Drag the fill handle (small square at the bottom-right corner of the cell) down to fill the formula for all items (up to D12).

Step 2 – Calculate New Purchases

New Purchases represent the quantity of a particular product that a business acquires from suppliers during the accounting period.

• Set up a new sheet or table specifically for recording new purchases.
• Include columns for the date, item, and quantity purchased.

• In the Tracker sheet, enter the following formula in cell E5 (assuming the item code is in cell B5):

• • Similar to before, Excel will search for the item code in the New_Purchase table and return the corresponding quantity purchased from the 3rd column.

• Drag the fill handle down to fill the formula for all items (up to E12).

Step 3 – Record Sales

Sales refer to the quantity of a particular product that a business sells to customers during the accounting period. To track sales:

• Set up a new sheet or table specifically for recording sales.
• Include columns for the date, item, and quantity sold.
• Name this table Sales.

• In the Tracker sheet, enter the following formula in cell F5 (assuming the item code is in cell B5):

• Excel will search for the item code (B5) in the Sales table and return the corresponding quantity sold from the 3rd column.

• AutoFill this formula down to F12.

Step 4 – Track Inventory for Different Items

Now let’s calculate the inventory for different items. This represents the quantity of a particular product that a business has in stock at the end of an accounting period.

• In cell G5 of the Tracker sheet, enter the following formula:

=D5+E5-F5