How to Analyze Inventory List Data in Excel - Report on Daily, Weekly, & Monthly Inventory Changes
How to analyze inventory list data by performing inventory tracking analysis in Excel. Analyze inventory data and track stock on a daily, weekly or monthly basis in Excel. Discover trends in your inventory data by building a master history log that updates automatically, using the inventory list template we developed in the video "How to Create & Track a Basic Inventory List in Excel." Building on our basic inventory list, we added a daily stock in and stock out tracker to update the master list in the video "How to Track Inventory Stock In & Stock Out Automatically in Excel." In this video, we create a macro button that when clicked will update the master inventory history log with a daily snapshot of our master inventory data. From there we build Pivot Tables for daily, weekly and monthly inventory stock tracking. We then create a dashboard with Pivot Charts, Slicers, and Timelines to easily visualize and filter our data to see patterns and trends.
Get a jump start on this project with my automated inventory template that we create and use in this video, available for purchase:
Local Elevator by Kevin MacLeod is licensed under a Creative Commons Attribution 4.0 license.
WATCH NEXT
Create a Basic Inventory List in Excel:
Track Inventory Stock Automatically:
TIMESTAMPS ⏰
00:00 Analyze Inventory Data in Excel
00:20 Create Master Inventory List History Log
02:29 Create Macro Button in Excel
05:09 Create Pivot Tables for Daily, Weekly & Monthly Tracking
08:03 Create Pivot Table Charts for Dashboard
10:47 Insert Slicers and Timelines for Pivot Charts
12:57 Add History Log Data and Refresh Tables and Dashboard
VBA Code used in this video:
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
COURSE
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro:
CHANNEL LINK
Visit my Channel page on YouTube to see all my videos, playlists, community posts and more!
TEMPLATES
Check out my helpful list of templates available for purchase:
Thank you for supporting my channel!
CONNECT WITH ME
Visit my website: for more information, tools and resources.
LinkedIn:
Twitter:
Instagram:
Facebook:
GEAR ️
Blue Yeti USB Microphone: (Great for recording professional sounding audio for your videos!)
Silent Mouse: (This is a really cool mouse!)
Screen Recording Software:
Green Screen:
Camera:
Live Stream Tool: (Turns your DSLR into a top notch webcam)
RESOURCES
️ JotForm:
️ TechSmith Camtasia Screen Recording:
Links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
SUPPORT THIS CHANNEL
- Hit the "$Thanks" button on any video, or
- Donate through my PayPal link:
If you found this content helpful, please consider donating to my channel. Your donation, no matter what amount, is greatly appreciated and goes towards producing more content that enhances your productivity and elevates your skills.
You can also support my channel just by watching, liking, and sharing all my videos! Thank you so much! ️
