How to Track Inventory Stock-In and Stock-Out Automatically in Excel

Managing inventory can be a daunting task, especially when you're dealing with manual updates and potential errors. But what if I told you that you could automate the tracking of stock-in and stock-out transactions in Excel, making the entire process much more efficient? In this tutorial, I'll walk you through the steps to create an automated inventory list in Excel that dynamically updates quantity in stock based on your transactions.

Ready to streamline your inventory management? Let’s dive in!

Step 1: Set Up Your Master Inventory List

Before we begin automating anything, we need a Master Inventory List. If you already have a basic inventory list, great! If not, follow the steps below to create one.

If you'd like a more in-depth guide on creating a basic inventory list, check out my previous video where we build a basic inventory list from scratch.

Create & Track a Basic Inventory List in Excel

You can also purchase this Inventory List template to get a jump-start.

1. Create a new Excel sheet and label it "Master Inventory."

2. Set up columns for the following headers: Inventory ID, Product Name, Quantity in Stock, and any other details you want to track, like Location or Supplier.

3. Populate your list with your current inventory data.

Step 2: Highlight Your Quantity in Stock Column

Now that your Master Inventory List is set up, we're going to highlight the column where we track the "Quantity in Stock." This is the column that will eventually be dynamically updated based on stock-in and stock-out transactions.

• Select the "Quantity in Stock" column, right-click, and choose a distinct fill color to make it stand out.

This will help visually separate the column as you work on automating your inventory management.

Step 3: Create a Stock Tracker Tab

Next, we’ll set up a Stock Tracker tab to log all your stock-in and stock-out transactions. This is where we’ll input each transaction to keep our inventory list updated.

1. Click the plus button in the Excel sheet to create a new tab.

2. Rename the tab to "StockTracker."

3. Set up columns with the following headers:

   • Transaction ID

   • Inventory ID (to match the Inventory ID in your Master Inventory List)

   • Transaction Date

   • Transaction Type (Stock In or Stock Out)

   • Quantity

   • Notes (Optional, for recording extra details)

Step 4: Format the Stock Tracker Tab as a Table

To make the Stock Tracker easier to manage, convert it into an Excel table. This will allow for easy filtering, sorting, and the automatic inclusion of new rows.

• Select all your data in the Stock Tracker tab.

• Press Ctrl + T to turn your data into a table, and click "OK."

This will help Excel recognize new transactions and keep everything organized.

Step 5: Create a Drop-Down List for Transaction Type

To make sure your transaction type is always consistent, create a drop-down list that allows you to choose between "Stock In" and "Stock Out."

1. Navigate to the "Data Validation" feature in Excel (found under the Data tab).

2. Select the Transaction Type column in your Stock Tracker tab.

3. Under "Settings," choose "List" from the "Allow" dropdown, then input the options: "Stock In" and "Stock Out."

4. Press "OK," and now you’ll have a simple drop-down list for every transaction.

Step 6: Create a Pivot Table for Transaction Insights

Pivot tables allow you to quickly analyze your data. Let’s create a pivot table to summarize your stock-in and stock-out transactions:

1. Click anywhere inside your Stock Tracker table.

2. Go to the "Insert" tab, then click "Pivot Table."

3. Choose to insert the pivot table into a new sheet or an existing one.

4. In the Pivot Table Fields area, drag the Transaction Date to "Rows," Inventory ID to "Rows," Transaction Type to "Columns," and Quantity to "Values."

You can now easily see how much stock has come in and gone out for each product. Check out the video to see how to add Slicers and Timelines to better filter your pivot table data.

Step 7: Set Up Dynamic Stock Updates in the Master Inventory List

This is where the magic happens! We’ll use formulas to dynamically update the quantity in stock in your Master Inventory List based on the data in your Stock Tracker.

1. In your Master Inventory List, go to the "Quantity in Stock" column.

2. Insert a formula that references your Stock Tracker data using SUMIFS. This formula will sum up all "Stock In" transactions and subtract all "Stock Out" transactions for an item to give you a real-time update on your inventory.

Here’s an example formula you can use that goes along with the inventory list spreadsheet template available for purchase. This formula references the sheet names and column structure used in this template:

=SUMIFS('StockTracker'!E:E, 'StockTracker'!B:B, A4, 'StockTracker'!D:D, "Stock In") - SUMIFS('StockTracker'!E:E, 'StockTracker'!B:B, A4, 'StockTracker'!D:D, "Stock Out")

This formula looks for the Inventory ID in the Stock Tracker and sums up the cumulative quantities based on whether the transaction was a Stock In or Stock Out for that item.

Step 8: Set Up a Variance Tracker for Physical Inventory Counts

It’s always important to double-check your digital records against physical counts. Let’s create a Variance Tracker tab where you can compare your expected stock levels with your actual physical count.

1. Add a new sheet and name it "Variance Tracker."

2. Copy over the Inventory IDs and Product Names from your Master Inventory List.

3. Create columns for:

   • Expected Quantity (pulled from your Master Inventory List)

   • Actual Count (from your physical inventory audit)

   • Variance (the difference between the expected quantity and the actual count)

Use this sheet to log your physical counts and compare them with the automated numbers in your system.

Final Step: Watch the Video for a Full Walkthrough

If you’re ready to automate your inventory tracking and want to follow along with these steps visually, be sure to watch my full video tutorial.

In the video, I go over each of these steps in detail, with helpful tips for setting up your automated inventory list in Excel. Plus, if you want to save time, you can purchase the exact template I use in the video, available in the link below.

Why Automate Your Inventory Management?

Automating your inventory system with Excel means less time spent manually updating stock levels and fewer errors in your records. By setting up an automated system, you can focus on growing your business instead of worrying about inventory discrepancies.