How to Analyze Inventory Data and Track Stock in Excel
Tracking and analyzing inventory data is essential for any business that manages stock. In this tutorial, I will show you how to track and analyze your inventory changes on a daily, weekly, or monthly basis in Excel, and visualize trends with pivot tables and dashboards. If you’d like to get started right away, you can download my pre-built Inventory Template by clicking here.
How to Analyze Inventory Data - Perform Inventory Tracking Analysis in Excel
Step 1: Create an Inventory List
Before analyzing data, you'll need a basic inventory list. If you haven’t created one yet, watch my video tutorial on How to Create a Basic Inventory List in Excel for a step-by-step guide. In this example, I’m starting with an existing inventory list that tracks items, quantities, and other details.
Step 2: Copy the Master Inventory to Create a History Log
To track changes over time, the first thing you’ll want to do is create a history log of your inventory. This way, you can compare stock levels on different dates.
Right-click on the tab of your Master Inventory sheet.
Choose Move or Copy.
Check the box that says Create a Copy, then click OK.
Rename the new sheet as Inventory Log.
In the Inventory Log sheet, delete unnecessary columns like total inventory value, and add a new column header for Date and Week Number.
Step 3: Add a Snapshot Date
Now that we’ve created the history log, you can start tracking snapshots of your inventory on different dates. In the Date column, enter the date when you captured the inventory data. This will serve as your reference for when the snapshot was taken.
For example, I entered 8/23/2024 to represent the snapshot taken on that day.
Step 4: Set Up a Macro to Automate Inventory Snapshots
To make it easy to log your inventory daily, we’re going to set up a macro that will automatically take a snapshot of the master inventory and log it with the current date.
Go to the Developer tab, then click Visual Basic.
Insert a new module, and enter the macro code (the sample code we used in this video is below):
Sub LogInventory()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastRowSource As Long
Dim lastRowDest As Long
Dim nextRowDest As Long
' Define source and destination sheets
Set wsSource = ThisWorkbook.Sheets("MasterInventory")
Set wsDest = ThisWorkbook.Sheets("InventoryLog")
' Find the last row in the source and destination sheets
lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
nextRowDest = lastRowDest + 1
' Copy current inventory data from A4 through N to log sheet
wsSource.Range("A4:N" & lastRowSource).Copy
wsDest.Range("A" & nextRowDest).PasteSpecial Paste:=xlPasteValues
' Add the current date to the log in column O
wsDest.Range("O" & nextRowDest & ":O" & wsDest.Cells(wsSource.Rows.Count, "A").End(xlUp).Row).Value = Date
' Optional: Display a message box to confirm logging
MsgBox "Inventory logged successfully!"
End Sub
Save your workbook as a Macro-Enabled Excel Workbook (with the extension .xlsm).
The macro will take a copy of your inventory data, log it in the Inventory Log, and add the current date for each entry. You can create a button to run this macro at the end of each day.
Step 5: Build Daily, Weekly, and Monthly Pivot Tables
Once you’ve logged your data, it’s time to analyze it. You can use pivot tables to track changes in your inventory on a daily, weekly, and monthly basis. Refer to the video for detailed steps.
Select your data range from the Master Inventory History Log data, then click on Insert > Pivot Table.
Drag the Date field to the Rows area.
Drag the Quantity in Stock field to the Values area.
Repeat this process to create separate pivot tables for daily, weekly, and monthly data. You can group the data by weeks and months in the Rows area.
Step 6: Create a Dashboard to Visualize Trends
Visualizing your data makes it easier to spot trends over time. To create an interactive dashboard:
Insert a Pivot Chart for each pivot table (daily, weekly, monthly).
Add these charts to a new sheet called Dashboard.
Customize the charts by using slicers and timelines for easier navigation through your data.
In the video, I show how to insert slicers for inventory items and timelines to control your date ranges. These features let you filter and update the data for specific inventory items or time periods.
Step 7: Refresh Data and Update Your Charts
As you continue logging your inventory data over time, you’ll need to refresh the pivot tables and charts to include the latest information.
Go to the PivotTable Analyze tab and click Refresh All.
Your dashboard will update automatically to reflect the newest inventory data.
Final Thoughts
Now that you’ve set up your inventory tracking system in Excel, you can easily monitor daily, weekly, and monthly trends. This will help you stay on top of stock levels and make informed decisions for your business.
If you found this tutorial helpful, be sure to check out the Inventory Management in Excel Playlist where in three videos, I walk you through the entire process in real-time. You can also download the template to get a head start.
Explore SharonSmithHR.com for more Excel tutorials, templates, and resources. Don’t forget to subscribe to my channel for more tips and tutorials!
