How to Track and Analyze Inventory Changes in Excel - Daily, Weekly, & Monthly Insights

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: https://creatoriq.cc/44qUfjx Local Elevator by Kevin MacLeod is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ WATCH NEXT 📺 Create a Basic Inventory List in Excel: https://youtu.be/GYChuAor3Zk Track Inventory Stock Automatically: https://youtu.be/wHTBezb-pEk #ExcelInventoryTutorial #InventoryManagement #ExcelTutorial #inventorytracking 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 🎓FREE COURSE: How To Create Fillable Forms in Microsoft Word - A Step-by-Step Guide Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro! https://youtu.be/438pCPCSuG4 CHANNEL LINK 📺 https://www.youtube.com/@SharonSmith 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: https://creatoriq.cc/43c51cv Thank you for supporting my channel! 🌟 CONNECT WITH ME 📎 Visit my website: https://www.sharonsmithhr.com for more information, tools and resources. LinkedIn: https://www.linkedin.com/in/sharonsmithhr Twitter: https://twitter.com/SharonSmithHR Instagram: https://www.instagram.com/sharonsmithlearning/ Facebook: https://www.facebook.com/SharonSmithLearning GEAR ⚙️ 🎙 Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!) 🖱 Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!) 🎥 Screen Recording Software: https://techsmith.z6rjha.net/NZG5b 📗 Green Screen: https://amzn.to/2DnHsY2 📸 Camera: https://amzn.to/39KvpQA 🔌 Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam) RESOURCES 📚 ✏️ JotForm: https://www.jotform.com/pricing/?utm_source=sharon-smith&utm_campaign=jf1&utm_medium=blog 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: https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url 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! ❤️